Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2015
    Posts
    1

    Post Unanswered: Improve mysql query speed with long date range condition

    I have a two table:table_car and table_order:
    Click image for larger version. 

Name:	m8RlK.png 
Views:	1 
Size:	4.2 KB 
ID:	16564
    Click image for larger version. 

Name:	4YNdV.png 
Views:	1 
Size:	14.7 KB 
ID:	16565
    I want a table like this:
    Click image for larger version. 

Name:	QjV1d.png 
Views:	1 
Size:	5.9 KB 
ID:	16566
    table_order has composite key (id_type,date)

    my query:
    Code:
    SELECT "2015-08-03" AS `date`,
    IF(id_type IN(1,2), SUM(items)) AS 'RedandBlue_items',
    IF(id_type IN(3,4), SUM(items)) AS 'YellowandGreen_items',
    IF(id_type IN(1,2), SUM(items)) AS 'RedandBlue_amount',
    IF(id_type IN(3,4), SUM(items)) AS 'YellowandGreen_amount' 
    FROM table_order 
    WHERE id_type IN(1,2,3,4) AND `date` >= "2015-08-03" AND `date` <= "2015-08-03"
    UNION
    SELECT 
    "2015-08-02" AS `date`,
    IF(id_type IN(1,2), SUM(items)) AS 'RedandBlue_items',
    IF(id_type IN(3,4), SUM(items)) AS 'YellowandGreen_items',
    IF(id_type IN(1,2), SUM(items)) AS 'RedandBlue_amount',
    IF(id_type IN(3,4), SUM(items)) AS 'YellowandGreen_amount' 
    FROM table_order 
    WHERE id_type IN(1,2,3,4) AND `date` >= "2015-08-02" AND `date` <= "2015-08-02"
    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    don't access the table twice. Give your entire date range,
    Code:
    AND `date` >= "2015-08-02" AND `date` <= "2015-08-03"
    make date part of your select statement rather than hard coding it. Do you really need to count the same data twice? You have in (1,2) and (3,4) for items and amounts...
    IF(id_type IN(1,2), SUM(items)) AS 'RedandBlue_items',
    IF(id_type IN(3,4), SUM(items)) AS 'YellowandGreen_items',
    IF(id_type IN(1,2), SUM(items)) AS 'RedandBlue_amount',
    IF(id_type IN(3,4), SUM(items)) AS 'YellowandGreen_amount'
    There should also be a group by date in here somewhere. Just a personal preference, but I would reccommend using the ANSI standard CASE rather than the IF, like:
    Code:
    sum(case when id_type in (1,2) then 1 else null end)  AS 'RedandBlue_items'
    Dave

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What data type is the "date" field?
    P.S. if you can, rename that column!

    You need to include this field in your grouping, instead of using UNIONs.

    If the field is of type date (see why you should rename that field?) then you can perform a simple grouping. If it is of datetime, then you need to perform some functions that can "truncate" the time portion of this value.
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •