why does explain plan change?

[charlie w]

I'm something of a newbie to MySQL, so apologies in advance if posted to the wrong forum or I'm not clear about something.

I've been seeing optimizer behavior I don't understand. I've been trying to see how a particular query will be executed, and have seen different explain plan output without any changes to either the statement or the tables.

Below is the statement and explain output:
mysql> explain select t.id, t.scope from t, r, h
-> where t.state=2
-> and t.uri_k1=r.k1 and t.uri_k2=r.k2
-> and r.iid = h.tid
-> and h.hops <= 2
-> and h.iid in ( 472 )
-> limit 400;
+----+-------------+-------+------+--------------------+-------------+---------+-----------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+--------------------+-------------+---------+-----------------------+--------+--------------------------+
| 1 | SIMPLE | t | ref | uri_k1,state | state | 2 | const | 709154 | Using where |
| 1 | SIMPLE | r | ref | lookup,k1 | k1 | 8 | db.t.uri_k1 | 1 | Using where |
| 1 | SIMPLE | h | ref | from,tid | from | 8 | const,db.r.iid | 1 | Using where; Using index |
+----+-------------+-------+------+--------------------+-------------+---------+-----------------------+--------+--------------------------+
3 rows in set (0.00 sec)


The 't' table is indeed large, and 700K rows with a state of 2 is not unreasonable.

The weird thing is that I have seen the explain plan start instead with the 'h' table, and 5K rows. This is despite the fact that there have been no changes to the schema, no any changes to the stored data.

At first, explain would show the above output. Then, for a time, explain would show this different, more efficient plan. And now it is showing the above output again.

So the optimizer is choosing these different plans seemingly at random, though I don't really think that would be the case. Why does something like this happen, and more importantly, how can I ensure the optimizer uses the more efficient plan in this case?

Thanks,
Charlie

edited to add:
As a follow-up, I've run "analyze table" on all 3 tables involved in the query, and am still getting the same explain plan out of the optimizer.

I switched around the order of tables to "h, r, t", and added "straight_join" to the select, and now am getting the more efficient plan I saw at one point (see below).

So while the problem is apparently solved, I would prefer not to use straight_join, and would love to better understand what the optimizer is doing here and how to influence it.


mysql> explain select straight_join t.id, t.scope from h, r, t
-> where t.state=2
-> and t.uri_k1=r.k1 and t.uri_k2=r.k2
-> and r.iid = h.tid
-> and h.hops <= 2
-> and h.iid in ( 472 )
-> limit 400;
+----+-------------+-------+------+--------------------+--------+---------+-------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------+--------+---------+-------------------------------+------+--------------------------+
| 1 | SIMPLE | h | ref | from,tid | from | 4 | const | 5268 | Using where; Using index |
| 1 | SIMPLE | r | ref | lookup,k1 | lookup | 4 | h.tid | 403 | |
| 1 | SIMPLE | t | ref | uri_k1,state | uri_k1 | 18 | r.k1,r.k2 | 1 | Using where |
+----+-------------+-------+------+--------------------+--------+---------+-------------------------------+------+--------------------------+
3 rows in set (0.00 sec)



Edited 1 time(s). Last edit at 07/30/2007 12:44PM by charlie w.