MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizer executes inperformant subquery first
Posted by: Rick James
Date: December 07, 2013 02:30PM

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.)

Don't use IN (SELECT...)
Turn it into JOIN (SELECT...) ON ...

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 (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 then JOIN to these tables instead of doing IN.
Note how I created an index during the CREATE.

Or you could convert one subquery into a JOIN. Or (etc).

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?

If you tables are huge, you will find GUIDs a terrible way to index them. This is because there is no 'locality of reference' due to the randomness of GUIDs.

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.