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

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

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

>> 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

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

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

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

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

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

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

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

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

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

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

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

t; 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 > >

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

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.

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

> 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

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

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

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

09 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 u

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

gt; 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 opt

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

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

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 pu

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

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

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