Databases
StackOverflow2013
Postsdbo
POSlow MYSQL query with sub queries using COUNT
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSlow MYSQL query with sub queries using COUNT
    text
    copied!<p>Right I have no idea why but this query takes well over 6 seconds to execute, index's are all setup correctly and if I run each query separately it works great with less than 0.5 seconds to execute.</p> <p>Here is the query</p> <pre><code>SELECT c.supplier_id, supplier_name, address1, address2, address3, address4, suppliertype, postcode, contact_name, (SELECT COUNT(*) FROM supplier_questions q1 WHERE c.supplier_id = q1.supplier_id AND q1.incomplete = '0') AS questions, IF (active=1,'Yes', IF (active=2, 'NCR Only','Inactive')) AS rated, (SELECT COUNT(*) FROM supplier_questions q2 WHERE c.supplier_id = q2.supplier_id AND q2.reviewed = '1') AS reviewed, questapproved, ss.supplier_no AS supplier_no FROM suppliers c INNER JOIN supplier_site ss ON c.supplier_id = ss.supplier_id WHERE c.supplier_id != '0' AND ss.site_id = '2' GROUP BY c.supplier_id ORDER BY c.supplier_name ASC LIMIT 0, 20 </code></pre> <p>Results of the Explain query is as follows </p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ss ref site_id,supplier_id site_id 4 const 1287 Using where; Using temporary; Using filesort 1 PRIMARY c eq_ref PRIMARY PRIMARY 4 ss.supplier_id 1 3 DEPENDENT SUBQUERY q2 ref supplier_id,reviewed reviewed 4 const 263 Using where 2 DEPENDENT SUBQUERY q1 ref supplier_id,incomplete incomplete 4 const 254 Using where </code></pre> <p>The reason the count queries are in there is because I need to know the number of rows from those tables, this can't be done in another query as the results also need to be sorted by those values :(</p>
 

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