How can I speed up this giant query against a MyISAM table?

This query goes against a single table. The table has 60 attributes called "attribute1" through "attribute60", and they are all of type:

varchar(255)

There is also a column called "brand_id" that is:

int(11)

The following key exists on the table:

brand_id, attribute1, attribute2, attribute3

The query is as follows:

SELECT distinct attribute1
from brands b inner join product_applications pa on pa.brand_id = b.id
where b.id in (1,372,373,374,375,376,378,381,452,
               453,454,455,456,457,458,461,474,476,
               544,480,563,508,512,513,516,517,519,520,521,
               522,524,525,527,528,529,533,538,539,540,542,
               546,547,548,555,开发者_如何学编程556,557,642,643,644,645,646,
               647,648,649,650,651,652,653,654,655)

It takes over 10 seconds. The table has 5,735,673 rows.

This query needs to take no more than 2 seconds. I cannot figure out how to write it, or if I need some sort of alternate table structure, to do this. Can anyone offer a recommendation?

I used to use an IN in the WHERE clause, but it was recommended in another forum that this ugly way was faster because of the join. I don't really know what that means, but it IS faster, but still incredibly slow.

+----+-------------+-------+-------+----------------------------------------+--------------------+---------+-----------+-------+-------------------------------------------+
| id | select_type | table | type  | possible_keys                          | key                | key_len | ref       | rows  | Extra                                     |
+----+-------------+-------+-------+----------------------------------------+--------------------+---------+-----------+-------+-------------------------------------------+
|  1 | SIMPLE      | b     | range | PRIMARY                                | PRIMARY            | 4       | NULL      |    60 | Using where; Using index; Using temporary | 
|  1 | SIMPLE      | pa    | ref   | brand_search_index,parttype_search_idx | brand_search_index | 5       | mcp5.b.id | 57356 | Using where; Using index                  | 
+----+-------------+-------+-------+----------------------------------------+--------------------+---------+-----------+-------+-------------------------------------------+
2 rows in set (0.04 sec)

This seems like an odd query, so I might be missing something, but I think you can do this with an IN clause:

SELECT distinct attribute1
from product_applications pa WHERE brand_id IN (1, 372, ..., 655)

I would think this is faster -- it's certainly cleaner -- but if not you might need to add a little detail about indexes, maybe an EXPLAIN QUERY, etc.

Your table is something like this:

        brand...attr1......attr2..........attr3
        BRAND...CATEGORY...PRODTYPE....PRODUCT

e.g.

        DOVE....HOME.......SOAP.......dish washing liquid

And you want go get the distinct set of product categories for a specified list of brands. But you have over 5 million products, so it cannot be done instantly with a single table. The way this is typically handled is to have multiple related tables:

       BRANDS(brandid, brandname)
       CATEGORIES (categoryid, categoryname)
       PRODTYPES (prodtypeid, prodtypename)
       BRANDCATEGORIES (brandid, categoryid) EDIT: put a unique constraint on (brandid, categoryid)
       PRODUCTS(brandid, categoryid, prodtypeid, productname)

Then, your query would be this:

      select distinct categories.categoryid, categoryname
      from brands inner join categories
      on brand.brandid = brandcategories.brandid
      and categories.categoryid = brandcategories.categoryid
      where brands.brandid in ( .... )