| 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: | |
| 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 | |
[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.
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