Hello,
I'm having a problem with query running very slowly. I run similar
queries on other tables all the time that perform as expected, and
this query used to run fine until I removed an explicit LEFT JOIN and
let the optimizer decide in what order to join two of the tables.
That fixed some other performance problems I was having, but seems to
have introduced this new one.
I'm using "Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)" on an
older copy of RedHat Linux 7.3 (kernel 2.4.25, libc6 2.2.5).
I've simplified the problem as much as I can and still reproduce it;
the actual tables I'm interested in are much larger, with many more
columns and rows.
The basic problem seems to be when I do a query sorting by price and
joining these three tables together, mysql resorts to "Using
temporary; Using filesort":
mysql> EXPLAIN SELECT test_homes.price,
test_homes.mls_num,
test_homes_supplemental.bathrooms,
test_homes_stats.detail_views
FROM test_homes,
test_homes_supplemental
LEFT JOIN test_homes_stats
ON test_homes.mls_num = test_homes_stats.mls_num
WHERE test_homes.mls_num = test_homes_supplemental.mls_num
ORDER BY test_homes.price
LIMIT 10;
+-------------------------+--------+---------------+---------+---------+---------------------------------+------+---------------------------------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------------------------+--------+---------------+---------+---------+---------------------------------+------+---------------------------------+
| test_homes_supplemental | ALL | PRIMARY | NULL | NULL |
NULL | 100 | Using temporary; Using filesort |
| test_homes | eq_ref | PRIMARY | PRIMARY | 9 |
test_homes_supplemental.mls_num | 1 | |
| test_homes_stats | eq_ref | PRIMARY | PRIMARY | 9 |
test_homes.mls_num | 1 | |
+-------------------------+--------+---------------+---------+---------+---------------------------------+------+---------------------------------+
That's very slow for 22,000 rows. I don't know why it's doing this,
since the column I'm sorting by is indexed; it seems like it should
get the data from the price index on test_homes, then use eq_ref to
join in test_homes_supplemental and test_homes_stats. Here are the
table definitions:
CREATE TABLE `test_homes` (
`mls_num` char(9) NOT NULL default '',
`price` mediumint(8) unsigned default NULL,
PRIMARY KEY (`mls_num`),
KEY `price` (`price`));
CREATE TABLE `test_homes_supplemental` (
`mls_num` char(9) NOT NULL default '',
`bathrooms` tinyint(3) unsigned default NULL,
PRIMARY KEY (`mls_num`));
CREATE TABLE `test_homes_stats` (
`mls_num` char(9) NOT NULL default '',
`detail_views` int(11) NOT NULL default '0',
PRIMARY KEY (`mls_num`));
Both test_homes and test_homes_supplemental contain one row for each
item; test_homes_stats contain zero or one rows for each item.
If I do explicit LEFT JOINs to tell MySQL what order to join in, I get
the results I expect:
mysql> EXPLAIN SELECT test_homes.price,
test_homes.mls_num,
test_homes_supplemental.bathrooms,
test_homes_stats.detail_views
FROM test_homes
LEFT JOIN test_homes_supplemental
ON test_homes.mls_num =
test_homes_supplemental.mls_num
LEFT JOIN test_homes_stats
ON test_homes.mls_num = test_homes_stats.mls_num
ORDER BY test_homes.price
LIMIT 10;
+-------------------------+--------+---------------+---------+---------+--------------------+------+-------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-------------------------+--------+---------------+---------+---------+--------------------+------+-------+
| test_homes | index | NULL | price | 4 |
NULL | 100 | |
| test_homes_supplemental | eq_ref | PRIMARY | PRIMARY | 9 |
test_homes.mls_num | 1 | |
| test_homes_stats | eq_ref | PRIMARY | PRIMARY | 9 |
test_homes.mls_num | 1 | |
+-------------------------+--------+---------------+---------+---------+--------------------+------+-------+
But this query is part of a larger query-generating framework, and I'd
rather not fill the framework will special cases; I'm not even sure
how I would identify that this query was a case that needs to be
handled specially.
I've put some sample data here:
http://www.flinthomes.net/~sgifford/test_homes.txt
I can reproduce the problem exactly with this data.
----ScottG.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
