Why isn't query using index and how to optimize it?

Question

I have an SQL query that runs for 114 seconds. The table contains 224000 rows.

Why isn't it using key for 'products' table?

Does anybody have some ideas how to optimize this query?

EXPLAIN SELECT SUM( quantity * ( 
SELECT IF( netoweight =  '', weight, netoweight ) AS weight
FROM products
WHERE product_nr = it.item ) /1000 ) 
FROM  `inventory_transactions` it
WHERE it.type =  'Production'
AND it.item >  '200000'
AND it.item <  '400000'
AND it.date LIKE  '2013-01%'
AND (
(

SELECT COUNT( id ) 
FROM structure
WHERE final_item = it.item
AND level >  '1'
) <1
)


+--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+
|id|select_type       |table    |type|possible_keys           |key       |key_len|ref    |rows  |Extra      |
+--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+
|1 |PRIMARY           |it       |ref |item,type,date          |type      |50     |const  |111604|Using where|
+--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+
|3 |DEPENDENT SUBQUERY|structure|ref |final_item,level,level_2|final_item|4      |it.item|8     |Using where|
+--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+
|2 |DEPENDENT SUBQUERY|products |ALL |product_nr              |NULL      |NULL   |NULL   |3831  |Using where|
+--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+

Janick Bernet · Accepted Answer · 2013-09-06 06:20:13Z

1

MySQL is really bad at optimizing subqueries like this, so you have to help it bit, if possible, rewriting it using joins. For the first subquery this should be easy:

SELECT SUM( quantity * weight /1000 ) 
FROM  `inventory_transactions` it
JOIN (SELECT product_nr, IF( netoweight =  '', weight, netoweight ) AS weight
     FROM products) AS products
ON product.product_nr = it.item
WHERE it.type =  'Production'
AND it.item >  '200000'
AND it.item <  '400000'
AND it.date LIKE  '2013-01%'
AND (
(

SELECT COUNT( id ) 
FROM structure
WHERE final_item = it.item
AND level >  '1'
) <1
)

However, this will likely not yet solve the problem with not using keys on the product table, since the second query is more complicated. However, it should be rewritable using group by:

SELECT SUM( quantity * weight /1000 ) 
FROM  `inventory_transactions` it
JOIN (SELECT product_nr, IF( netoweight =  '', weight, netoweight ) AS weight
     FROM products) AS products,
ON product.product_nr = it.item
LEFT OUTER JOIN (SELECT final_item, COUNT( id ) AS count
    FROM structure
    WHERE level > '1'
    GROUP BY final_item) AS struct_count
ON it.item = struct_count.final_item
WHERE it.type =  'Production'
AND it.item >  '200000'
AND it.item <  '400000'
AND it.date LIKE  '2013-01%'
AND struct_count.count IS NULL

The IS NULL part is needed for products with structure count 0, since they will not match in the join. This query should be much easier for the query processor to use appropriate indexes. If it still won't use them, check you have them on the correct columns.

edited Sep 6, 2013 at 6:20

answered Sep 6, 2013 at 6:14

Janick Bernet

21.2k2 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mend Over a year ago

The first join brought the execution time from 114s to 3.5s. Thank you very much. I will try the second half of your answer also and post the results later.

Janick Bernet Over a year ago

Ah nice! Surprised though that was the culprit. Could you also try only the 2nd optimization without the first? Just to get all the numbers, would be interesting :)

mend Over a year ago

I now ran the query on a different month and the results are as follows. My original query: 197s. Your first query: 0.48s. Your second query without the first JOIN: takes too much time and hangs. Your second query with JOIN and LEFT OUTER JOIN: 0.45s. Quite nice optimization :). Thank you very much @inflagranti !

Collectives™ on Stack Overflow

Why isn't query using index and how to optimize it?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related