Bug #5271 Improperly optimized JOIN query
Submitted: 27 Aug 2004 22:23 Modified: 29 Aug 2004 10:41
Reporter: Dave Dyer Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: ISAM storage engine Severity:S3 (Non-critical)
Version: Ver 12.22 Distrib 4.0.20a, for Win95/Wi OS:Microsoft Windows (win2k)
Assigned to: CPU Architecture:Any

[27 Aug 2004 22:23] Dave Dyer
Description:
EXPLAIN SELECT j_numberid,j_yearid,count(color_indicator) FROM jobpage LEFT JOIN job ON jobpage.number=job.number 
  WHERE pagenum>0 AND pagenum<=job.numpages AND j_numberid is NOT NULL AND j_yearid IS NOT NULL  AND 
   ((pagenum-1)&15)<2  AND color_indicator is not null AND color_indicator!='no color'  
    GROUP BY jobpage.number ORDER BY j_numberid,j_yearid;

+---------+--------+----------------------+---------+---------+----------------+--------+----------------------------------------------+
| table   | type   | possible_keys        | key     | key_len | ref            | rows   | Extra                                        |
+---------+--------+----------------------+---------+---------+----------------+--------+----------------------------------------------+
| jobpage | ALL    | number,pagenum,num   | NULL    |    NULL | NULL           | 407207 | Using where; Using temporary; Using filesort |
| job     | eq_ref | PRIMARY,number,jinfo | PRIMARY |      32 | jobpage.number |      1 | Using where                                  |
+---------+--------+----------------------+---------+---------+----------------+--------+----------------------------------------------+
2 rows in set (0.05 sec)

The problem is that the "available key" number on JOB is not used,
where the join apprears to be a simple match of job.number with
jobpage.number

How to repeat:
try it

Suggested fix:
optimize it
[29 Aug 2004 10:41] Alexander Keremidarski
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.