There are no temps, filesorts or anything like that.
It's a simple join across 3 tables.
1st table contains hundreds of records
2nd table contains tens of thousands of records
3rd table is mapped 1-to-1 to the second table (but contains millions of records in full).
when 2nd join is added, performance drops from several milliseconds to several minutes per run.
mysql> explainselect tri.equId, tri.triId, tri.logEndIdfrom tmpEquipmentGeoIDs equ JOIN tblTrip tri ON equ.equId = tri.equId JOIN tblGPSLog gps ON gps.logId = tri.logEndId;+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+| 1 | SIMPLE | equ | index | PRIMARY | PRIMARY | 4 | NULL | 43 | Using index || 1 | SIMPLE | tri | ref | FK_tblTrip_1,FK_tblTrip_3,Index_5 | Index_5 | 4 | ats.equ.equId | 1492 | || 1 | SIMPLE | gps | eq_ref | PRIMARY | PRIMARY | 8 | ats.tri.logEndId | 1 | Using index |+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+
It's a simple join across 3 tables.
1st table contains hundreds of records
2nd table contains tens of thousands of records
3rd table is mapped 1-to-1 to the second table (but contains millions of records in full).
when 2nd join is added, performance drops from several milliseconds to several minutes per run.
mysql> explainselect tri.equId, tri.triId, tri.logEndIdfrom tmpEquipmentGeoIDs equ JOIN tblTrip tri ON equ.equId = tri.equId JOIN tblGPSLog gps ON gps.logId = tri.logEndId;+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+| 1 | SIMPLE | equ | index | PRIMARY | PRIMARY | 4 | NULL | 43 | Using index || 1 | SIMPLE | tri | ref | FK_tblTrip_1,FK_tblTrip_3,Index_5 | Index_5 | 4 | ats.equ.equId | 1492 | || 1 | SIMPLE | gps | eq_ref | PRIMARY | PRIMARY | 8 | ats.tri.logEndId | 1 | Using index |+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+
Comment