StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>How is kundeid defined? It would be helpful to see the full schema for both tables (as generated by MySQL, ie. with indexes) as well as the output of EXPLAIN with the queries above.</p> <p>The easiest way to debug this and find out what is your bottleneck would be to start removing fields, one by one, from the query and measure how long does it take to run (remember to run RESET QUERY CACHE before running each query). At some point you'll see a significant drop in the execution time and then you've identified your bottleneck. For example:</p> <pre><code>SELECT b.name, COUNT(*) AS orders, COUNT(DISTINCT(a.kundeid)) AS leads FROM katalogbestilling_katalog a, medie b WHERE a.offlineid = b.id GROUP BY b.name </code></pre> <p>may become</p> <pre><code>SELECT b.name, COUNT(DISTINCT(a.kundeid)) AS leads FROM katalogbestilling_katalog a, medie b WHERE a.offlineid = b.id GROUP BY b.name </code></pre> <p>to eliminate the possibility of "orders" being the bottleneck, or</p> <pre><code>SELECT b.name, COUNT(*) AS orders FROM katalogbestilling_katalog a, medie b WHERE a.offlineid = b.id GROUP BY b.name </code></pre> <p>to eliminate "leads" from the equasion. This will lead you in the right direction.</p> <p><strong><em>update</em></strong>: I'm not suggesting removing any of the data from the final query. Just remove them to reduce the number of variables while looking for the bottleneck. Given your comment, I understand</p> <pre><code>SELECT b.name FROM katalogbestilling_katalog a, medie b WHERE a.offlineid = b.id GROUP BY b.name </code></pre> <p>is still performing badly? This clearly means it's either the join that is not optimized or the group by (which you can test by removing the group by - either the JOIN will be still slow, in which case that's the problem you need to fix, or it won't - in which case it's obviously the GROUP BY). Can you post the output of</p> <pre><code>EXPLAIN SELECT b.name FROM katalogbestilling_katalog a, medie b WHERE a.offlineid = b.id GROUP BY b.name </code></pre> <p>as well as the table schemas (to make it easier to debug)?</p> <p><strong><em>update #2</em></strong></p> <p>there's also a possibility that all of your indeces are created correctly but you have you mysql installation misconfigured when it comes to max memory usage or something along those lines which forces it to use disk sortation.</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