Improve mysql query speed with long date range condition

Question

I have a two table:table_car and table_order:

I want a table like this:

table_order has composite key (id_type,date)

SELECT "September" AS `Range`,
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_items',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_items',
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_amount',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_amount' 
FROM table_order 
WHERE id_type IN(1,2,3,4) AND `date` >= "2015-09-01" AND `date` <= "2015-09-30"
UNION
SELECT 
"August" AS `Range`,
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_items',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_items',
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_amount',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_amount' 
FROM table_order 
WHERE id_type IN(1,2,3,4) AND `date` >= "2015-08-01" AND `date` <= "2015-08-31"

Are there any others way to query faster? Let say I have a table with billion records and can execute long date range query in the condition, like date >= "2015-01-01" and date <= "2016-01-01" for example.
You can find sample here: http://sqlfiddle.com/#!9/1787e/1

@Drew: yes it need to be datetime. But if you have any better suggestion? — sontd
– sontd, Commented Sep 25, 2015 at 7:04

jkavalik · Accepted Answer · 2015-09-25 10:14:11Z

1

Using the GROUP BY:

SELECT date_format(`date`, '%M') AS `Range`,
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_items',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_items',
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_amount',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_amount' 
FROM `order`
WHERE id_type IN(1,2,3,4) AND `date` >= "2015-08-01" AND `date` <= "2015-09-30"
group by year(`date`), month(`date`)
order by year(`date`) desc, month(`date`) desc;

http://sqlfiddle.com/#!9/d76254/2

Or keep your query with the union if you are always going for just two months. But if the range can be a lot bigger then I suggest using the modified one, it is much simpler to write and the grouping should not be the performance bottleneck if you select only a relatively small part of the table.

The GROUP BY variant can be made faster if you store the year-month in a separate column, so no functions are needed, and modify indexes accordingly. Your variant can use indexes well without modifications.

Your example (on the fiddle) does not show index usage because you have too few rows in the table and the query will have to read all of them anyway. But you can add a covering index (id_type, date, items) (just extending the one you already have is enough, no need to keep the shorter one) which helps both versions of the query.

Just check explains:

answered Sep 25, 2015 at 10:14

jkavalik

1,31611 silver badges22 bronze badges

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

2 Comments

sontd Over a year ago

great! it will useful with month or year data. that's what I need, but one more problem: sometimes I want to use the condition like this: date >= "2015-08-15" AND date <= "2015-09-15". So with your query we cannot group by, isn't it?

jkavalik Over a year ago

@sontd if you want just one group from that range and not two then you are right, this simple group by won't make it. You might create an expression which would work or use your original solution.

Community · Accepted Answer · 2017-05-23 12:14:10Z

0

1) your example shows sum(items)for the amount, which I suppose is a copy error.

2) Possibly GROUP_CONCAT is what you're looking for

MySQL Doku

Examples

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Sep 25, 2015 at 7:47

Kurt Ludikovsky

7121 gold badge6 silver badges24 bronze badges

Comments

Luzgan · Accepted Answer · 2015-09-25 08:54:06Z

0

You could also try with indexes. Very helpful in many cases: Mysql Indexes

answered Sep 25, 2015 at 8:54

Luzgan

10610 bronze badges

2 Comments

sontd Over a year ago

I used indexes for this table: (id_type,date)

Luzgan Over a year ago

So you cannot get more from that. Fine :D I hope that you will get more optimization ideas ;)

Collectives™ on Stack Overflow

Improve mysql query speed with long date range condition

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related