Eduardo <[EMAIL PROTECTED]> wrote: > At 19:42 14/06/2006, you wrote: > >On Jun 14, 2006, at 16:42 UTC, [EMAIL PROTECTED] wrote: > > > > > Might go a lot faster if you put a "+" in fron of > > > recID. Like this: > > > > > > SELECT * FROM table WHERE +recID IN (...) ORDER BY dateFld > > > >What magic is this? I checked both lang_expr.html and > >lang_select.html but can't find any such syntax -- perhaps I missed > >it. What does it do? > > The '+' mads the optimizer, so it will not use any optimization. In > these case, the optimization engine (at where.c) takes a bad > decission. It's not a feature, is a side effect of the optimizer design. >
Eduardo is essentially correct (even if his English is a tad off :-)). Putting "+" in front of the name of a column in the WHERE clause (or in the ORDER BY clause) disqualifies that column from use by the optimizer. So instead of using the index on recID to satisfy the WHERE clause as it normally would, the extra "+" causes SQLite to chooses the index on dateFld to satisfy the ORDER BY clause. Whether or not that is a win or a loss depends on the content of your table and is best determined by experimentation. -- D. Richard Hipp <[EMAIL PROTECTED]>
