On Thu, Sep 23, 2010 at 12:53, Erik Huelsmann <ehu...@gmail.com> wrote: > On Wed, Sep 22, 2010 at 11:25 PM, Greg Stein <gst...@gmail.com> wrote: >... >> Wouldn't it be better to do: >> >> where wc_id = ?1 and local_relpath = ?2 >> and op_depth = (select max(op_depth) from nodes >> where wc_id=?1 and local_relpath=?2 and op_depth > 0); >> >> It seems that eliminating the "order by" and "limit", in favor of >> max() will tell sqlite what we're really searching for: the maximal >> value. > > I wrote those queries like that because Bert said it would introduce an > aggregation function - at the time he said it, that sounded like it was > something negative.
I don't think we should be second-guessing the sqlite query optimizer unless and until we need to. The 'select max(op_depth)' query can be optimized. If sqlite does not, then that is not our problem until some performance data shows these queries are killing us. >... >> > @@ -312,7 +312,7 @@ WHERE wc_id = ?1 AND local_relpath = ?2; >> > update nodes set translated_size = ?3, last_mod_time = ?4 >> > where wc_id = ?1 and local_relpath = ?2 >> > and op_depth = (select op_depth from nodes >> > - where wc_id = ?1 and local_relpath = ?2 >> > + where wc_id = ?1 and local_relpath = ?2 and op_depth >> > > 0 >> > order by op_depth desc >> > limit 1); >> >> This one does not. The rest of the statements you converted all use >> the "in" variant. > > The "in" variant is probably better, because - especially with the op_depth >> 0 restriction - the result set can probably be empty. Excellent point! Thanks. Cheers, -g
