Optimizer executes inperformant subquery first
Hi,
today I have a really strange behavior of the optimizer on MySQL 5.6.14 when evaluating a subquery.
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.
Is there a way to give the optimizer a hint, which subselect should be evaluated first or last? The subselect on the selectionvaluesrelation-table is always more restrictive than the one on ADDRESSORel.
Any other ideas?
I'd appreciate any advice.
Thanks in advance.
Kind regards,
Martin Kirchner