I have a correlated sub query, which is taking 16 Minutes to execute. How can I convert it to join or optimize it.

SELECT SSF.SM_StockCode, SSF.ST_ItemSize FROM  Stock AS SSF 
    WHERE SSF.SM_StockCode = (
        SELECT SM_StockCode
        FROM  Stock AS SSFAI
        WHERE SSFAI.ST_StockCode = SSF.ST_StockCode
        ORDER BY SSFAI.ST_ItemSize ASC
        LIMIT 1
    ) GROUP BY SSF.SM_StockCode

UPDATE : Added Explain Plan

| id | select_type        | table | type  | possible_keys     | key              | key_len | ref  | rows  | Extra                                        |
|  1 | PRIMARY            | SSF   | ALL   | NULL              | NULL             | NULL    | NULL | 45180 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | SSFAI | index | ST_StockCode_Indx | ST_ItemSize_Indx | 9       | NULL |     1 | Using where                                  |

UPDATE : Table Structure

 `ST_StockCode` varchar(20) NOT NULL,
 `SM_StockCode` varchar(20) NOT NULL,
 `ST_ItemSize` decimal(18,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ST_StockCode_Indx` (`ST_StockCode`),
 KEY `SM_StockCode_Indx` (`SM_StockCode`),
 KEY `ST_ItemSize_Indx` (`ST_ItemSize`)
  • What's up Abdul? Please, include the explain and the table structure. – Craig Efrein Oct 22 '13 at 7:20
  • 1
    You should also describe in words what you intend the output results to be: otherwise we might make incorrect assumptions based on just looking at the SQL statement and preclude optimisation options based on those assumptions. – David Spillett Oct 22 '13 at 9:27
  • @DavidSpillett : This is written by someone else, I have to optimize this, I also don't know what was the intention behind this.That's why i have put it here. – Abdul Manaf Oct 22 '13 at 9:40
  • Abdul, what is the version of MySQL being used here? There is an interesting optimization in global variables called materialization that might apply here. – Craig Efrein Oct 22 '13 at 14:37
  • @AbdulManaf Why did you make that edit? If you have a new problem (related or not), post a new question. – ypercubeᵀᴹ Nov 7 '13 at 14:32

You have SELECT SSF.ST_ItemSize after GROUP BY SSF.SM_StockCode, so you are using a column without aggregation in the SELECT list, a column that is not in the GROUP BY list.

This is not valid SQL by the 1992 standard.

Since ST_ItemSize is neither the primary key nor it has a unique constraint, the ST_ItemSize is not functionally dependent on it. This makes the SQL code invalid by the ISO/ANSI standards even in the 2003+ versions and will give indeterminate results (in the ST_ItemSize column).

MySQL unfortunately allows such queries (does no dependency check) and is the duty of the developer to ensure that a query will give determinate results.

So I would advise to remove this query from your code, discuss with the developers that wrote it or the ones that use it and find out what results it is supposed to return. Then you can rewrite it.

If you still want to have it running until you do that, the following will produce similar results - and will gain efficiency by an index on (ST_StockCode, ST_ItemSize, SM_StockCode):

SELECT ssf.SM_StockCode, MIN(ssf.ST_ItemSize) AS ST_ItemSize
FROM  Stock AS ssf 
  ( SELECT ST_StockCode, MIN(ST_ItemSize) AS ST_ItemSize 
    FROM  Stock 
    GROUP BY ST_StockCode 
  ) AS g
  ON  g.ST_StockCode = ssf.ST_StockCode
  AND g.ST_ItemSize = ssf.ST_ItemSize
GROUP BY ssf.SM_StockCode ;

It's a little hard to tell what you're trying to do; what are SM_StockCode and ST_StockCode? Here's my first rewrite:

    SM_StockCode, ST_ItemSize
    Stock AS S1
    NOT EXISTS (SELECT * FROM Stock AS S2 WHERE S1.SM_StockCode = S2.SM_StockCode AND S1.ST_StockCode = S2.ST_StockCode AND S2.ST_ItemSize > S1.ST_ItemSize)
    SM_StockCode, ST_ItemSize

It looks like what you need may be as simple as this, though:

    SM_StockCode, MIN(ST_ItemSize) AS ST_ItemSize

If you were to give us some sample data I could be more confident.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.