Optimize MySQL Self JOIN Query 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 |
+-------+--------------+--------------+-------------+
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.