0

I have a simple query - it only looks at one table and has a choice of 2 columns for indexing, an ErrorTypeId (points to a lookup table of about 20 unique values) and DateOccurred (a datetime column that could have any date in it).

In most cases it is fine but under certain scenarios the query will time-out. Looking into it, the index that is chosen (ErrorTypeId) has fewer rows in it's plan than the other (DateOccurred) but for some reason takes far longer to run than if I manually force it to use the other index.

/* This execution plan has fewer rows than the second one - and as a result this is
 * the one chosen.  But it is much much slower.
 */
EXPLAIN
SELECT * FROM my_log_table
WHERE DateOccurred BETWEEN '2013-06-11 00:00:00' AND '2013-06-22 00:00:00' AND ErrorTypeId = 4
ORDER BY DateOccurred DESC
LIMIT 1000;


/* Many more rows - but much much quicker to execute. 
 * Is it the spread of data across pages?  Is there a
 * way other than USE/FORCE INDEX to improve execution plan?
 */
EXPLAIN
SELECT * FROM my_log_table USE INDEX (DateOccurred_Index)
WHERE DateOccurred BETWEEN '2013-06-11 00:00:00' AND '2013-06-22 00:00:00' AND ErrorTypeId = 4
ORDER BY DateOccurred DESC
LIMIT 1000;

Why is the 2nd query faster when there are more rows to look at?

Is there a way to help MySql choose the faster index without using USE/FORCE index?

Taryn
  • 242,637
  • 56
  • 362
  • 405
PeteGO
  • 5,597
  • 3
  • 39
  • 70
  • Have you tried adding a composite index, on `(ErrorTypeId, DateOccurred)`? – ypercubeᵀᴹ Jun 25 '13 at 14:49
  • Well force index will be OK for this 1 case - but I'm worried about the many other queries we have that may be under performing because of what ever reason is causing this. – PeteGO Jun 25 '13 at 20:53
  • How many rows does the table have? And why are you compromising with a not totally sufficient index (that also need to be hinted/forced) when you can add a much more efficient one? – ypercubeᵀᴹ Jun 25 '13 at 21:52
  • You're assuming a composite index, on (ErrorTypeId, DateOccurred) will be "much more efficient" than the DateOccurred one alone. I'll check and post the results. Unfortunately it still doesn't answer either of my questions in the post. – PeteGO Jun 26 '13 at 19:55
  • No, I'm "assuming" that the composite index will not be worse than any of the 2 indexes alone. In any case, with any distribution of data. And in most cases, it will be more selective than any of these 2. And because MySQL will choose that (composite) index as it will be more selective than the other 2, your query will not run "for ever", without using any hints. – ypercubeᵀᴹ Jun 26 '13 at 20:25

1 Answers1

0

The reason that the queries are slow is the inefficient indexing.

Add a composite index, on (ErrorTypeId, DateOccurred) and you will not need any forcing or hinting.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235