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:
Test Data and Definitions
Table Definitions:
MySQL Query Plan:
Postgres Query Plan:
PlanetMySQL Voting:
Vote UP /
Vote DOWN