I'm having a hard time figuring out why a query in my application is
slow. It seems that MySQL is using a filesort where it's not
necessary, and as a result a query that should be taking a fraction of
a second is taking up to 10 seconds.
Essentially, the query is doing a join of 4 tables, two of which may
not have a matching row (and so must use an explicit LEFT JOIN). All
tables have a common "mls_num" primary key, which is the column used
for the join. The results are sorted by "mls_num", and only the first
row is requested.
I've whittled the query down to just a few lines that reproduce the
problem:
mysql> explain
SELECT mirealsource_homes.mls_num,
mirealsource_homes_supplemental.listdate,
mirealsource_images.image1,
mirealsource_homes_stats.detail_views
FROM mirealsource_homes,
mirealsource_homes_supplemental
LEFT JOIN mirealsource_images
ON mirealsource_homes.mls_num =
mirealsource_images.mls_num
LEFT JOIN mirealsource_homes_stats
ON mirealsource_homes.mls_num =
mirealsource_homes_stats.mls_num
WHERE
mirealsource_homes.mls_num =
mirealsource_homes_supplemental.mls_num
ORDER BY mirealsource_homes.mls_num
LIMIT 1;
+----+-------------+---------------------------------+--------+---------------+---------+---------+-----------------------------------------------+------+---------------------------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------------------------------+--------+---------------+---------+---------+-----------------------------------------------+------+---------------------------------+
| 1 | SIMPLE | mirealsource_homes_supplemental | ALL |
PRIMARY | NULL | NULL | NULL
| 100 | Using temporary; Using filesort |
| 1 | SIMPLE | mirealsource_homes | eq_ref |
PRIMARY | PRIMARY | 8 |
devel.mirealsource_homes_supplemental.mls_num | 1 | |
| 1 | SIMPLE | mirealsource_images | eq_ref |
PRIMARY | PRIMARY | 8 | devel.mirealsource_homes.mls_num
| 1 | |
| 1 | SIMPLE | mirealsource_homes_stats | eq_ref |
PRIMARY | PRIMARY | 8 | devel.mirealsource_homes.mls_num
| 1 | |
+----+-------------+---------------------------------+--------+---------------+---------+---------+-----------------------------------------------+------+---------------------------------+
All tables are indexed by primary key (as can be seen from the
execution plan). The column is declared as CHAR(8), though it is
VARCHAR(8) in some tables that have other VARCHAR columns. Tables are
MyISAM, and the server version is "4.1.7-standard".
Apparently MySQL's optimizer sees that it can use the primary key for
mirealsource_home_supplemental to do the query, but for some reason
decides not to.
I had a similar problem a few months ago, and it was solved when I
upgraded from a very old 3.x version of MySQL to this one.
I can fix the problem by requiring a STRAIGHT_JOIN, or by sorting by
"mirealsource_homes_supplemental.mls_num" instead. However, this
query is part of a larger framework that handles a wide variety of
queries, so I need to understand why this is happening instead of
tweaking individual cases.
Any idea why this might be happening, and how to fix it?
Thanks!
----ScottG.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
