--- michael cuthbertson <[EMAIL PROTECTED]> wrote: > Hello. > I sent this 2 days a go but have not received an explanation or solution.
As I explained to you a couple of days ago in this post http://www.mail-archive.com/sqlite-users%40sqlite.org/msg20802.html that's the nature of indexes - they can help you in the general case for the "average" query, but you can always find cases where they are slightly slower than using a simple table scan. You have identified such a case. Surely you will perform other queries that are greatly helped by the same time1 index. It's not reasonable to expect the power of an Oracle query optimizer in a such a small embeddable database library. I suggest you actually try and read the SQLite documentation: http://sqlite.org/optoverview.html Look into using a single composite index rather than a bunch of individual column indexes. > Anybody had this problem? > > > Hello and Happy New year! > I have an indexing problem with a very simple query. > The correct index is not used or no index is used, according to Explain > Query Plan. > In the following query, all columns but num have indexes, using default. > num is integer; filetype is Varchar; time1 is Datetime; name is varchar. > > > This query: > select * from tbl where num = 2563351070 and > filetype = 'cpp' and > time1 > '2003-01-01' and time1 < '2006-05-05' > returns 18k rows in 600ms and index for time1 is used. > > If I 'plus' the time1 columns, the exec time drops to 420ms and > no index is used. > If I use 'name' instead of 'filetype' with time1 "plussed", no index is > used. > Also, if I use 'order by name', no index is used. > > To summarize, in this query with indexes on most columns, SQLite will only > use time1 index and is slower doing so. > Could someone please explain to me why this happens? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
