StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimize SQL query on large-ish table
text
Body
copied!<p>First of all, this question regards MySQL 3.23.58, so be advised.</p> <p>I have 2 tables with the following definition:</p> <pre><code>Table A: id INT (primary), customer_id INT, offlineid INT Table B: id INT (primary), name VARCHAR(255) </code></pre> <p>Now, table A contains in the range of 65k+ records, while table B contains ~40 records. In addition to the 2 primary key indexes, there is also an index on the <em>offlineid</em> field in table A. There are more fields in each table, but they are not relevant (as I see it, ask if necessary) for this query.</p> <p>I was first presented with the following query (<em>query time: ~22 seconds</em>):</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>Now, each id in medie is associated with a different name, meaning you could group by id as well as name. A bit of testing back and forth settled me on this (<em>query time: ~6 seconds</em>):</p> <pre><code>SELECT a.name, COUNT(*) AS orders, COUNT(DISTINCT(b.kundeid)) AS leads FROM medie a INNER JOIN katalogbestilling_katalog b ON a.id = b.offline GROUP BY b.offline; </code></pre> <p>Is there any way to crank it down to "instant" time (max 1 second at worst)? I added the index on offlineid, but besides that and the re-arrangement of the query, I am at a loss for what to do. The EXPLAIN query shows me the query is using fileshort (the original query also used temp tables). All suggestions are welcome!</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