List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 21 2005 4:12pm
Subject:Re: Slow query: mysql not using correct indexes?
View as plain text  
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
	dnelson@stripped
Thread
Slow query: mysql not using correct indexes?Scott Gifford21 Apr
  • Re: Slow query: mysql not using correct indexes?Dan Nelson21 Apr