MySQL Forums :: Optimizer & Parser :: Optimizer executes inperformant subquery first


Advanced Search

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


Subject Views Written By Posted
Optimizer executes inperformant subquery first 2422 Martin Kirchner 12/06/2013 07:12AM
Re: Optimizer executes inperformant subquery first 975 Rick James 12/07/2013 02:30PM
Re: Optimizer executes inperformant subquery first 1235 Martin Kirchner 12/07/2013 03:49PM
Re: Optimizer executes inperformant subquery first 1373 Øystein Grøvlen 12/09/2013 09:48AM
Re: Optimizer executes inperformant subquery first 1092 Martin Kirchner 12/18/2013 03:04AM
Re: Optimizer executes inperformant subquery first 1091 Øystein Grøvlen 12/19/2013 02:45AM
Re: Optimizer executes inperformant subquery first 963 Martin Kirchner 12/20/2013 02:24AM
Re: Optimizer executes inperformant subquery first 947 Øystein Grøvlen 12/23/2013 08:07AM
Re: Optimizer executes inperformant subquery first 983 Rick James 12/23/2013 01:11PM
Re: Optimizer executes inperformant subquery first 1091 Martin Kirchner 12/18/2013 05:16AM
Re: Optimizer executes inperformant subquery first 1076 Rick James 12/18/2013 06:49PM
Re: Optimizer executes inperformant subquery first 1012 Øystein Grøvlen 12/19/2013 02:49AM


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.