MySQL Bugs: #67461: Left Join / Left Outer Join not return after query

Bug #67461	Left Join / Left Outer Join not return after query
Submitted:	3 Nov 2012 12:35	Modified:	13 Nov 2012 0:00
Reporter:	Bob Schenk	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I believe the MySQL query has a problem with Left Join / Left Outer Join (See SQL and results below).  The Join, Right Join/Right Outer Join seem to work with no noticeable problem. The left Join/Left Outer Join never returns a completion.  The query will appear as running for 4 hours then gives the lost connection error message.  If try too many other actions while the left join query is running then MySQL for this user will become not responding.  MySQL may become not responding for all other users using the MySQL on this Database.

I think I can flip my queries to be right outer joins and continue.

Recently the latest MySQL has been installed on the server. I am using the latest Navicat on my PC.

Is there a reason for this problem and is there a solution for this problem?
 Thanks 
 

How to repeat:
===============================================
DROP TABLE IF EXISTS Bob_Temp_MSctr_RadioSctr_J;
CREATE  TABLE Bob_Temp_MSctr_RadioSctr_J ENGINE = MYISAM
SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_radiosector
JOIN market_msector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;
[SQL] DROP TABLE IF EXISTS Bob_Temp_MSctr_RadioSctr_J;
Affected rows: 0
Time: 0.037s

[SQL] 
CREATE  TABLE Bob_Temp_MSctr_RadioSctr_J ENGINE = MYISAM
SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_radiosector
JOIN market_msector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;
Affected rows: 18186
Time: 54.036s
=============================================
[SQL] DROP TABLE IF EXISTS Bob_Temp_MSctr_RadioSctr_J;
Affected rows: 0
Time: 0.037s

[SQL] 
CREATE  TABLE Bob_Temp_MSctr_RadioSctr_J ENGINE = MYISAM
SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_radiosector
JOIN market_msector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;
Affected rows: 18186
Time: 54.036s

[SQL] DROP TABLE IF EXISTS Bob_Temp_MSctr_RadioSctr_RO;
Affected rows: 0
Time: 0.036s

[SQL] 
CREATE  TABLE Bob_Temp_MSctr_RadioSctr_RO ENGINE = MYISAM
SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_radiosector
RIGHT OUTER JOIN market_msector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
RIGHT OUTER JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;
Affected rows: 19955
Time: 55.526s

==================================================================

DROP TABLE IF EXISTS Bob_Temp_MSctr_RadioSctr_LO;
CREATE  TABLE Bob_Temp_MSctr_RadioSctr_LO ENGINE = MYISAM
SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_msector
LEFT OUTER JOIN market_radiosector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
LEFT OUTER JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;

[SQL] DROP TABLE IF EXISTS Bob_Temp_MSctr_RadioSctr_LO;
Affected rows: 0
Time: 0.037s

[SQL] 
CREATE  TABLE Bob_Temp_MSctr_RadioSctr_LO ENGINE = MYISAM
SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_msector
LEFT OUTER JOIN market_radiosector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
LEFT OUTER JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;

=================================================================================

Thank you for the report.

LEFT JOIN can easily examine much more records than INNER or RIGHT JOINs.

Please send output of:

EXPLAIN SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_msector
LEFT OUTER JOIN market_radiosector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
LEFT OUTER JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;

EXPLAIN SELECT DISTINCT
market_msector.BSCID_KEY,
market_msector.CELLNAME,
market_msector.CELL,
market_radiosector.FREQ,
market_radiosector.MODULETYPE,
market_radiosector.BSCID_KEY as R_BSCID_KEY,
market_radiosector.CELLNAME as R_CELLNAME,
zz_cascade_cellid_sector_map.Cell_ID,
zz_cascade_cellid_sector_map.BSC_NAME,
zz_cascade_cellid_sector_map.CascadeID,
zz_cascade_cellid_sector_map.BSCID_KEY as C_BSCID_KEY,
zz_cascade_cellid_sector_map.Market99
FROM
market_msector
JOIN market_radiosector ON market_msector.BSCID_KEY = market_radiosector.BSCID_KEY AND market_msector.CELLNAME = market_radiosector.CELLNAME
JOIN zz_cascade_cellid_sector_map ON market_msector.CELL = zz_cascade_cellid_sector_map.Cell_ID AND market_msector.BSCID_KEY = zz_cascade_cellid_sector_map.BSCID_KEY
;

and output of SELECT COUNT(*) from all involved tables.

have added excel file containing the export of the requested info.

requested results

Attachment: results_explain_ counts.xls (application/vnd.ms-excel, text), 27.50 KiB.

Thank you for the feedback.

See numbers for JOIN:

35094*1350*501   = 23735826900

And for LEFT JOIN:

133286*501*35904 = 2397535772544

This is 2397535772544/23735826900 = 101.0092 times more rows!

So LEFT JOIN just have to examine so much rows, so it could not repeat fast enough. JOIN and RIGHT JOIN are not affected, because they don't select rows for each of column in your largest query. You need to read about how LEFT JOIN works and how you can improve query performance.