How can I speed up this query?

I have a table with 60 attributes in it, attribute1..attribute60. The database engine in MySQL and the table engine is MyISAM. The query is as follows:

SELECT DISTINCT attribute1
  FROM `product_applications`
 WHERE `product_applications`.`brand_id` NOT IN (642, 630, 513, 637, 632,
                                                 556, 548, 628, 651, 660,
                                                 648, 557, 650, 624, 652,
                                                 636, 546, 662, 634, 629,
                                                 657, 638, 658, 659, 661, 625)

I use a NOT IN, because that list is significantly smaller than the IN list.

I have created the following index:

brand_id, attribute1, attribute2, attribute3, attribute4

A DESC reveals that this index is being selected, but it looks like it is still looking at the whole table because I see the whole row count in the "rows" column:

6732948

In the "extra" column I have:

Using where; Using index; Using temporary

This query is taking over 7 seconds. I am looking at all different options here, including breaking the table up.

UPDATE:

I was able to cut the query time in half with the clever use of the UNION ALL noted by my friend below. Also, this is a dynamically genera开发者_Python百科ted query, so none of the temporary table options that some of you have offered, while an excellent idea, were available to me.

---

Previously, the following used a LEFT JOIN -- but the OP reversed the logic to use an INNER JOIN:

   SELECT DISTINCT 
          t.attribute1
     FROM PRODUCT_APPLICATIONS t
     JOIN (SELECT 642 AS brand_id
           UNION ALL 
           SELECT 630
           UNION ALL 
           SELECT 513
           UNION ALL 
           SELECT 637
           UNION ALL 
           SELECT 632           
           UNION ALL 
           SELECT 556
           UNION ALL 
           SELECT 548
           UNION ALL 
           SELECT 628
           UNION ALL 
           SELECT 651
           UNION ALL 
           SELECT 660
           UNION ALL 
           SELECT 648
           UNION ALL 
           SELECT 557
           UNION ALL 
           SELECT 650
           UNION ALL 
           SELECT 624
           UNION ALL 
           SELECT 652
           UNION ALL 
           SELECT 636
           UNION ALL 
           SELECT 546
           UNION ALL 
           SELECT 662
           UNION ALL 
           SELECT 634
           UNION ALL 
           SELECT 629
           UNION ALL 
           SELECT 657
           UNION ALL 
           SELECT 638
           UNION ALL 
           SELECT 658
           UNION ALL 
           SELECT 659
           UNION ALL 
           SELECT 661
           UNION ALL 
           SELECT 625) x ON x.brand_id = t.brand_id

You could consider populating a temp table, to use in place of the derived one you see in my answer.

---

[1] 642, 630, 513, 637, 632, 556, 548, 628, 651, 660, 648, 557, 650, 624, 652, 636, 546, 662, 634, 629, 657, 638, 658, 659, 661, 625

Since you have hardcoded it, i assume these are the numbers you want to exclude all the time.
Then, why not create a table that only contains these ids, and another table that doesn't contain these ids. And, your insert determines which table to be inserted into based on the 'brand_id'.

[2] 662, 661, 660, 659, 658, 657, 652, 651, 650, 648, 642, 638, 637, 636, 634, 632, 630, 629, 628, 625, 624, 557, 556, 548, 546, 513

A sorted list of your brand_ids is shown above. It looks like you can lower the equality calls a few more by providing a range condition instead. (ie., >=657 && <=662, >=650 &&<=652, etc..)

---

The long pole in this query is the "DISTINCT" clause.

First, I'm not sure why you said "this is a dynamically generated query, so none of the temporary table options that some of you have offered, while an excellent idea, were available to me." It's possible to use temporary tables with dynamically generated queries ... ? Perhaps you meant something else.

Can you at least build a supporting table for this? Something like:

CREATE TABLE product_applications_brand_id_attribute1 (
    PRIMARY KEY (attribute1)
) IGNORE AS SELECT attribute1
FROM product_applications
WHERE brand_id NOT IN (
    642, 630, 513, 637, 632, 556, 548, 628, 651, 660,
    648, 557, 650, 624, 652, 636, 546, 662, 634, 629,
    657, 638, 658, 659, 661, 625)

Then, you would query:

SELECT attribute1
    FROM product_applications_brand_id_attribute1

This isn't an ideal solution because you have to update the new separate table every time there's a change in the base table.