mysql query slow with index

Question

I have the following table structure.

Now I am using the following query

SELECT DATE(date),SUM(amount) FROM my_table WHERE type!=5 
AND date BETWEEN '2014-01-01' AND '2014-01-04'
GROUP BY DATE(date) ORDER BY date DESC

This query is working on my development database which only has small number of rows. But this is very slow with the prod database and is taking almost 9 seconds to display the values for a week. I have indexes for date and type and my explain query is showing type as all. The prod DB almost has 700000 entries there.

How can I optimize this query further ??

ModulusJoe · Accepted Answer · 2014-02-17 11:28:18Z

I believe you are trying to find a way for the index to be referenced with your query. The MySQL documents talks about group by optimisation here:

http://dev.mysql.com/doc/refman/5.5/en/group-by-optimization.html

There are a number of points about what must be applicable for a loose scan to occur. While the documentation states that only MIN and MAX should be used you may find that adding the following index:

ADD INDEX `BTREE_OPT` (`type`, `date`, `amount`) USING BTREE

And then examining the explain extended you see the new inclusion of a an index usage:

mysql> EXPLAIN EXTENDED SELECT DATE( date ), SUM(amount) FROM my_table WHERE TYPE !=5 AND date BETWEEN '2014-01-01' AND '2014-01-04' GROUP BY DATE( date ) ORDER BY date DESC \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
         type: range
possible_keys: date,type,BTREE_OPT
          key: BTREE_OPT
      key_len: 4
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using where; Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

Which may speed up the query.

Neels · Accepted Answer · 2014-02-17 11:42:20Z

0

In your query, you are using DATE(date) to extract the datetime part of the expression in use. But in your DDL, date is already defined as a datetime. This might be creating an overhead while execution. If your DDL is correctly defined with the correct datatype, I guess DATE(date) can be avoided to achieve better results.

Another approach is to force the SQL optimizer to use the Index applied on the columns. You can achieve this by modifying the query as:

SELECT DATE(date),SUM(amount) FROM my_table 
USE INDEX(type,date) 
WHERE type!=5 
AND date BETWEEN '2014-01-01' AND '2014-01-04'
GROUP BY DATE(date) ORDER BY date DESC;

You can read more about USE INDEX at http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

edited Feb 17, 2014 at 11:42

answered Feb 17, 2014 at 11:17

Neels

2,5336 gold badges36 silver badges41 bronze badges

5 Comments

Happy Coder Over a year ago

Oh it is not on that way. I am using it to get the date part only. Otherwise, GROUP BY will show individual sums instead of showing daily ones.

Neels Over a year ago

I understand that you cannot remove it from GROUP BY. But while selecting the date from your table do you need only the date part?

Neels Over a year ago

Check the new query posted. This should give better results.

ModulusJoe Over a year ago

@Neels I have to ask, on the fiddle it doesn't look like it is actually using the index on the explain extened, even if specified with the INDEX clause. Is this just miss-reporting in the fiddle?

Neels Over a year ago

Yeah.. As far as I know USE INDEX hints the optimizer to use the defined indexes which in normal scenarios it is supposed to pick automatically if defined correctly. May be what you suggest will work better in this case.

Collectives™ on Stack Overflow

mysql query slow with index

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related