Re: [sqlite] Fine tuning table indexes

2014-02-02 Thread Dan Kennedy
On 02/02/2014 11:16 PM, James K. Lowden wrote: On Sat, 1 Feb 2014 11:21:45 -0500 Stephen Chrzanowski wrote: Would be interesting to see when and where that single index comes into play when multiple indexes are defined. create table T (t int primary key, a int , b int);

Re: [sqlite] Fine tuning table indexes

2014-02-02 Thread Petite Abeille
On Feb 2, 2014, at 5:55 PM, Keith Medcalf wrote: > Nevertheless, each traversal operation is only using one index at a time. One word: bitmap. As in bitmap index: http://www.oracle.com/technetwork/articles/sharma-indexes-093638.html

Re: [sqlite] Fine tuning table indexes

2014-02-02 Thread Keith Medcalf
statistical optimizers. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of James K. Lowden >Sent: Sunday, 2 February, 2014 09:16 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Fine tuning table

Re: [sqlite] Fine tuning table indexes

2014-02-02 Thread James K. Lowden
On Sat, 1 Feb 2014 11:21:45 -0500 Stephen Chrzanowski wrote: > Would be interesting to see when and where that single index comes > into play when multiple indexes are defined. create table T (t int primary key, a int , b int); create index Ta on T(a); create index Tb on

Re: [sqlite] Fine tuning table indexes

2014-02-01 Thread Simon Slavin
On 2 Feb 2014, at 1:09am, Keith Medcalf wrote: > No access method (yet invented) in any product (yet invented) can use more > than a single traversal path through a collection of data at any given time > during a single traversal operation. Yeah, that's why I asked for a

Re: [sqlite] Fine tuning table indexes

2014-02-01 Thread Scott Robison
As I recall, I recorded the source file and line number location of the query, the query and its plan. No execution time, because the time each query ran would have been heavily influenced by the amount of time the caller used in processing each row. It could have been done, but the query plan was

Re: [sqlite] Fine tuning table indexes

2014-02-01 Thread Keith Medcalf
>> Would be interesting to see >> when and where that single index comes into play when multiple indexes >> are >> defined. >MS SQL allows for many sorts of indexes, and SQLite has only one. MS SQL >has a Tuning Wizard, which will recommend indexes to create or drop, but >SQLite has a better

Re: [sqlite] Fine tuning table indexes

2014-02-01 Thread Simon Slavin
On 1 Feb 2014, at 4:21pm, Stephen Chrzanowski wrote: > What was the raw results of some of the queries? Obviously query and > explain, but did you also tag in time deltas pre and post queries? > > I'll have to read up more on EXPLAIN. I'm aware of how indexes work as >

Re: [sqlite] Fine tuning table indexes

2014-02-01 Thread Stephen Chrzanowski
What was the raw results of some of the queries? Obviously query and explain, but did you also tag in time deltas pre and post queries? I'll have to read up more on EXPLAIN. I'm aware of how indexes work as well as where and when you need to put them in, but, when I had my SQL training back on

Re: [sqlite] Fine tuning table indexes

2014-01-31 Thread Scott Robison
On Fri, Jan 31, 2014 at 8:49 PM, Stephen Chrzanowski wrote: > I've not done anything to 'generate' a SQL statement, but I can see the use > of this just as a debugging tool to figure out why something is taking so > long. I might just implement that in the wrapper I use

Re: [sqlite] Fine tuning table indexes

2014-01-31 Thread Stephen Chrzanowski
On Fri, Jan 31, 2014 at 12:05 PM, Simon Slavin wrote: > > However, you also mention that your app generates its own commands. > Unless you can predict things about these commands ("90% of the time users > are going to want to sort by date") you're not in a good place to

Re: [sqlite] Fine tuning table indexes

2014-01-31 Thread Simon Slavin
On 31 Jan 2014, at 4:41pm, Andreas Hofmann wrote: > I would like to fine tune table indexes. I want to make sure I got indexes > for the columns or combined columns of all (most) where clauses. The issue > is that the application builds dynamic SQL strings all over

[sqlite] Fine tuning table indexes

2014-01-31 Thread Andreas Hofmann
Hi, I would like to fine tune table indexes. I want to make sure I got indexes for the columns or combined columns of all (most) where clauses. The issue is that the application builds dynamic SQL strings all over the place and it may not be easy to find them all without reading the code very