| Query runs very slowly, however all indexes are used [message #3716] |
Fri, 14 November 2008 20:00  |
|
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> explain
select
tri.equId,
tri.triId,
tri.logEndId
from
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 |
+----+-------------+-------+--------+-----------------------------------+---------+---------+------------------+------+-------------+
|
|
|
|
| Re: Query runs very slowly, however all indexes are used [message #3740 is a reply to message #3739 ] |
Wed, 26 November 2008 05:12  |
|
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.
|
|
|
|
| Re: Query runs very slowly, however all indexes are used [message #3745 is a reply to message #3742 ] |
Wed, 26 November 2008 17:55 |
|
|
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...
|
|
|
| Re: Query runs very slowly, however all indexes are used [message #3748 is a reply to message #3716 ] |
Thu, 27 November 2008 03:24 |
 matthew016
Messages: 4
Registered: November 2008
Location: Brussels |
Junior Member |
|
|
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.
[Updated on: Thu, 27 November 2008 03:24]
|
|
|
| Re: Query runs very slowly, however all indexes are used [message #4141 is a reply to message #3716 ] |
Sat, 21 March 2009 21:23 |
MarkRose
Messages: 94
Registered: January 2008 |
Member |
|
|
|
Make sure you have an index on tblGPSLog.logId.
|
|
|
Members
Search
Help
Register
Login
Home
