optimal mysql index for multi variable where clause

Question

I have a table that I often do this type of where clause on queries:

where brand_id = 330516084 
    and influencer_id = 28995 
    and date_retrieved >= '2011-01-24' 
    and date_retrieved <= '2011-02-23'

my table has an id and 8 or so other fields, it is innodb

To make this query fast, should I create an index for brand_id and for influencer_id and for date_retrieved (1 index for each) or create 1 index with all 3 fields? Or something else?

thanks Joel

nate c · Accepted Answer · 2011-02-23 23:09:30Z

1

For that query the fastest index would be one index on brand_id and influencer_id (put the one with the most unique values first). The date conditions will not use any index because you have less than and greater than signs in the operators.

answered Feb 23, 2011 at 23:09

nate c

9,0532 gold badges29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joelio Over a year ago

ok cool, is the best thing to do for the date range stuff is to just clear off the stuff thats older than 6 months and put in some sort of archive table, then all my queries can run without date range?

nate c Over a year ago

I would not move around tables to try and predict query speed. It may not even help. If your first two indexes are selective enough the date conditions will not make that much of an impact. Best to keep a normalized design and optimize at the end. (You could use a date index if you changed >= <= to IN condition and listed the specific dates you want.)

Collectives™ on Stack Overflow

optimal mysql index for multi variable where clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related