EXPLAIN LEFT JOIN fails when joining on an empty table with constant in
WHERE clause
CREATE TABLE idx (
id mediumint,
KEY id (id),
) TYPE=MyISAM
CREATE TABLE hist (
id mediumint,
link mediumint,
KEY id (id,link)
) TYPE=MyISAM
Table idx is populated with thousands or records. Consider the following
query:
1. SELECT idx.id FROM idx
2. LEFT JOIN hist ON hist.link=idx.id AND hist.id=5
3. WHERE
4. hist.id=5 AND
5. hist.id IS NULL AND
6. idx.id=2
The query works perfectly, buy ONLY if table 'hist' is populated. If table
'hist' is empty then the query returns no results. This is easy to fix by
commenting out line #4. However by commenting out line 4 the optimizer
decides to use a temporary table which would slow it down.
Here is the EXPLAIN output as shown above with empty 'hist' table:
"Impossible WHERE noticed after reading const tables"
Here is the EXPLAIN output from above query when 'hist' has 1 record:
| table | type | possible_keys | key | key_len | ref | rows |
Extra
+-------+------+---------------+------+---------+------------+------+-------
------------------------------+
| idx | ref | id | id | 3 | const | 3 |
where used ; Using index (using temp without ln #4)
| hist | ref | id | id | 6 | const,idx.id | 2 |
where used; Using index; Not exists
+-------+------+---------------+------+---------+------------+------+-------
------------------------------+
It seems the optimizer is checking the constant in hist before it even
considers this is a LEFT JOIN. This is a problem on my Windows MySQL
3.23.33. Based on the EXPLAIN output of a simpler query I do not believe
3.22 does the same thing.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
