StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, so 900K rows isn't a massive table, it's reasonably big but and your queries really shouldn't be taking that long.</p> <p>First things first, which of the 3 statements above is taking the most time?</p> <p>The first problem I see is with your first query. Your WHERE clause doesn't include an indexed column. So this means that it has to do a full table scan on the entire table.</p> <p>Create an index on the "data_updated" column, then run the query again and see what that does for you.</p> <p>If you don't need the hash's and are only using them to avail of the dark magic then remove them completely.</p> <p>Edit: Someone with more SQL-fu than me will probably reduce your whole set of logic into one SQL statement without the use of the temporary tables. </p> <p>Edit: My SQL is a little rusty, but are you joining twice in the third SQL staement? Maybe it won't make a difference but shouldn't it be :</p> <pre><code>SELECT temp1.element_id, temp1.category, temp1.source_prefix, temp1.source_name, temp1.date_updated, AVG(temp1.value) AS avg_value, SUM(temp1.value * temp1.weight) / SUM(weight) AS rating FROM temp1 LEFT JOIN temp2 ON temp1.subcat_hash = temp2.subcat_hash WHERE temp1.date_updated = temp2.maxdate GROUP BY temp1.cat_hash; </code></pre> <p>or </p> <pre><code>SELECT temp1.element_id, temp1.category, temp1.source_prefix, temp1.source_name, temp1.date_updated, AVG(temp1.value) AS avg_value, SUM(temp1.value * temp1.weight) / SUM(weight) AS rating FROM temp1 temp2 WHERE temp2.subcat_hash = temp1.subcat_hash AND temp1.date_updated = temp2.maxdate GROUP BY temp1.cat_hash; </code></pre>

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