StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Count's are often slow as they require fetching all data returned by the cursor in order to figure out how many rows would actually be fetched. </p> <p>How long does it take to do a count on each of the individual tables? Add up the total times - if it's more than 0.1 milliseconds I don't think you'll be able to get the query to execute as fast as you'd like. As far as ways to speed it up goes, you could try pushing some of the WHERE clause criteria into a sub-select, as in</p> <pre><code>select count(distinct this_.id) as y0_ from (select * from gallery where published=?) this_ inner join site site3_ on this_.site_id=site3_.id inner join site_to_tag list7_ on site3_.id=list7_.site_id inner join tag sitetag4_ on list7_.tag_id=sitetag4_.id inner join gallery_to_name names9_ on this_.id=names9_.gallery_id inner join name name2_ on names9_.name_id=name2_.id inner join gallery_to_tag list11_ on this_.id=list11_.gallery_id inner join tag tag1_ on list11_.tag_id=tag1_.id where lower(name2_.value) like ? or tag1_.term=? or lower(site3_.name) like ? or lower(this_.description) like ? or sitetag4_.term=? </code></pre> <p>How many fields are on each of these tables? Can you use sub-selects to cut down on the amount of data the database has to join together, or do you really need all the columns?</p> <p>The presence of three LIKE predicates is going to slow things down, as will the use of the LOWER function in the WHERE clause. If you need to be able to do case-insensitive compares it might be better to have two fields, one in 'normal' (as typed in) case and one stored in lower (or UPPER) case to do insensitive searches on. You could use a trigger to keep the lower/UPPER one in sync with the 'normal' case version.</p> <p>I hope this helps.</p> <p>EDIT:</p> <p>Looking at the EXPLAIN PLAN output it doesn't appear that the fields used in your WHERE clause are indexed - or at least it appears the indexes aren't being used. This could be a by-product of all the OR predicates in the WHERE. If these fields aren't indexed, you might try indexing them.</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