How can I make this MySQL query perform better?

I have one query that is preventing me from going live with this application, because it can take up to 7 seconds to complete when it isn't cached.

SELECT attribute1
FROM `product_applications`
WHERE `product_applications`.`brand_id` IN (.. like 500 ids...)
GROUP 开发者_开发百科BY attribute1

I have the brand_id indexed. I used to have this doing a SELECT DISTINCT, but opted for the GROUP BY and performance has improved slightly.

This table is using InnoDB, and has about 2.3 million rows. I have run an EXPLAIN on it and it uses the index, it just takes forever.

I know there are a lot of variables to getting something like this to perform. The db is on an Amazon EC2 instance.

Is there some sort of table splitting I could do to get the query to perform better? I really appreciate any help anybody can offer.

EDIT:

Here are the results on my explain, from NewRelic:

Id  1
Select Type SIMPLE
Table   product_applications
Type    range
Possible Keys   brand_search_index_1,brand_search_index_2,brand_search_index_3,brand_search_index_4,brand_sarch_index_5
Key brand_search_index_1
Key Length  5
Ref 
Rows    843471
Extra   Using where; Using index; Using temporary; Using filesort

See, it's using the index. But it's also using a temp table and filesort. How can I overcome that stuff?

EDIT:

Since the time I opened this question, I changed the engine on this table from InnoDB to MyISAM. I also vertically partitioned the table by moving attributes 5 through 60 to another table. But this select statement STILL TAKES BETWEEN 2 AND 3 SECONDS!!!! The poor performance of this query is absolutely maddening.

A different approach if there are very few different values of attribute1 iis to try an index on attribute1 to take advantage of the loose index scan.

please refer to the following answer:

Rewriting mysql select to reduce time and writing tmp to disk

According to this answer IN should be very fast in case of constants otherwise type conversion happens which can slow things.

I would also try a covering index with brand_id as the first column and attribute1 as the second. That should speed up things because your table won't be accessed anymore.

EDIT :

About the temporary/filesort, I suspect they are caused by the your list of +500 ids. Could you try EXPLAIN on the query with only one id in the IN operator ?

If you can reduce the size of your rows that might help. Make as many columns as possible not null. If you can remove all varchar colums that could help as well.

What exactly does the index it is using cover? Possibly try making the index cover less or more columns.

Have you ran analyze table recently? That may cause it to pick another index. Also you could try forcing certain indexes.

Is there a possibility of reducing the number of ids in the IN clause? What about using a range, if they are always sequential ids?