Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
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
add comment

2 Answers

up vote 1 down vote accepted

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 ;
share|improve this answer
add comment

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:

SELECT
    SM_StockCode, ST_ItemSize
FROM
    Stock AS S1
WHERE
    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)
GROUP BY
    SM_StockCode, ST_ItemSize

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

SELECT
    SM_StockCode, MIN(ST_ItemSize) AS ST_ItemSize
FROM
    Stock
GROUP BY
    SM_StockCode

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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