Query runs very slowly, however all indexes are used

MySQL & MariaDB Other MySQL® Questions
1

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 |±—±------------±------±-------±----------------------------------±--------±--------±-----------------±-----±------------+

2

Hi,

first of all I am just a beginner and new to this forum, but maybe wishing to help )

Which is the table containing ~100recs, which is the table containing ~1K recs, … Can you specify ?
If 2d table has a 1-1 link to 3d table, how can 3d table contain millions of recs?
Could you run a SHOW INDEX
Thanks

3

It’s all there, actually.

1st table - tmpEquipmentGeoIDs ~ 100 records (first table in the query)
2nd table - tblTrip ~ 1K (second table in the query)
3rd table - tblGPSlog ~ 1M records

if you look at the key used for 3rd table - you’ll see PRIMARY, which means it only selects as many records as there were in the result of the join with second table, hence only thousands are being selected. I can’t do SHOW INDEX right now, but if you look at the type column - you’ll see that it uses index, ref, and eq_ref which are best possible select types.

4

Why is the ‘tri’ table not as “Using Index” ?

5

Well, two others use Primary key, second one uses foreign key index, maybe that’s why it’s not showing “Using Index” in that column. It’s still a “ref” type, and, according to this page it’s still pretty good…

6

Hey,

If I’m not wrong, ‘Using Index’ has nothing to do with the type of key that’s used.
It rather means that for tri table, to retrieve the data it needs, it has to execute disk accesses instead of accessing the data in the index tree. So you’re using some column names which aren’t defined as an index for this table.
I may be wrong that’s why I asked a SHOW INDEX …
However I doubt this will increase performance dramatically … That’s all I can see to optimize.

7

Make sure you have an index on tblGPSLog.logId.