Aggregation queries 10x slower on MySQL compared to Postgres??

Posted on August 8, 2013 by Sumeet Sheokand

How can this be? I am shocked. I have looked at query plans, confirmed indexes, checked handler status variables after query execution to figure out what MySQL is up to, and I don’t think there is anything wrong with it. MySQL is using the right index, using ICP, Batched Key Access. Basically, everything that we can throw at it. I even tried MariaDB and it used the new Batched Hash Join. Same result. Postgres is done in 150ms and MySQL 5.6 takes 3s!

We had a customer who was migrating from Postgres to MySQL approach us about a slow running query. Here’s the situation. They have a fleet of cars, which are loaned out to customers for short periods of time. The cars have sensors that report mileage periodically. They want to figure out which customer drove how many miles during a certain time period. Easy enough, right?

The cust_car table (25K rows) captures which customer had which car and what was the odometer reading when it was loaned. The car_read table (255K rows) captures the odometer reading that the sensors send out frequently. So a simple … scan the smaller table, nested loop join to the big one, using the indexes to look up rows, sort, group by and done! That’s what the query plan says… for both MySQL and Postgres. And I’ve confirmed that Sort/Group By is not the problem. It’s the Join.

How can this be?!

Query:

select
    cust_id, sum(mileage) as lifetime_mileage
from
    (select
        cust_id, max(odometer_reading - issued_odometer) as mileage
    from
        car_read
    inner join cust_car ON car_read.car_id = cust_car.car_id
        AND read_datetime between assigned_datetime AND unassigned_datetime
    group by cust_car.id, cust_id) As first
group by cust_id order by cust_id;

Test Data and Definitions

Table Definitions:

create table cust_car(id int not null auto_increment,
  cust_id int not null,
  car_id int not null,
  assigned_datetime datetime default null,
  unassigned_datetime datetime not null,
  issued_odometer bigint not null,
  merged boolean default 0,
  create_manually boolean default 0,
  created_at datetime not null,
  updated_at datetime not null,
  primary key(id)
) engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
create table car_read(
  id int not null auto_increment,
  car_id int not null,
  reader_id int not null,
  odometer_reading bigint not null,
  longitude float default null,
  latitude float default null,
  read_datetime datetime not null,
  created_at datetime not null,
  updated_at datetime not null,
  primary key(id),
  key idx_reader_id(reader_id),
  key idx_car_read_datetime (car_id, read_datetime);
) engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL Query Plan:

+----+-------------+------------+------+-----------------------+-----------------------+---------+-----------------------+---------+----------+---------------------------------------------------------------+
| id | select_type | table      | type | possible_keys         | key                   | key_len | ref                   | rows    | filtered | Extra                                                         |
+----+-------------+------------+------+-----------------------+-----------------------+---------+-----------------------+---------+----------+---------------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                  | NULL                  | NULL    | NULL                  | 6859230 |   100.00 | Using temporary; Using filesort                               |
|  2 | DERIVED     | cust_car   | ALL  | NULL                  | NULL                  | NULL    | NULL                  |   25690 |   100.00 | Using temporary; Using filesort                               |
|  2 | DERIVED     | car_read   | ref  | idx_car_read_datetime | idx_car_read_datetime | 4       | track.cust_car.car_id |     267 |   100.00 | Using index condition; Using join buffer (Batched Key Access) |
+----+-------------+------------+------+-----------------------+-----------------------+---------+-----------------------+---------+----------+---------------------------------------------------------------+

Postgres Query Plan:

Sort  (cost=257970.22..257970.72 rows=200 width=12)
   Sort Key: cust_car.cust_id
   ->  HashAggregate  (cost=257960.07..257962.57 rows=200 width=12)
         ->  GroupAggregate  (cost=245430.46..257576.76 rows=25554 width=28)
               ->  Sort  (cost=245430.46..248371.21 rows=1176299 width=28)
                     Sort Key: cust_car.id, cust_car.cust_id
                     ->  Nested Loop  (cost=0.00..70531.27 rows=1176299 width=28)
                           ->  Seq Scan on cust_car  (cost=0.00..546.54 rows=25554 width=40)
                           ->  Index Scan using idx_car_read_datetime on car_read  (cost=0.00..1.93 rows=46 width=20)
                                 Index Cond: ((car_read.car_id = cust_car.car_id) AND (car_read.read_datetime >= cust_car.assigned_datetime) AND (car_read.read_datetime <= cust_car.unassigned_datet
ime))

Sumeet Sheokand is the CTO of GenieDB. With over 15 years of building scalable database architectures, he has extensive experience in database and data warehouse applicable to various Big Data problems. Sumeet regularly writes about technical solutions to solve the challenges of the database community.

  • http://shardquery.com Justin Swanhart
    • Sumeet

      Thanks Justin! Appreciate the legwork in confirming the bug. Now to wait and look forward to a fix.

      Thanks again.

  • Akira

    cust_car.car_id has no index!
    Maybe a covered index (car_id, cust_id) help the group by. Add ORDER BY NULL after group by to avoid sort in subquery.

    • Sumeet

      Hi Akira.. thanks for looking at the problem.

      The query does not need an index on car_id since it is not trying to look for a specific row using car_id. All the data in the table has to be scanned for this query. Hence, the options are a table scan or an index scan based on an index which has all the columns needed for the query.

      I have attempted such an index and it has no net impact on query performance, because all it does is to reduce the number of columns read per row. The data is in RAM and the net difference of not reading a few columns doesn’t help much.

      Please see my reply to James above about my speculation about the Join being the culprit.

  • http://www.jonathanlevin.co.uk Jonathan Levin

    I would just add to what has already been said that you are filtering the rows on the larger car_read table with the read_datetime column.
    So I suggest you have an index that has that as it’s left most column.

    • Sumeet

      That would be the wrong thing to do. If I put read_datetime as the first column of the index and the join is on car_id, the index will be ignored, since it can’t satisfy the join condition.

      Please also my reply to James above.

  • https://blogs.oracle.com/supportingmysql/ James Day

    What happens if you switch to this version:

    select
    cust_id, sum(mileage) as lifetime_mileage
    from
    (select cust_id, mileage, cust_car_id
    from
    (select
    cust_id, max(odometer_reading – issued_odometer) as mileage, cust_car.id as cust_car_id
    from
    car_read
    inner join cust_car ON car_read.car_id = cust_car.car_id
    AND read_datetime between assigned_datetime AND unassigned_datetime
    ) as middle
    group by cust_car_id, cust_id) As first
    group by cust_id order by cust_id;

    Also look at the explain output’s key_len column. Notice it’s 4 bytes for idx_car_read_datetime and the info does not say “Using index”, as a covering index, because the column car_read.odometer_reading is also used? Perhaps adding that column to the index at the end would be useful.

    I also wonder about max(odometer_reading – issued_odometer). Is this the same as max(odometer_reading) – issued_odometer, a comparison that avoids calculating the difference for every row? Perhaps there is an opportunity here to select max(odometer_reading) within an inner query before doing the distance calculation?

    James Day, MySQL Senior Principal Support Engineer, Oracle

    • Sumeet

      Thanks for the comments James,

      I assume you meant to put a group by in the ‘middle’ subquery, or else the max() will just produce a max across the whole table. That would not be right! And If I move the group by to the ‘middle’, there is no difference from the query I posted. I confirmed by making the change and executing the query.

      The index using only 4 bytes is the crux of the problem. however I try, I can’t get MySQL to use the other components of the index. I did create an index with ‘car_id, read_datetime, odometer_reading’. These are the only car_read columns used in the query. MySQL still only uses the first 4 bytes (car_id) and switches to

      “attached_condition”: “(car_read.read_datetime between cust_car.assigned_datetime and cust_car.unassigned_datetime)”

      And the end result is poorer query performance.

      Also moving to max(odometer_reading) – issued_odometer has immeasurable improvement.

      And that jives with my current theory. Its not the sort/group by/aggregation that is the culprit. Its, the nested loop join.

      BTW, the sample data and table structure is attached to the post. ;-)

  • Sumeet

    Thanks for the comments Justin. I did try turning the various switches on/off and the best was with mrr=off. That dropped it down to 2.5s. So, that’s something.

    Can’t really remove group by cust_car.id as it would produce wrong results. The idea of course is to continue to get good results :-)

  • http://shardquery.com Justin Swanhart

    First, BKA and MRR perform worse than traditional index lookups when all data fits in RAM because sorting has to be done before access.

    I suggest turning off MRR and BKA first.

    Second, you will notice the join in the subquery in the from clause is using REF access on only the car_id portion of the join, it isn’t using the full 8 bytes of the key. ICP should be able to help here, but it could be hurting the query so try turning ICP off.

    Lastly the biggest problem is that you are grouping on columns from more than one table. Remove the group by on cust_car.id if you can.