Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

On Mon, Aug 17, 2009 at 3:15 AM, Dan Kennedy  wrote:

> >> The INDEXED BY feature was introduced to address concerns that SQLite
> >> might
> >> suddenly start using a different plan for a query in the field than
> >> it
> >> did
> >> in the office during testing. Either because somebody ran ANALYZE, or
> >> because
> >> the SQLite version was upgraded. In this situation, some users
> >> consider it
> >> better to throw an exception than to run the query with a different,
> >> possibly
> >> slower, plan.
> >
> > Confusion reigns supreme. Your second last paragraph says (about your
> > last scenario) that it uses index i1 instead of the apparently better
> > index i2 -- no exception throwing. Your last paragraph indicates
> > that in
> > this case an exception would be thrown.
>
> I guess I got that wrong then. Said users considered it better to throw
> an error if the index that the author of the SQL query expected it to
> use had been removed or radically altered.
>
> Dan.
>


Sorry for muddying the waters.  I was just trying to make clear that INDEXED
BY isn't intended to be used as a tuning mechanism for index selection.  My
statement should have been clearer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

>> The INDEXED BY feature was introduced to address concerns that SQLite
>> might
>> suddenly start using a different plan for a query in the field than  
>> it
>> did
>> in the office during testing. Either because somebody ran ANALYZE, or
>> because
>> the SQLite version was upgraded. In this situation, some users
>> consider it
>> better to throw an exception than to run the query with a different,
>> possibly
>> slower, plan.
>
> Confusion reigns supreme. Your second last paragraph says (about your
> last scenario) that it uses index i1 instead of the apparently better
> index i2 -- no exception throwing. Your last paragraph indicates  
> that in
> this case an exception would be thrown.

I guess I got that wrong then. Said users considered it better to throw
an error if the index that the author of the SQL query expected it to
use had been removed or radically altered.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 17/08/2009 2:37 PM, Dan Kennedy wrote:
> On Aug 17, 2009, at 11:05 AM, John Machin wrote:
> 
>> On 17/08/2009 11:41 AM, Shane Harrelson wrote:
>>> INDEXED BY doesn't allow you to specify which index to use.  It  
>>> just causes
>>> the query to fail if SQLite thinks it should use an index different  
>>> then the
>>> one specified by the INDEXED BY clause.
>> Oh. The docs say "If index-name does not exist or cannot be used for  
>> the
>> query, then the preparation of the SQL statement fails." Please  
>> consider
>>  submitting a docs bug report (with evidence).
> 
> The two statements are not incompatible.

AFAICT, NONE of the scenarios you outline below fit Shane's statement 
"It just causes the query to fail if SQLite thinks it should use an 
index different then the one specified by the INDEXED BY clause."

For a scenario to match that statement, there must be at least TWO 
indexes on the table.

Only one scenario has two indexes. In that case, whether SQLite "thinks" 
(looks at the analyze results) or not, you say that the result is that 
it goes with the index in the INDEXED BY clause ... so your description 
of that scenario doesn't match Shane's statement at all.

On the other hand, all your scenarios match the above doc excerpt -- 
provided of course that the "use" is interpreted as "use 
beneficially/advantageously".

> 
> What happens in the code is that for a table with an "INDEXED BY" clause
> attached, SQLite does not consider any other indexes or a linear scan  
> when
> planning a query.

"SQLite does not consider any other indexes" is NOT compatible with 
Shane's "SQLite thinks it should use an index different ...".

> SQLite will not do a full scan of an index (unless  
> this
> helps with an ORDER BY clause). If this means no valid plan is found,  
> query
> compilation fails.
> 
> So if you try this:
> 
>CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a);
>SELECT * FROM t1 INDEXED BY i1;
> 
> Then the SELECT statement fails (to prepare) as SQLite cannot find a  
> plan
> where it can use index i1 (advantageously).

Yes, indeed, that select statement constitutes a user bug; it is a 
nonsense, quite irrespective of the presence/absence of i1 or any other 
index, creating/deleting ANALYZE results, or SQLite version changes.

  But if you do this:
> 
>SELECT * FROM t1 INDEXED BY i1 ORDER BY a;
> 
> Then this will prepare and run fine. The following will also work:
> 
>CREATE INDEX i2 ON t1(b);
>SELECT * FROM t1 INDEXED BY t1 WHERE b=10 ORDER BY a;
> 
> In this case, depending on the stats collected by any ANALYZE command,  
> SQLite
> will normally use index i2 to optimize the b=10 constraint. But with the
> INDEXED BY, it uses index i1 to optimize the ORDER BY instead.  
> Presumably the
> user knows something about the contents of table t1 that has allowed  
> her to
> conclude that using index i1 will be more efficient in this case.
> 
> The INDEXED BY feature was introduced to address concerns that SQLite  
> might
> suddenly start using a different plan for a query in the field than it  
> did
> in the office during testing. Either because somebody ran ANALYZE, or  
> because
> the SQLite version was upgraded. In this situation, some users  
> consider it
> better to throw an exception than to run the query with a different,  
> possibly
> slower, plan.

Confusion reigns supreme. Your second last paragraph says (about your 
last scenario) that it uses index i1 instead of the apparently better 
index i2 -- no exception throwing. Your last paragraph indicates that in 
this case an exception would be thrown.

The docs are likewise confused -- after the early piece that I quoted 
(which supports using the INDEXED BY index unless it has vanished or 
would result in a full scan), we find this "The intent of the INDEXED BY 
clause is to raise a run-time error if a schema change, such as dropping 
or creating an index, causes the query plan for a time-sensitive query 
to change. The INDEXED BY clause is designed to help detect undesirable 
query plan changes during regression testing."

SUMMARY:
Three worries with "INDEXED BY i1":

(1) i1 doesn't exist => no argument, raise an exception.

(2) i1 cannot be used advantageously e.g. there is nothing in a WHERE 
clause or ORDER BY clause that fits i1 => no argument, raise an exception.

(3) i1 exists and could be used (as in better than no index at all) but 
there is another index i2 that looks even better => option (a) use i1; 
(b) raise an exception

IMHO whichever of (a) and (b) actually happens, some clarification in 
the docs might be useful.

Cheers,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

On Aug 17, 2009, at 11:05 AM, John Machin wrote:

> On 17/08/2009 11:41 AM, Shane Harrelson wrote:
>> INDEXED BY doesn't allow you to specify which index to use.  It  
>> just causes
>> the query to fail if SQLite thinks it should use an index different  
>> then the
>> one specified by the INDEXED BY clause.
>
> Oh. The docs say "If index-name does not exist or cannot be used for  
> the
> query, then the preparation of the SQL statement fails." Please  
> consider
>  submitting a docs bug report (with evidence).

The two statements are not incompatible.

What happens in the code is that for a table with an "INDEXED BY" clause
attached, SQLite does not consider any other indexes or a linear scan  
when
planning a query. SQLite will not do a full scan of an index (unless  
this
helps with an ORDER BY clause). If this means no valid plan is found,  
query
compilation fails.

So if you try this:

   CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a);
   SELECT * FROM t1 INDEXED BY i1;

Then the SELECT statement fails (to prepare) as SQLite cannot find a  
plan
where it can use index i1 (advantageously). But if you do this:

   SELECT * FROM t1 INDEXED BY i1 ORDER BY a;

Then this will prepare and run fine. The following will also work:

   CREATE INDEX i2 ON t1(b);
   SELECT * FROM t1 INDEXED BY t1 WHERE b=10 ORDER BY a;

In this case, depending on the stats collected by any ANALYZE command,  
SQLite
will normally use index i2 to optimize the b=10 constraint. But with the
INDEXED BY, it uses index i1 to optimize the ORDER BY instead.  
Presumably the
user knows something about the contents of table t1 that has allowed  
her to
conclude that using index i1 will be more efficient in this case.

The INDEXED BY feature was introduced to address concerns that SQLite  
might
suddenly start using a different plan for a query in the field than it  
did
in the office during testing. Either because somebody ran ANALYZE, or  
because
the SQLite version was upgraded. In this situation, some users  
consider it
better to throw an exception than to run the query with a different,  
possibly
slower, plan.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 17/08/2009 11:41 AM, Shane Harrelson wrote:
> INDEXED BY doesn't allow you to specify which index to use.  It just causes
> the query to fail if SQLite thinks it should use an index different then the
> one specified by the INDEXED BY clause.

Oh. The docs say "If index-name does not exist or cannot be used for the 
query, then the preparation of the SQL statement fails." Please consider 
  submitting a docs bug report (with evidence).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Simon Slavin]

On 17 Aug 2009, at 2:39am, Shane Harrelson wrote:

> No.  It only collects/updates stats when you explicitly call the  
> ANALYZE.
>
> On Sat, Aug 15, 2009 at 2:48 AM, Jim Showalter   
> wrote:
>
>> It doesn't collect those statistics automatically, as part of query
>> plan optimization?

Would people please add new text below any existing text they quote ?   
That way we can follow the thread.  If you don't think people need to  
read existing text before they read your new text, there's no reason  
to include it in your post, so don't.

Shane is right: the table that ANALYZE makes up is only created/ 
updated by that command.  Without that command, SQLite makes some  
assumptions about the distribution of keys values in each index.  The  
assumptions are generally good, but ANALYZE is better and will give  
results which will continue to be better until you change the  
distribution of the data in your tables.

But to get back to the original question, ANALYZE should be a better  
answer to the problem than a programmer trying to set the best index  
manually.  ANALYZE knows better than you which index is better.  Not  
only will ANALYZE allow SQLite to pick the index which does the search  
faster, but ANALYZE means that SQLite don't have to spend the  
necessary time working out which index will do the search faster.  If  
you are producing a big dataset, perhaps one intended for read-only  
use, doing an ANALYZE just before you burn your DVDs (or whatever)  
will help.

You can read some information on how SQLite works out which index to  
use here

http://www.sqlite.org/cvstrac/wiki?p=QueryPlans

Which describes a rarely documented SQLite command and otherwise  
supplements the normal documentation at

http://sqlite.org/optoverview.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

INDEXED BY doesn't allow you to specify which index to use.  It just causes
the query to fail if SQLite thinks it should use an index different then the
one specified by the INDEXED BY clause.

On Sun, Aug 16, 2009 at 7:59 PM, His Nerdship  wrote:

>
> Hi Pavel,
>
> > Does INDEXED BY clause work for you?
> > http://www.sqlite.org/lang_select.html
>
> The page suggests that INDEXED BY can only be used in single-table queries.
> The report queries also join other smaller tables, some of them from an
> attached database, so I don't think this will work.
> However, you have given me an idea - maybe I could break it down to two
> queries. Use the INDEX BY on the big table to get a subset table, and do
> another SELECT on the subset. Thanks.
> Sholto (His Nerdship)
>
>
>
> > Is there any way the code can 'suggest' SQLite use a certain index?
>
>
> Pavel
>
> --
> View this message in context:
> http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24999340.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Shane Harrelson]

No.  It only collects/updates stats when you explicitly call the ANALYZE.

On Sat, Aug 15, 2009 at 2:48 AM, Jim Showalter <j...@jimandlisa.com> wrote:

> It doesn't collect those statistics automatically, as part of query
> plan optimization?
>
> - Original Message -
> From: "Dan Kennedy" <danielk1...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Friday, August 14, 2009 11:37 PM
> Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which
> to use
>
>
> >
> > On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:
> >
> >> How will that help him fix this problem, if the problem is that
> >> SQLite's query optimizer is selecting a suboptimal index to use,
> >> and
> >> there is no way to specify which index to use?
> >
> > The statistics collected by the ANALYZE command will be used by
> > SQLite to (hopefully) select the optimal index.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[His Nerdship]

Hi Pavel,

> Does INDEXED BY clause work for you?
> http://www.sqlite.org/lang_select.html

The page suggests that INDEXED BY can only be used in single-table queries. 
The report queries also join other smaller tables, some of them from an
attached database, so I don't think this will work.
However, you have given me an idea - maybe I could break it down to two
queries. Use the INDEX BY on the big table to get a subset table, and do
another SELECT on the subset. Thanks.
Sholto (His Nerdship)



> Is there any way the code can 'suggest' SQLite use a certain index?


Pavel

-- 
View this message in context: 
http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24999340.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Pavel Ivanov]

> Is there any way the code can 'suggest' SQLite use a certain index?

Does INDEXED BY clause work for you?
http://www.sqlite.org/lang_select.html


Pavel

On Sat, Aug 15, 2009 at 2:08 AM, His Nerdship wrote:
>
> Good day,
> We have a puzzling problem with a large (1GB+) database.
> Most of our queries are based on 3 columns, say X, Y and Z.
> X is always the first in the index.  However, sometimes the query involves a
> small range of Y and a larger range of Z, and sometimes the reverse.  We
> first had an index based on X, Y & Z (in that order), and noticed that the
> results are fast when there was one X, one Y and many Z's.  I check if the
> range is a single value, and if it is, I change the SQL to a straight '=',
> e.g:
>  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;
>
> According to Mike Owens, using an equality or IN operator on Y allows Z to
> be indexed, speeding up the search.  If Y is a range and we use "BETWEEN y1
> AND y2" on it, then Z will not be indexed.  This is what we found - the
> second search was much slower.
>
> However because sometimes the numbers are reversed, such that there are many
> Y's and few Z's, we added another index based on X, Z and Y, in that order.
> In this case, though, it didn't make any difference.  It seems like SQLite
> does not select the correct index to use - it uses XYZ instead of XZY.
> I know Mr Hipp is reluctant to add the ability to specify which index to use
> - it is 'un-RDBMS like' in his words.
> Is there any way the code can 'suggest' SQLite use a certain index?  Or at
> least confirm which index is being used?
>
> Also, is there a sensible maximum no of values we can put in an IN clause?
> Many of the queries involve all records over a month, and hitherto we have
> used, say, ..WHERE Date BETWEEN 20090701 AND 20090731;
> In this case would it work better with ..WHERE Date IN (20090701, 20090702,
> 20090703, ..., 20090731)?
> Thanks in advance
> --
> View this message in context: 
> http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981846.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[John Machin]

On 15/08/2009 4:48 PM, Jim Showalter wrote:
> It doesn't collect those statistics automatically, as part of query 
> plan optimization?

You may like to consider looking at
"6.0 Choosing between multiple indices" in
http://www.sqlite.org/optoverview.html

HTH,

John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Jim Showalter]

It doesn't collect those statistics automatically, as part of query 
plan optimization?

- Original Message - 
From: "Dan Kennedy" <danielk1...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, August 14, 2009 11:37 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


>
> On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:
>
>> How will that help him fix this problem, if the problem is that
>> SQLite's query optimizer is selecting a suboptimal index to use, 
>> and
>> there is no way to specify which index to use?
>
> The statistics collected by the ANALYZE command will be used by
> SQLite to (hopefully) select the optimal index.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[His Nerdship]

Thanks for the feedback. I realise that Dan's suggestions won't necessarily
fix the problem, but it would be very handy to know if my theory about poor
choice of indexes is right. So, Dan, take your bow!

- Original Message - 
From: "Dan Kennedy" <danielk1...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, August 14, 2009 11:15 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


> How will that help him fix this problem, if the problem is that 
> SQLite's query optimizer is selecting a suboptimal index to use, and 
> there is no way to specify which index to use?


>
> On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:
>
>>
>> Good day,
>> We have a puzzling problem with a large (1GB+) database.
>> Most of our queries are based on 3 columns, say X, Y and Z.
>> X is always the first in the index.  However, sometimes the query
>> involves a
>> small range of Y and a larger range of Z, and sometimes the
>> reverse.
-- 
View this message in context: 
http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981973.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:

> How will that help him fix this problem, if the problem is that
> SQLite's query optimizer is selecting a suboptimal index to use, and
> there is no way to specify which index to use?

The statistics collected by the ANALYZE command will be used by
SQLite to (hopefully) select the optimal index.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Jim Showalter]

How will that help him fix this problem, if the problem is that 
SQLite's query optimizer is selecting a suboptimal index to use, and 
there is no way to specify which index to use?

- Original Message - 
From: "Dan Kennedy" <danielk1...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, August 14, 2009 11:15 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


>
> On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:
>
>>
>> Good day,
>> We have a puzzling problem with a large (1GB+) database.
>> Most of our queries are based on 3 columns, say X, Y and Z.
>> X is always the first in the index.  However, sometimes the query
>> involves a
>> small range of Y and a larger range of Z, and sometimes the
>> reverse.  We
>> first had an index based on X, Y & Z (in that order), and noticed
>> that the
>> results are fast when there was one X, one Y and many Z's.  I check
>> if the
>> range is a single value, and if it is, I change the SQL to a
>> straight '=',
>> e.g:
>>  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;
>>
>> According to Mike Owens, using an equality or IN operator on Y
>> allows Z to
>> be indexed, speeding up the search.  If Y is a range and we use
>> "BETWEEN y1
>> AND y2" on it, then Z will not be indexed.  This is what we found -
>> the
>> second search was much slower.
>>
>> However because sometimes the numbers are reversed, such that there
>> are many
>> Y's and few Z's, we added another index based on X, Z and Y, in 
>> that
>> order.
>> In this case, though, it didn't make any difference.  It seems like
>> SQLite
>> does not select the correct index to use - it uses XYZ instead of 
>> XZY.
>
> You might just need to run the ANALYZE command to collect database
> statistics.
>
>   http://www.sqlite.org/lang_analyze.html
>
> Use EXPLAIN QUERY PLAN to see the strategy SQLite is using for each
> query.
>
>   http://www.sqlite.org/lang_explain.html
>
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Dan Kennedy]

On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:

>
> Good day,
> We have a puzzling problem with a large (1GB+) database.
> Most of our queries are based on 3 columns, say X, Y and Z.
> X is always the first in the index.  However, sometimes the query  
> involves a
> small range of Y and a larger range of Z, and sometimes the  
> reverse.  We
> first had an index based on X, Y & Z (in that order), and noticed  
> that the
> results are fast when there was one X, one Y and many Z's.  I check  
> if the
> range is a single value, and if it is, I change the SQL to a  
> straight '=',
> e.g:
>  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;
>
> According to Mike Owens, using an equality or IN operator on Y  
> allows Z to
> be indexed, speeding up the search.  If Y is a range and we use  
> "BETWEEN y1
> AND y2" on it, then Z will not be indexed.  This is what we found -  
> the
> second search was much slower.
>
> However because sometimes the numbers are reversed, such that there  
> are many
> Y's and few Z's, we added another index based on X, Z and Y, in that  
> order.
> In this case, though, it didn't make any difference.  It seems like  
> SQLite
> does not select the correct index to use - it uses XYZ instead of XZY.

You might just need to run the ANALYZE command to collect database  
statistics.

   http://www.sqlite.org/lang_analyze.html

Use EXPLAIN QUERY PLAN to see the strategy SQLite is using for each  
query.

   http://www.sqlite.org/lang_explain.html


Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple indexes in SQLite, and selecting which to use

[His Nerdship]

Good day,
We have a puzzling problem with a large (1GB+) database.
Most of our queries are based on 3 columns, say X, Y and Z.
X is always the first in the index.  However, sometimes the query involves a
small range of Y and a larger range of Z, and sometimes the reverse.  We
first had an index based on X, Y & Z (in that order), and noticed that the
results are fast when there was one X, one Y and many Z's.  I check if the
range is a single value, and if it is, I change the SQL to a straight '=',
e.g:
  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;

According to Mike Owens, using an equality or IN operator on Y allows Z to
be indexed, speeding up the search.  If Y is a range and we use "BETWEEN y1
AND y2" on it, then Z will not be indexed.  This is what we found - the
second search was much slower.

However because sometimes the numbers are reversed, such that there are many
Y's and few Z's, we added another index based on X, Z and Y, in that order. 
In this case, though, it didn't make any difference.  It seems like SQLite
does not select the correct index to use - it uses XYZ instead of XZY.
I know Mr Hipp is reluctant to add the ability to specify which index to use
- it is 'un-RDBMS like' in his words.
Is there any way the code can 'suggest' SQLite use a certain index?  Or at
least confirm which index is being used?

Also, is there a sensible maximum no of values we can put in an IN clause? 
Many of the queries involve all records over a month, and hitherto we have
used, say, ..WHERE Date BETWEEN 20090701 AND 20090731;
In this case would it work better with ..WHERE Date IN (20090701, 20090702,
20090703, ..., 20090731)?
Thanks in advance
-- 
View this message in context: 
http://www.nabble.com/Multiple-indexes-in-SQLite%2C-and-selecting-which-to-use-tp24981846p24981846.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users