Databases
StackOverflow2013
Postsdbo
POOptimizing database query with up to 10mil rows as result
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimizing database query with up to 10mil rows as result
    text
    copied!<p>I have a MySQL Query that i need to optimize as much as possible (should have a load time below 5s, if possible)</p> <p>Query is as follow:</p> <pre><code>SELECT domain_id, COUNT(keyword_id) as total_count FROM tableName WHERE keyword_id IN (SELECT DISTINCT keyword_id FROM tableName WHERE domain_id = X) GROUP BY domain_id ORDER BY total_count DESC LIMIT ... </code></pre> <ul> <li>X is an integer that comes from an input</li> <li>domain_id and keyword_id are indexed</li> <li>database is on localhost, so the network speed should be max</li> </ul> <p>The subquery from the WHERE clause can get up to 10 mil results. Also, for MySQL seems really hard to calculate the COUNT and ORDER BY this count.</p> <p>I tried to mix this query with SOLR, but no results, getting such a high number of rows at once gives hard time for both MySQL and SOLR</p> <p>I'm looking for a solution to have the same results, no matter if i have to use a different technology or an improvement to this MySQL query.</p> <p>Thanks!</p> <hr> <p>Query logic is this:</p> <p>We have a domain and we are searching for all the keywords that are being used on that domain (this is the sub query). Then we take all the domains that use at least one of the keywords found on the first query, grouped by domain, with the number of keywords used for each domain, and we have to display it ordered DESC by the number of keywords used.</p> <p>I hope this make sense</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