On R, 2005-09-02 at 15:07 -0400, Christopher Browne wrote:
> Michael Crozier wrote:

> >** When I say combining the "set parts", mean what was described earlier in 
> >this thread, manually reducing the common substatements of the query in ways 
> >that the optimizer doesn't seem able to:
> >
> >Instead of this:
> >========================================================
> > select [columns] from .sl_log_1
> > where 
> >   log_origin = <origin>
> >   and
> >     (   
> >        (   log_tableid in (  [tableid's from set 1]  and  <xid filter> )
> >        or
> >        (   log_tableid in (  [tableid's from set 2]  and  <xid filter> )
> >      )
> >     ) order by log_actionseq;
> >
> >This:
> >========================================================
> > select [columns] from .sl_log_1
> > where 
> >   log_origin = <origin>
> >   and  log_tableid in  [tableid's from sets 1 & 2]
> >   and  <xid filter> 
> > order by log_actionseq;
> >  
> >
> The thing is, the second query /isn't/ equivalent to the first one.

if both <xid filter> parts from the first query are the same, then 
the queries are equivalent.

> I expect that the results of the queries will be the same, but in order
> for it to be appropriate to actually change the query, we need to verify
> that the results are "provably" the same.


((A and X) or (B  and X)) ==> ((A or B) and X)

(A = log_tableid in ([set1])) and (B = log_tableid in ([set2])) 
   ==> (A or B) = log_tableid in ([set1] + [set2])

what more proof do you need ?

> I'll have to have a chat with Jan about that; 'tis on my list...
> 
> But in any case, it seems to me that we get the Big Gain from adding in
> an index on the XID column.  If that gets us 80% of the improvement, it
> may not be worth improving the query.

I guess that in my case the improvement was more like 95%.

Maybe it's not worth rewriting it to collapse the OR's, as for only a
few hundred rows, the difference will be only in sort, vs sorted-by-
index.

I guess that using some way to avoid long list of or's on tableid (maybe
by using temp tables, or just join on sl_table to hopefully get a hash
lookup), could cut down even more on CPU usage.

-- 
Hannu Krosing <hannu at skype.net>