StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>OR</code> murders query performance, even with good indexes. It gets worse as tables get larger.</p> <p>This is horrifcly ugly, but it's likely to be faster (at the expense of readability, obviously). If MySQL only supported CTEs then this would be much, much neater.</p> <p>You could also look into writing a short batch and selecting the common part of the repeated query into a temp table and then doing everything against the temp table. You may or may not have to index the temp table for this to work out well, it depends on row counts really.</p> <p>(Note that <code>union</code> already does a <code>distinct</code>, so there's no need to do it again the <code>count</code> and force another sort)</p> <pre><code>select count(id) from ( SELECT 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%' UNION SELECT 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 tag.term = 'sometext' UNION SELECT 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 site.`name` like 'sometext%' UNION SELECT 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 site_tag.term = 'sometext' ) as x </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