MySQL Forums
Forum List  »  Optimizer & Parser

Optimizer executes inperformant subquery first
Posted by: Martin Kirchner
Date: December 06, 2013 07:12AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.