Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel