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 query which is running in 15+ seconds

SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

How can I Rewrite/Optimize query to speed up execution time.

Table Structure

CREATE TABLE `stocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ST_StockCode` int(11) NOT NULL,
  `SM_StockCode` int(11) NOT NULL,
  `ST_ItemSize` decimal(18,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `stockcode` (`ST_StockCode`),
  KEY `sm_stockcode` (`SM_StockCode`)
) ENGINE=InnoDB

EXPLAIN PLAN

+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys      | key       | key_len | ref                | rows    | Extra                           |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
|  1 | SIMPLE      | t2    | ALL  | sm_stockcode       | NULL      | NULL    | NULL               | 1000545 | Using temporary; Using filesort |
|  1 | SIMPLE      | t1    | ref  | stockcode,idx_test | stockcode | 4       | lc.t2.SM_StockCode |       4 | Using index                     |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+

UPDATE SOME ROWS

SELECT * FROM stocks LIMIT 10;
+----+--------------+--------------+-------------+
| id | ST_StockCode | SM_StockCode | ST_ItemSize |
+----+--------------+--------------+-------------+
|  1 |       679783 |       678649 |        7.00 |
|  2 |       679789 |       688622 |        7.00 |
|  3 |       679792 |       679793 |        8.00 |
|  4 |       679792 |       686376 |        8.00 |
|  5 |       679793 |       679792 |        7.00 |
|  6 |       679793 |       686376 |        8.00 |
|  7 |       679795 |       679796 |        8.00 |
|  8 |       679796 |       679795 |        7.00 |
|  9 |       679797 |       617114 |        7.00 |
| 10 |       679797 |       627339 |        7.00 |
+----+--------------+--------------+-------------+

FOR ypercube

 SELECT * FROM similar_stocks WHERE ST_StockCode = 679792 OR SM_StockCode = 679792 ;
+-------+--------------+--------------+-------------+
| id    | ST_StockCode | SM_StockCode | ST_ItemSize |
+-------+--------------+--------------+-------------+
|     3 |       679792 |       679793 |        8.00 |
|     4 |       679792 |       686376 |        8.00 |
|     5 |       679793 |       679792 |        7.00 |
|  4774 |       686376 |       679792 |        7.00 |
| 50028 |       679792 |       679793 |        8.00 |
| 50029 |       679792 |       686376 |        8.00 |
| 50030 |       679793 |       679792 |        7.00 |
| 52798 |       686376 |       679792 |        7.00 |
+-------+--------------+--------------+-------------+
share|improve this question
    
Show us a few sample rows of the table. –  ypercube Oct 23 '13 at 11:56
    
@ypercube: Added sample rows –  Abdul Manaf Oct 23 '13 at 12:03
    
Can you select a value, say 679792 and run this: SELECT * FROM stocks WHERE ST_StockCode = 679792 OR SM_StockCode = 679792 ; and show that result? –  ypercube Oct 23 '13 at 12:06
    
@ypercube : added in question –  Abdul Manaf Oct 23 '13 at 12:32
    
I see that all the rows with SM_StockCode=679792 have itemsize = 7.00 Is that coincidence or not? –  ypercube Oct 23 '13 at 12:36

1 Answer 1

up vote 2 down vote accepted

Let's start by dissecting the original query, based on the provided example data:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Stocks
    (`id` int, `ST_StockCode` int, `SM_StockCode` int, `ST_ItemSize` int)
;

INSERT INTO Stocks
    (`id`, `ST_StockCode`, `SM_StockCode`, `ST_ItemSize`)
VALUES
    (1, 679783, 678649, 7.00),
    (2, 679789, 688622, 7.00),
    (3, 679792, 679793, 8.00),
    (4, 679792, 686376, 8.00),
    (5, 679793, 679792, 7.00),
    (6, 679793, 686376, 8.00),
    (7, 679795, 679796, 8.00),
    (8, 679796, 679795, 7.00),
    (9, 679797, 617114, 7.00),
    (10, 679797, 627339, 7.00)
;

Query 1:

SELECT 
        t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
    FROM
        `stocks` AS t1,
        `stocks` AS t2
    WHERE
        t1.`ST_StockCode` = t2.`SM_StockCode`
    GROUP BY t1.`ST_StockCode`
    ORDER BY t1.`id` ASC

Results:

| ST_STOCKCODE | SM_STOCKCODE | ST_ITEMSIZE |
|--------------|--------------|-------------|
|       679792 |       679792 |           7 |
|       679793 |       679793 |           8 |
|       679795 |       679795 |           7 |
|       679796 |       679796 |           8 |

First thing to note is that, this being an inner join, the two values ST_StockCode and SM_StockCode are always identical. In the following examples I therefore mention only one of them as it is easy to add the missing one back in.

Second, this query is using a non SQL Standard extension to the GROUP BY clause. The query groups by ST_StockCode and then references two other columns without an aggregate. MySQL will return for those columns the first value it comes across. It does not check if the values are distinct and you might get different results each execution. If we assume that the original query writer was aware of this behavior we can do this instead:

Query 2:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       617114 |           7 |
|       627339 |           7 |
|       678649 |           7 |
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |
|       686376 |           8 |
|       688622 |           7 |

This returns for each SM_StockCode the minimum ST_ItemSize (instead of a random one). However, it returns potentially more rows then the original query because there might be SM_StockCodes that do not have a matching ST_StockCode (as in the provided example data). However, that is easily fixed:

Query 3:

SELECT SM_StockCode, MIN(ST_ItemSize) ST_ItemSize
  FROM stocks AS t1
 WHERE EXISTS(SELECT 1 FROM stocks AS t2 WHERE t2.ST_StockCode = t1.SM_StockCode)
 GROUP BY SM_StockCode;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       679792 |           7 |
|       679793 |           8 |
|       679795 |           7 |
|       679796 |           8 |

Now only SM_StockCodes that have a matching ST_StockCodes are returned. (You have to duplicate the SM_StockCode column if you really need that value twice.)

With the query written this way, the indexing strategy becomes fairly obvious:

Create an index on ST_StockCode to support the EXISTS lookup and create a second index on SM_StockCode, ST_ItemSize to support the GROUP BY.

If you are sure that every SM_StockCode has a matching ST_StockCode (for example because a foreign key has been declared between the two, or because two of the analysts swear that this is always the case) you can shorten the query even more:

Query 4:

SELECT DISTINCT SM_StockCode, ST_ItemSize
  FROM stocks;

Results:

| SM_STOCKCODE | ST_ITEMSIZE |
|--------------|-------------|
|       678649 |           7 |
|       688622 |           7 |
|       679793 |           8 |
|       686376 |           8 |
|       679792 |           7 |
|       679796 |           8 |
|       679795 |           7 |
|       617114 |           7 |
|       627339 |           7 |

However, as in the example data excerpt of 10 random rows this condition is not given, the above result does not match in this case.

share|improve this answer

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.