Databases
StackOverflow2013
Postsdbo
POHow do I optimize this MySQL query?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I optimize this MySQL query?
    text
    copied!<p>I've got a really big problem, and it stems from a table with 50k+ records. </p> <p>This table looks something like this (+15 or so more columns that aren't too important):</p> <pre><code>table_1 date | name | email | num_x | num_y </code></pre> <p>I also have another table ON A DIFFERENT DB (same server) that looks something like this (+1 not important column):</p> <pre><code>table_2 name | comment | status </code></pre> <p>table_1 is updated daily with new entries (it is a feed table for use on other projects), which means there are a lot of repeat "name" rows. This is intended. table_2 contains comments and status notes about "name"s, but no repeat "name"s. </p> <p>I need to write a query that will select all "name"s from table_1 where the total of all num_x + num_y > X. So, for example, if this were a few rows...</p> <pre><code>2010-11-19 | john.smith | john.smith@example.com | 20 | 20 2010-11-19 | joel.schmo | joel.schmo@example.com | 10 | 10 2010-11-18 | john.smith | john.smith@example.com | 20 | 20 2010-11-18 | joel.schmo | joel.schmo@example.com | 10 | 10 </code></pre> <p>.. and I needed to find all "name"s with total num_x + num_y > 50, then I'd return john.smith | john.smith@example.com | 80 . I would also return john.smith's status and comment from the other DB.</p> <p>I wrote a query that I believe works fine, but it's problematic because it takes forever and a day to run. I also successfully retrieve records from the other db (I don't have that listed below).</p> <pre><code>SELECT name, email, SUM(num_x + num_y) AS total FROM table_1 GROUP BY name HAVING SUM(num_x + num_y) &gt; 100 ORDER BY total ASC </code></pre> <p>Is there a better way to go about this?</p> <p>Thanks everyone!</p> <p>Dylan</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