In the last episode (Apr 21), Scott Gifford said:
> 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 get the correct EXPLAIN plan (test_homes first, then
test_homes_supplemental, then test_homes_stats) when I load your data
into Mysql 4.1.11, and the query itself comes back immediately. Is
upgrading an option? Even if you can't upgrade to 4.1, try going to
the latest in the 3.23 branch (3.23.58, released way back in Sep 2003).
3.23.49 is over 3 years old...
+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------+------+-------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------+------+-------+
| 1 | SIMPLE | test_homes | index | PRIMARY | price
| 4 | NULL | 100 | |
| 1 | SIMPLE | test_homes_supplemental | eq_ref | PRIMARY | PRIMARY
| 9 | test.test_homes.mls_num | 1 | |
| 1 | SIMPLE | test_homes_stats | eq_ref | PRIMARY | PRIMARY
| 9 | test.test_homes.mls_num | 1 | |
+----+-------------+-------------------------+--------+---------------+---------+---------+-------------------------+------+-------+
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
