MySQL Bugs: #71171: Optimizer executes inperformant subquery first

Bug #71171	Optimizer executes inperformant subquery first
Submitted:	18 Dec 2013 10:40	Modified:	4 Feb 2014 14:40
Reporter:	Martin Kirchner	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.14	OS:	Linux (Debian 7.1)
Assigned to:		CPU Architecture:	Any

Description:
This bug was filed after discussion with Øystein Grøvlen in http://forums.mysql.com/read.php?115,601675,601843#msg-601843.

The query: 
SELECT ADDRESS.KUNDENWERT, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.AGENT_ID, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.ACTIVITY_TYPE_ALLOWED, ADDRESS.DEACTIVATED 
FROM ADDRESS 
WHERE (((ADDRESS.DEACTIVATED = 0) 
AND (ADDRESS.GGUID IN (SELECT selectionvaluesrelation.TableGUID FROM selectionvaluesrelation WHERE selectionvaluesrelation.ValueGUID IN (0xD95B94336A9946A39CF5B58CFE772D8C)))) 
AND (ADDRESS.GGUID IN (SELECT TABLEGUID FROM ADDRESSORel WHERE EIMRight >= 64 AND (OID IN (3900, -364, -92, -397, -270, -97, -428, -99, -153, -8, -320, -423, -424, -355, -98, -279, -96, -427, -319, -101, -88, -305, -91, -94, -90, -326, -50, -93, -95, 0) OR (OID IN (-364, -394, -375, -328, -327, -326, -325, -316, -154, -153, 3880, 3881, 4222, 3879, 4191, 3915, 4470, 3902, 3907, 4377, 3058, 3611, 3061, 3918, 3919, 3882, 3916, 3917, 4318, 4118, 3923, 3922, 3921, 3920, 3927, 3926, 3925, 3924, 3884, 3885, 3886, 3887, 3901, 3903, 4287, 3897, 4286, 3896, 3899, 3898, 3953, 3893, 3892, 3895, 3894, 3889, 3888, 3891, 3890, 3914, 3912, 3913, 3906, 4107, 3904, 3905, 3910, 3911, 3908, 4381, 3909, 4112, 4316, 4437, 3808, 4312, 4292, 4188, 4190, 3059, 3060, 3062, -270, -8, -279, -320, -88, -99, -90, -96, -424, -50, -94, -305, -101, -423, -92, -91, -98, -93, -97, -319, -355, -95, -427, -397, -396, -428, -426, -89, -317, -357, -407) AND ADDRESS.ForeignEditPermission = 65535))))) 
AND (ADDRESS.ISDELETED = 0 AND ADDRESS.ISTEMPLATE = 0) 
ORDER BY ADDRESS.AGENT_ID ASC 
LIMIT 0, 100 

If the optimizer decides to evaluate the subselect on selectionvaluesrelation first the statement returns in about 2 seconds, however if it decides to evaluate the subquery on ADDRESSORel first the query runs about 5 minutes! 

I have two servers, a live and a test server. The two systems have an almost identical database. The live DB was copied to the test environment two days ago. 
Strangely, the two servers behave completely different. 

LIVE 
==== 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: selectionvaluesrelation 
type: ref 
possible_keys: IDX_SelectionValuesRelation_ValueGUID 
key: IDX_SelectionValuesRelation_ValueGUID 
key_len: 16 
ref: const 
rows: 85970 
Extra: Using index condition; Using temporary; Using filesort; Start temporary 
*************************** 2. row *************************** 
id: 1 
select_type: SIMPLE 
table: ADDRESS 
type: ref 
possible_keys: IDX_ADDRESS0_GGUID,IDX_GGUID_UNIQUE 
key: IDX_ADDRESS0_GGUID 
key_len: 17 
ref: selectionvaluesrelation.TableGUID 
rows: 1 
Extra: Using where; End temporary 
*************************** 3. row *************************** 
id: 1 
select_type: SIMPLE 
table: ADDRESSORel 
type: ref 
possible_keys: IDX_ADDRESSORel_OID_EIMRole,IDX_OID10,IDX_TableGUID11,IX_MIXOREL_V511,IDX_ADDRESSORel_Role,IDX_ADDRESSORel_Permission 
key: IDX_TableGUID11 
key_len: 16 
ref: selectionvaluesrelation.TableGUID 
rows: 1 
Extra: Using where; FirstMatch(ADDRESS) 
3 rows in set (0,00 sec) 

TEST 
==== 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: ADDRESSORel 
type: range 
possible_keys: IDX_ADDRESSORel_OID_EIMRole,IDX_OID10,IDX_TableGUID11,IX_MIXOREL_V511,IDX_ADDRESSORel_Role,IDX_ADDRESSORel_Permission 
key: IDX_ADDRESSORel_Role 
key_len: 4 
ref: NULL 
rows: 53475 
Extra: Using index condition; Using where; Using temporary; Using filesort; Start temporary 
*************************** 2. row *************************** 
id: 1 
select_type: SIMPLE 
table: ADDRESS 
type: eq_ref 
possible_keys: IDX_ADDRESS0_GGUID,IDX_GGUID_UNIQUE 
key: IDX_ADDRESS0_GGUID 
key_len: 16 
ref: svg.ADDRESSORel.TableGUID 
rows: 1 
Extra: Using where; End temporary 
*************************** 3. row *************************** 
id: 1 
select_type: SIMPLE 
table: selectionvaluesrelation 
type: ref 
possible_keys: IDX_SelectionValuesRelation_ValueGUID 
key: IDX_SelectionValuesRelation_ValueGUID 
key_len: 16 
ref: const 
rows: 75178 
Extra: Using index condition; Using where; FirstMatch(ADDRESS) 
3 rows in set (0.00 sec) 

I already tried ANALYZE TABLE and OPTIMIZE TABLE on the TEST environment, however that did not improve the performance. 

I will attach an anonymized dump of the tables mentioned above.
You will find further information in the forum posting: http://forums.mysql.com/read.php?115,601675,601675

How to repeat:
Execute the query. Maybe use a variation of the OIDs-values in the IN.
See forum post.

Thank you for the bug report. Please provide the dump table file. Thanks in advance.

See FTP server

Do the servers have same 'optimzer switch' setting (http://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html) and are they exactly same versions (ie. both 5.6.14)?

Peter
(not a MySQL/Oracle person)

Versions are identical 5.6.14.
Optimizer switches are identical:

LIVE
====

kirchner@localhost [(none)]>SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0,00 sec)

TEST
===kirchner@localhost [(none)]>SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)

Hello Martin,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

Optimizer trace for the given query is truncated.  I have reported this issue as Bug#71273 (Optimizer trace error on range analysis of index on a binary column).

I got around that issue by changing the server to not output the constants used for range scans in the trace. Then I got a valid optimizer trace, and I found the following in the trace when arriving at a prefix of the "good" plan:

"pruned_by_eq_ref_heuristic": true

Not yet sure exactly what happens here, but for some reason the good planned is pruned by the join optimizer. Fortunately, it is possible to turn off this pruning by setting a variable:

set optimizer_prune_level = 0;

Now I get the good plan.

Please also look at http://bugs.mysql.com/bug.php?id=71584. That is the same problem with a different query. Thanks.