One more question that I was pondering upon - does the order of rows and their placement in the table matter at all when using indexes (provided indexes are used correctly)?
Oh, and another :) Would such a statement use both indexes or just one? CREATE INDEX indexA on tableA (columnA, columnB, columnC) SELECT EXISTS (SELECT ROWID from tableA WHERE columnA='a' AND columnB='b') (reading the presentation slides at the moment) > -----Original Message----- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 28, 2007 1:17 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Questions on views > > Dennis Volodomanov wrote: > > I might not go down the threading path at all, as in theory it > > wouldn't gain me that much - I'd still need to lock/unlock the > > database for each of the threads and I think the overhead > of that plus > > the fact that it will have to wait for the other thread to do its > > stuff wouldn't gain me much if anything. > > > > I found that my indexes are not used correctly, eventhough the SQL > > statements have no inequalities on the left and all fields > are indexed. > > I guess I'll have to use the EXPLAIN to dig in and see > what's wrong. > > > > > Dennis, > > I wouldn't give up on the multiple thread idea so fast if you > have multiple CPU's to execute the queries on since you can > have multiple read queries executing in parallel on different > CPUs. Each of these readers would write their output to a > queue for a single writer thread which would run after all > the readers have finished. The writer would execute all the > database updates sequentially in a single transaction. > > If the queries take half the current execution time and the > updates take the other half, then using 4 CPUs could cut the > query time by 4 and eliminate 3/8 of the execution time. > However, if you don't have multiple CPUs the overhead will > probably slow things down overall. > > Using the correct indexes (or indicies) is very important. It > impacts the query speed directly, and having unnecessary > unused indexes increases the time it takes to do the inserts > and deletes on the indexed tables. > > SQLite will only use a single index per table per query. The > way sqlite uses indexes is explained in the slide show at > http://www.sqlite.org/php2004/page-001.html This may help you > pick the best columns to index for your application. > > HTH > Dennis Cote > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
