MySQL Bugs: #27316: LEFT JOIN fails to use INDEX in 4.1.2 (used to work in 3.23)

Bug #27316	LEFT JOIN fails to use INDEX in 4.1.2 (used to work in 3.23)
Submitted:	21 Mar 2007 4:20	Modified:	17 Jan 2012 16:48
Reporter:	Dobromir Radichkov	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.2	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, join, key, left join, Optimizer, query, SQL

Description:
The following query used to run optimally under MySQL 3.23.58:

mysql> EXPLAIN
    -> SELECT *
    -> FROM `dmorg`.smf_topics t
    -> INNER JOIN `dmorg`.smf_messages AS m ON m.ID_MSG = t.ID_FIRST_MSG AND m.subject LIKE 'НОВИНИ:%'
    -> LEFT JOIN `dmorg`.smf_members AS mem ON ( m.ID_MEMBER = mem.ID_MEMBER )
    -> LEFT JOIN `dmorg`.smf_members AS edit_mem USE INDEX (realName) ON ( m.modifiedName = edit_mem.realName )
    -> WHERE (
    -> FIND_IN_SET( 59, mem.additionalGroups )
    -> OR mem.id_group =59
    -> OR edit_mem.id_group =59
    -> OR FIND_IN_SET( 59, edit_mem.additionalGroups )
    -> )
    -> ;
+----------+--------+---------------+--------------+---------+----------------+------+------------+
| table    | type   | possible_keys | key          | key_len | ref            | rows | Extra      |
+----------+--------+---------------+--------------+---------+----------------+------+------------+
| m        | range  | PRIMARY,test  | test         |      22 | NULL           |  280 | where used |
| t        | ref    | firstMessage  | firstMessage |       4 | m.ID_MSG       |    1 |            |
| mem      | eq_ref | PRIMARY       | PRIMARY      |       3 | m.ID_MEMBER    |    1 |            |
| edit_mem | ref    | realName      | realName     |      32 | m.modifiedName |    1 | where used |
+----------+--------+---------------+--------------+---------+----------------+------+------------+
4 rows in set (0.00 sec)

The query successfully chooses to use the index in the edit_mem table.

-------------------------------
-------------------------------
-------------------------------
-------------------------------

However, the query fails to use the index and runs extremely slowly under MySQL 4.1.2:

mysql> EXPLAIN
    -> SELECT *
    -> FROM `dmorg`.smf_topics t
    -> INNER JOIN `dmorg`.smf_messages AS m ON m.ID_MSG = t.ID_FIRST_MSG AND m.subject LIKE 'НОВИНИ:%'
    -> LEFT JOIN `dmorg`.smf_members AS mem ON ( m.ID_MEMBER = mem.ID_MEMBER )
    -> LEFT JOIN `dmorg`.smf_members AS edit_mem USE INDEX (realName) ON ( m.modifiedName = edit_mem.realName )
    -> WHERE (
    -> FIND_IN_SET( 59, mem.additionalGroups )
    -> OR mem.id_group =59
    -> OR edit_mem.id_group =59
    -> OR FIND_IN_SET( 59, edit_mem.additionalGroups )
    -> )
    -> ;
+----+-------------+----------+--------+---------------+--------------+---------+-------------------+------+--------------------------------------------------+
| id | select_type | table    | type   | possible_keys | key          | key_len | ref               | rows | Extra                                            |
+----+-------------+----------+--------+---------------+--------------+---------+-------------------+------+--------------------------------------------------+
|  1 | SIMPLE      | m        | range  | PRIMARY,test  | test         |      22 | NULL              |  289 | Using where                                      |
|  1 | SIMPLE      | t        | ref    | firstMessage  | firstMessage |       4 | dmorg.m.ID_MSG    |    1 |                                                  |
|  1 | SIMPLE      | mem      | eq_ref | PRIMARY       | PRIMARY      |       3 | dmorg.m.ID_MEMBER |    1 |                                                  |
|  1 | SIMPLE      | edit_mem | ALL    | realName      | NULL         |    NULL | NULL              |  896 | Range checked for each record (index map: 0x200) |
+----+-------------+----------+--------+---------------+--------------+---------+-------------------+------+--------------------------------------------------+
4 rows in set (0.00 sec)

How to repeat:
Sorry, impossible without submitting the actual tables/data.

Suggested fix:
N/A

Thank you for a problem report. Please, try to repeat with a newer version, 4.1.22, and inform about the results. In case of the same problem, please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for all the tables involved.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Feedback is still needed.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".