1

I have a two table:table_car and table_order:

enter image description here enter image description here

I want a table like this: enter image description here

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

sontd
  • 397
  • 2
  • 4
  • 15
  • sure, de-normalize it. – Drew Sep 25 '15 at 06:42
  • Could you please explain more? – sontd Sep 25 '15 at 06:49
  • what is the datatype of `date` – Drew Sep 25 '15 at 06:54
  • @Drew: the datatype of date is datetime – sontd Sep 25 '15 at 07:00
  • @Drew: yes it need to be datetime. But if you have any better suggestion? – sontd Sep 25 '15 at 07:04
  • you need the time in there ? – Drew Sep 25 '15 at 07:05
  • @Drew: I saved time in another two columns, such as: time_begin and time_end. For example: from 09:00:00 to 10:00:00. But in this example just need date – sontd Sep 25 '15 at 07:09
  • be that as it may, this column is a datetime. Your composite is 12 bytes wide, plus the query does an `in()`. You *could* get the composite keylen down to 3 bytes. And where start and end are the same date, you are taxing it by an extra comparison. A prepared stmt would handle that – Drew Sep 25 '15 at 07:11
  • but I am not writing that up, not on this website. Off to sleep – Drew Sep 25 '15 at 07:12
  • @Drew: sorry I got a mistake, the datatype for date column is date, just like you see in the picture. I'm not use datetime actually. Do you know another way to select, maybe different as UNION ? – sontd Sep 25 '15 at 07:15
  • @sontd Your query does not seem right - you have no GROUP BY and the sums will sum the same values, the IF should be inside the sum() probably. You can select `date` from the table directly, no need to add it as a constant in the UNIONed queries. Make the query right and only then think about speed. – jkavalik Sep 25 '15 at 07:50
  • @jkavalik: I can use group by but I need a group Red&Blue, Yellow&Green data, if I use group by it will separate to 2 different rows. see in my question, I edited query a bit to get data of two months – sontd Sep 25 '15 at 08:47
  • @sontd the sums look better now, but you can probably get rid of the union by grouping on the date. Can you try to prepare an example on http://sqlfiddle.com/ ? – jkavalik Sep 25 '15 at 08:57
  • @ jkavalik: I made an example here: http://sqlfiddle.com/#!9/1787e/1. Could you take a look? I see this query not use indexs so it will take long time – sontd Sep 25 '15 at 09:24

3 Answers3

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:

jkavalik
  • 1,296
  • 11
  • 21
  • 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? – sontd Sep 25 '15 at 11:02
  • @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. – jkavalik Sep 25 '15 at 11:05
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

Community
  • 1
  • 1
Kurt Ludikovsky
  • 682
  • 1
  • 6
  • 21
0

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

Luzgan
  • 106
  • 10