Re: sometimes, mysql don't use an existing index

[Toa Sty]

Hi there,

With your first statement my guess is that the condition on the timestamp is not selective enough to warrant using index lookups. i.e. I guess that mysql considers it more efficient to just table scan in this case, because there is such a large proportion of matching rows.

In your second example the condition on the timestamp *is* selective enough to warrant using index lookups, so that's what mysql does.


I don't know your data distribution but I would imagine that you'll get much better results by getting the query to use a trace.customerID index rather than the timestamp one as I imagine it's much more selective.
(how many rows would you expect a particular customerID to match? Not that many I guess)

Try adding an index on the trace table on (customerID, timestamp) and see what the explain plan for the query then looks like. (and post it here)

There may be more (or better) things that can be done, but let's see what difference this makes. I suspect it will make quite a lot.

BTW it may take a while to add on a table of 50M rows so it's probably do it on a non-live db :)

HTH,
Toasty