Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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:</p> <pre><code>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 &gt; '200000' AND it.item &lt; '400000' AND it.date LIKE '2013-01%' AND ( ( SELECT COUNT( id ) FROM structure WHERE final_item = it.item AND level &gt; '1' ) &lt;1 ) </code></pre> <p>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:</p> <pre><code>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 &gt; '1' GROUP BY final_item) AS struct_count ON it.item = struct_count.final_item WHERE it.type = 'Production' AND it.item &gt; '200000' AND it.item &lt; '400000' AND it.date LIKE '2013-01%' AND struct_count.count IS NULL </code></pre> <p>The <code>IS NULL</code> 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.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhy isn't query using index and how to optimize it?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJanick Bernet
    1. USJanick Bernet
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhy isn't query using index and how to optimize it?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThe 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.
      singulars
      1. PO
      1. USmend
    2. COAh 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 :)
      singulars
      1. PO
      1. USJanick Bernet
    3. COI 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 !
      singulars
      1. PO
      1. USmend
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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