StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to tune a 7-table-join MySQL count query where tables contain 30,000+ rows?
text
Body
copied!<p>I have an sql query that counts the number of results for a complex query. The actual select query is very fast when limiting to 20 results, but the count version takes about 4.5 seconds on my current tables after lots of optimizing.</p> <p>If I remove the two joins and where clauses on site tags and gallery tags, the query performs at 1.5 seconds. If I create 3 separate queries - one to select the pay sites, one to select the names and one to pull everything together - I can get the query down to .6 seconds, which is still not good enough. This would also force me to use a stored procedure since I will have to make a total of 4 queries in Hibernate.</p> <p>For the query "as is", here is some info:</p> <p>The Handler_read_key is 1746669<br> The Handler_read_next is 1546324</p> <p>The gallery table has 40,000 rows<br> The site table has 900 rows<br> The name table has 800 rows<br> The tag table has 3560 rows </p> <p>I'm pretty new to MySQL and tuning, and I have indexes on the: </p> <ul> <li>'term' column in the tag table</li> <li>'published' column in the gallery table</li> <li>'value' for the name table</li> </ul> <p>I am looking to get this query to 0.1 milliseconds.</p> <pre><code>SELECT count(distinct gallery.id) from gallery gallery inner join site site on gallery.site_id = site.id inner join site_to_tag p2t on site.id = p2t.site_id inner join tag site_tag on p2t.tag_id = site_tag.id inner join gallery_to_name g2mn on gallery.id = g2mn.gallery_id inner join name name on g2mn.name_id = name.id inner join gallery_to_tag g2t on gallery.id = g2t.gallery_id inner join tag tag on g2t.tag_id = tag.id where gallery.published = true and ( name.value LIKE 'sometext%' or tag.term = 'sometext' or site.`name` like 'sometext%' or site_tag.term = 'sometext' ) </code></pre> <p>Explain Data:</p> <pre><code>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+-------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+------------------------------------+ | 1 | SIMPLE | site | index | PRIMARY,nameIndex | nameIndex | 258 | NULL | 950 | Using index; Using temporary | | 1 | SIMPLE | gallery | ref | PRIMARY,publishedIndex,FKF44C775296EECE37,publishedSiteIdIndex | FKF44C775296EECE37 | 9 | production.site.id | 20 | Using where | | 1 | SIMPLE | g2mn | ref | PRIMARY,FK3EFFD7F8AFAD7A5E,FK3EFFD7F832C04188 | FK3EFFD7F8AFAD7A5E | 8 | production.gallery.id | 1 | Using index; Distinct | | 1 | SIMPLE | name | eq_ref | PRIMARY,valueIndex | PRIMARY | 8 | production.g2mn.name_id | 1 | Distinct | | 1 | SIMPLE | g2t | ref | PRIMARY,FK3DDB4D63AFAD7A5E,FK3DDB4D63E210FBA6 | FK3DDB4D63AFAD7A5E | 8 | production.g2mn.gallery_id | 2 | Using where; Using index; Distinct | | 1 | SIMPLE | tag | eq_ref | PRIMARY,termIndex | PRIMARY | 8 | production.g2t.tag_id | 1 | Distinct | | 1 | SIMPLE | p2t | ref | PRIMARY,FK29424AB796EECE37,FK29424AB7E210FBA6 | PRIMARY | 8 | production.gallery.site_id | 3 | Using where; Using index; Distinct | | 1 | SIMPLE | site_tag | eq_ref | PRIMARY,termIndex | PRIMARY | 8 | production.p2t.tag_id | 1 | Using where; Distinct | +----+-------------+--------------+--------+-------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+------------------------------------+ </code></pre> <p>Individual Count Speeds:</p> <pre><code>[SQL] select count(*) from gallery; Affected rows: 0 Time: 0.014ms Results: 40385 [SQL] select count(*) from gallery_to_name; Affected rows: 0 Time: 0.012ms Results: 35615 [SQL] select count(*) from gallery_to_tag; Affected rows: 0 Time: 0.055ms Results: 165104 [SQL] select count(*) from tag; Affected rows: 0 Time: 0.002ms Results: 3560 [SQL] select count(*) from site; Affected rows: 0 Time: 0.001ms Results: 901 [SQL] select count(*) from site_to_tag; Affected rows: 0 Time: 0.003ms Results: 7026 </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