What fields should be indexed together? group by? order by?

Question

I'm trying to speed up a query which I currently have as:

SELECT * 
FROM `events` 
WHERE (field1 = 'some string' or field1 = 'some string') 
    and is_current = true 
GROUP BY event_id 
ORDER BY pub_date

this takes roughly 30seconds.

field1 is a varchar(150)

I'm currently indexing field1, is_current, event_id, pub_data charity, pub_date, is_current and all the fields individually...

I'm really not sure what fields should be indexed together, when I remove the order by, the query speeds up to around 8 seconds, and if I removed both the order by and group by, it's less than 1 second...

What exactly should be indexed in this case to speed up the query?

Edit: I've run explain on the modified query (which no longer includes the group by):

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
    1   SIMPLE  events  range   is_current,field1_2,field1_3,field1_4,field1    field1_3    153     NULL    204336  Using where; Using filesort

Which indicates it's using the key field1_3 which is: field1 & is_current
Although it's not using the key which includes those two fields and pub_date (for the ordering..?)

It's also using FILESORT which seems to be the main problem..

any ideas why it's using a filesort even though the pub_date field is also indexed (with the other fields)?

Answer 1

Everything, (field1, is_current, event_id, pub_date) in one index. MySQL can only use one index per joined table in a query.

Use EXPLAIN to see what happens when you do.

Also, an aside - as KoolKabin says, * is rarely a good idea. Sometimes MySQL will copy the rows in a temporary table; and then there are the communication costs. The less you ask from it the faster things will work.

UPDATE: I was actually wrong. Sorry. First off, you can't get full use of indexing if your grouping is different than your ordering. Second, do you have an index where your ordering key (pub_date) is first? If not, try if that fixes the ordering thing.

Answer 2

any ideas why it's using a filesort even though the pub_date field is also indexed (with the other fields)?

This is because the mysql optimizer is trying to use index "field1" and you want the data ordered by pub_date. If you are using mysql 5.1 (the following query will give error in earlier versionn), you can force mysql to use the pub_date index for order by, something like this

SELECT * 
FROM `events` 
force index for order by (pub_date)
WHERE (field1 = 'some string' or field1 = 'some string') 
    and is_current = true 
GROUP BY event_id 
ORDER BY pub_date