0

I wrote the following SQL query to group results and then sort results in each group by date:

SELECT 
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
            ORDER BY departure_date
            SEPARATOR ','),
        ',',
        10)
FROM
    `flights`
WHERE
    origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
        AND cabin_type = 'ECONOMY'
GROUP BY origin , provider;

Second query:

SELECT 
destination,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
            ORDER BY departure_date
            SEPARATOR ','),
        ',',
        10)
FROM
    `flights`
WHERE
    origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
        AND cabin_type = 'ECONOMY'
GROUP BY destination, provider;

InnoDB table flights is quite simple:

+----------------+---------------+------+-----+-----------+----------------+
| Field          | Type          | Null | Key | Default   | Extra          |
+----------------+---------------+------+-----+-----------+----------------+
| id             | int(11)       | NO   | PRI | NULL      | auto_increment |
| origin         | varchar(255)  | NO   | MUL | NULL      |                |
| destination    | varchar(255)  | NO   | MUL | NULL      |                |
| departure_date | date          | NO   | MUL | NULL      |                |
| price          | decimal(14,2) | NO   |     | NULL      |                |
| provider       | varchar(255)  | NO   |     | airnet    |                |
| cabin_type     | varchar(255)  | YES  |     | ECONOMY   |                |
| created_at     | datetime      | YES  |     | NULL      |                |
| updated_at     | datetime      | YES  |     | NULL      |                |
+----------------+---------------+------+-----+-----------+----------------+

I have indexes on columns: origin, destination, departure_date. And approx. 10 million records in the table.

Above-mentioned query takes about 200 to 600 milliseconds. I tried to use profiler and found out that most of the time is spent on Creating sort index (approx. 98% of whole query time).

Is there any way to optimize this query. I tried compund index on origin, provider, departure_date, but that did not seem to help. Maybe configuration should be tweaked more and if so which configuration variables must be considered?

Machine has 4 GB of RAM, 2 vCPUs and dedicated for DB only (10.3.13-Maria DB).

  • Create index lime flights(origin, destination, cabin_type) (it seems for this query you can use any possible fields order in this index). – Akina Mar 25 at 19:29
  • @Akina Thanks for your help. I added this compound index, unfortunately queries execution time remains approximately the same. – user882027 Mar 26 at 8:42
  • What is the result of SELECT COUNT(*), COUNT(DISTINCT origin, provider) FROM flights. Approximate is enough. – Akina Mar 26 at 8:45
  • @Akina The result is: 10633304|9578. – user882027 Mar 26 at 14:23
1

Test

WITH cte AS ( SELECT origin,
                     provider,
                     price,
                     departure_date,
                     ROW_NUMBER() OVER (PARTITION BY /* origin, */ provider
                                        ORDER BY departure_date) rn 
              FROM flights
              WHERE origin = 'LHR' 
                AND destination IN ('FCO', 'CDG', 'AMS')
                AND cabin_type = 'ECONOMY' )
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte
WHERE rn <= 10
GROUP BY origin, 
         provider;

UPDATE: (draft code, see comments)

WITH 
cte1 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn 
          FROM flights
          WHERE origin = 'LHR' 
            AND destination ='FCO'
            AND cabin_type = 'ECONOMY' ),
cte2 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte1
          WHERE rn <= 10),
cte3 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn 
          FROM flights
          WHERE origin = 'LHR' 
            AND destination = 'CDG'
            AND cabin_type = 'ECONOMY' ),
cte4 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte3
          WHERE rn <= 10),
cte5 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn 
          FROM flights
          WHERE origin = 'LHR' 
            AND destination = 'AMS'
            AND cabin_type = 'ECONOMY' ),
cte6 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte5
          WHERE rn <= 10),
cte7 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte2
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte4
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte6 )
cte8 as ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                        ORDER BY departure_date) rn 
           FROM cte7)
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte8
WHERE rn <= 10
GROUP BY origin, 
         provider;        
  • Yes, this one does execute faster, thanks. There is one more query I want to optimize, quite similar actually, I added it as second query in the description. Number of possible destinations can be more than 3. – user882027 Mar 26 at 19:56
  • @user882027 Can't you replace origin with destination yourself? – Akina Mar 27 at 4:39
  • Never mind, I try to look for ways to optimize that second query on my own. – user882027 Mar 27 at 6:17
  • @user882027 Optimizing way is the same - avoid GROUP_CONCAT of all values (which is over 1000) by enumerating and limiting them. – Akina Mar 27 at 6:24
  • Yes, I think I understand the technique, however it seems that more destinations I add longer it takes to execute. That is in both original and optimized solutions. I am not sure if smth faster can be achieved in this case anyway. Lots of records must be checked and there is no way around that I am afraid. – user882027 Mar 27 at 7:35
1
WHERE
origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
    AND cabin_type = 'ECONOMY'

begs for either of these composite indexes:

INDEX(origin, cabin_type, destination)
INDEX(cabin_type, origin, destination)

origin and destination look like they could be CHAR(3) instead of VARCHAR(255).

cabin_type could probably be an ENUM, or at least a shorter VARCHAR.

  • Actually I added composite origin_destination_provider index and it speed up queries significantly. Thanks for VARCHAR shortening suggestions. – user882027 Apr 18 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.