Alex Rivera | Logout

How to tune a 7-table-join MySQL count query where tables contain 30,000+ rows?

Asked 2010-06-30T16:38:51.217
9

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.

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.

For the query "as is", here is some info:

The Handler_read_key is 1746669
The Handler_read_next is 1546324

The gallery table has 40,000 rows
The site table has 900 rows
The name table has 800 rows
The tag table has 3560 rows

I'm pretty new to MySQL and tuning, and I have indexes on the:

  • 'term' column in the tag table
  • 'published' column in the gallery table
  • 'value' for the name table

I am looking to get this query to 0.1 milliseconds.

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 = 's
sql mysql count performance
Edit
Report

1 Answer

1

It appears that your WHERE clause may be the offender, especially the following:

lower(name2_.value) like ?

According to MySQL documentation:

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default.

You may not need the LOWER() function in your WHERE clause. Functions on the left side of the comparison prevent the use of indexes.

What do your LIKE values look like? If you are using a wildcard on the left side of the value, it prevents the use of indexes.

Try replacing your OR statements with UNION.

Try running the query without DISTINCT just to see how much it's affecting your query.

answered 2010-06-30T17:42:45.327

Your Answer