Hi Rick,
thanks for your reply. You'll find my answers below.
Rick James Wrote:
-------------------------------------------------------
> How many TableGUIDs correspond to
> 0xD95B94336A9946A39CF5B58CFE772D8C?
> Perhaps 1 GUID in one system and more than one in the other?
> If it will always be exactly 1, then use =, not
> IN. (But I suspect that is not your case.)
In the LIVE database it's 45705 and in test it's 45850.
>
> Don't use IN (SELECT...)
I just migrated from MySQL 5.0. There I used an INNER JOIN as an optimization which sped up the query execution by a factor of 100 in comparison to the subselect.
SELECT ADDRESS.KUNDENWERT, ADDRESS.MONITORING_STATE, ADDRESS.ISORGANISATION, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.AGENT_ID, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORPORATE_CUSTOMER_ID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_CAR, ADDRESS.CORP_ASSOC_PREMIUM_CAR, ADDRESS.SQ_GESAMT_VKDNR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.DEACTIVATED
FROM (ADDRESS0 AS ADDRESS INNER JOIN selectionvaluesrelation AS svr ON ADDRESS.GGUID = svr.TABLEGUID)
WHERE (((ADDRESS.DEACTIVATED = 0)
AND (svr.VALUEGUID = 0xA8B127D1235B4975838C5D41ED053ABC))
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)
LIMIT 100
However, on MySQL 5.6 the performance of this INNER JOIN is sometimes really bad. The listed OIDs depend on the logged in user and for some users the query does not return within 10 minutes.
The execution plan is:
*************************** 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; 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: svr
type: ref
possible_keys: IDX_SelectionValuesRelation_ValueGUID
key: IDX_SelectionValuesRelation_ValueGUID
key_len: 16
ref: const
rows: 119346
Extra: Using index condition; Using where
> Turn it into JOIN (SELECT...) ON ...
SELECT ADDRESS.KUNDENWERT, ADDRESS.MONITORING_STATE, ADDRESS.ISORGANISATION, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.AGENT_ID, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORPORATE_CUSTOMER_ID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_CAR, ADDRESS.CORP_ASSOC_PREMIUM_CAR, ADDRESS.SQ_GESAMT_VKDNR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.DEACTIVATED
FROM SVG.ADDRESS0 AS ADDRESS INNER JOIN (SELECT TableGUID FROM SVG.selectionvaluesrelation AS svr WHERE svr.VALUEGUID = 0xA8B127D1235B4975838C5D41ED053ABC) AS q ON ADDRESS.GGUID = q.TABLEGUID
WHERE ((ADDRESS.DEACTIVATED = 0)
AND (ADDRESS.GGUID IN (SELECT TABLEGUID FROM SVG.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)
LIMIT 100
This query returns in 0,7s on TEST and 0,4s on LIVE. That is weird. So probably I'll use this sort of statement.
The execution plan is:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
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; Start temporary
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
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: PRIMARY
table: <derived2>
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 16
ref: svg.ADDRESSORel.TableGUID
rows: 10
Extra: Using index
*************************** 4. row ***************************
id: 2
select_type: DERIVED
table: svr
type: ref
possible_keys: IDX_SelectionValuesRelation_ValueGUID
key: IDX_SelectionValuesRelation_ValueGUID
key_len: 16
ref: const
rows: 119346
Extra: Using index condition
4 rows in set (0.00 sec)
>
> So, even better would be to...
>
>
> CREATE TEMPORARY TABLE t1 (PRIMARY KEY
> (TableGUID))
> SELECT DISTINCT TABLEGUID
> FROM selectionvaluesrelation
> WHERE ValueGUID IN
> (0xD95B94336A9946A39CF5B58CFE772D8C);
>
> CREATE TEMPORARY TABLE t2 (PRIMARY KEY
> (TableGUID))
> SELECT DISTINCT TABLEGUID
> FROM ADDRESSORel
> WHERE EIMRight >= 64
> AND (OID IN (...) AND ADDRESS.ForeignEditPermission = 65535));
>
> and then JOIN to these tables instead of doing IN.
> Note how I created an index during the CREATE.
I'll give the temporary tables a thought. Unfortunately the execution of additional statements and thus the creation of temporary tables will not fit that easily in our application code.
And the second creation of a temp table won't work that easily since it references a column from ADDRESS.
>
> Or you could convert one subquery into a JOIN. Or
> (etc).
See above.
>
> You are reaching into very new code in 5.6 -- it
> is trying to do the temp table and indexing for
> you. But perhaps it is not optimal. Oracle would
> love to take your code and use it as a test case;
> would you like to give them your dataset?
I think that is possible if I anonymize the data, as long as I do not change the key columns or the columns referenced in my WHERE clause that shouldn't be a problem.
How could I submit the dataset?
Thank you again! Have a nice weekend.
Martin