StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy isn't query using index and how to optimize it?
text
Body
copied!<p>I have an SQL query that runs for 114 seconds. The table contains 224000 rows.</p> <p>Why isn't it using key for 'products' table?</p> <p>Does anybody have some ideas how to optimize this query?</p> <pre><code>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 ) </code></pre> <pre><code> +--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+ |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| +--+------------------+---------+----+------------------------+----------+-------+-------+------+-----------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload