Eu tenho uma consulta que está sendo executada em mais de 15 segundos
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
Como posso reescrever/otimizar a consulta para acelerar o tempo de execução.
Estrutura da tabela
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
EXPLICAR O PLANO
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
| 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 |
+----+-------------+-------+------+--------------------+-----------+---------+--------------------+---------+---------------------------------+
ATUALIZE ALGUMAS LINHAS
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 |
+----+--------------+--------------+-------------+
PARA ypercubo
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 |
+-------+--------------+--------------+-------------+
