MySQL Forums
Forum List  »  Optimizer & Parser

Re: optimize query for index using between and group by
Posted by: Rick James
Date: August 29, 2015 09:56AM

There are three ways to perform this:

WHERE tpc_checkdate BETWEEN 1408437444 AND 1408519225
GROUP BY tpc_checkmaster , tpc_checkmasteraction

Plan A:
1. Filter on tpc_checkdate, using INDEX(tpc_checkdate). If more than about 20% of the table is included, then scanning the table is faster than using the index.
2. Sort the resulting dataset for the GROUP BY and ORDER BY

Plan B:
1. Use INDEX(tpc_checkmaster , tpc_checkmasteraction) to prevent the sort, but have to spend extra effort on the filtering.

Plan C (ignore the indexes):
1. Scan the table, filtering on the date.
2. Sort the resulting dataset for the GROUP BY and ORDER BY

> key: INDEX_TPC_MASTER_ACTION_TIME

says that it picked Plan A.

> Using temporary; Using filesort

Does NOT necessarily say that it created a file. The first choice, which is probably the case for your code, is to build a MEMORY table, and so an in-RAM sort.

> rows: 85

And the whole query took how few milliseconds? Will you eventually have 85M rows, and you are thinking ahead?

Be aware... If you have a bigger date range, the optimizer may not use that index, and it might switch to plan B or C. The optimizer is dynamic like that.

The optimizer _usually_ picks correctly. Our job is to provide the optimal INDEXes for it to pick among. You have done that.

You should consider switching to InnoDB.

Another note... Since you are asking for only 85 rows out of 12582, filtering first was clearly the better choice. (Caveat, 85 & 12582 may not be the actual numbers.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: optimize query for index using between and group by
1405
August 29, 2015 09:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.