Convert to Self JOIN 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

CREATE TABLE `Stock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `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`)
) ENGINE=MyISAM;
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 
  JOIN
  ( 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 ;