Re: [sqlite] Index and ORDER BY
Richard, Just a suggestion. Would it make sense to ask one of your document maintainers to add something similar to your explanation and add it to the "SELECT" documentation page as a note to using the WHERE clause and the "INDEX" documentation page = Note the "+" operator in front of the "type" field in the WHERE clause. This + size makes that term of the WHERE clause an expression, rather than a constraint on a column, and this disqualifies it from use by an index. That forces SQLite to use another query strategy. = Regards, Noah CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Jul 1, 2008, at 3:53 PM, Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 > [EMAIL PROTECTED] написал(а): >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" >>> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" >>> keyword for index? >> >> The DESC keyword creates the index in descending collation order, >> rather >> than ascending order (default). I believe this sort order may not be >> observed in older versions, but more recent ones do so. > > I'm using SQLite 3.5.9 and there are no differents in my tests > between DESC > and default indeces. I try create index with keywork DESC for > optimize DESC > sorting but it don't work for me. My tests you can see above. > Production tests for the descending index feature are found here: http://www.sqlite.orc/cvstrac/fileview?f=sqlite/test/descidx1.test http://www.sqlite.orc/cvstrac/fileview?f=sqlite/test/descidx2.test http://www.sqlite.orc/cvstrac/fileview?f=sqlite/test/descidx3.test Perhaps you can look at those tests and figure out what the difference is between them and your tests. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
В сообщении от Wednesday 02 July 2008 08:25:10 Dan написал(а): > > I'm using SQLite 3.5.9 and there are no differents in my tests > > between DESC > > and default indeces. I try create index with keywork DESC for > > optimize DESC > > sorting but it don't work for me. My tests you can see above. > > Have you seen the notes on file-format and "pragma legacy_file_format" > in the documentation for CREATE INDEX? > > http://www.sqlite.org/lang_createindex.html I try test new file format with "PRAGMA legacy_file_format = off": #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {PRAGMA legacy_file_format = off} db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} db close $ ls -lh итого 4,0G -rw-r--r-- 1 veter veter 4,0G Июл 2 12:44 index_order.db Database size is more than with default "PRAGMA legacy_file_format = on". sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 0.572035 sys 0.232014 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY === Result: With new file format index with keyword "desc" not work again. === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
> > I'm using SQLite 3.5.9 and there are no differents in my tests > > between DESC > > and default indeces. I try create index with keywork DESC for > > optimize DESC > > sorting but it don't work for me. My tests you can see above. > > Have you seen the notes on file-format and "pragma legacy_file_format" > in the documentation for CREATE INDEX? > >http://www.sqlite.org/lang_createindex.html > Is it safe to use this pragma? I have seen the notes but I'm not sure ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Jul 2, 2008, at 2:53 AM, Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 > [EMAIL PROTECTED] написал(а): >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" >>> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" >>> keyword for index? >> >> The DESC keyword creates the index in descending collation order, >> rather >> than ascending order (default). I believe this sort order may not be >> observed in older versions, but more recent ones do so. > > I'm using SQLite 3.5.9 and there are no differents in my tests > between DESC > and default indeces. I try create index with keywork DESC for > optimize DESC > sorting but it don't work for me. My tests you can see above. Have you seen the notes on file-format and "pragma legacy_file_format" in the documentation for CREATE INDEX? http://www.sqlite.org/lang_createindex.html Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Yes, this has been my experience as well. I've tried 3.5.6 and 3.5.9. Jeff Alexey Pechnikov wrote: > В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > >> On Tue, 1 Jul 2008, Alexey Pechnikov wrote: >> >>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" >>> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" >>> keyword for index? >>> >> The DESC keyword creates the index in descending collation order, rather >> than ascending order (default). I believe this sort order may not be >> observed in older versions, but more recent ones do so. >> > > I'm using SQLite 3.5.9 and there are no differents in my tests between DESC > and default indeces. I try create index with keywork DESC for optimize DESC > sorting but it don't work for me. My tests you can see above. > ___ > 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] Index and ORDER BY
В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а): > On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" > > keyword for index? > > The DESC keyword creates the index in descending collation order, rather > than ascending order (default). I believe this sort order may not be > observed in older versions, but more recent ones do so. I'm using SQLite 3.5.9 and there are no differents in my tests between DESC and default indeces. I try create index with keywork DESC for optimize DESC sorting but it don't work for me. My tests you can see above. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Tue, 1 Jul 2008, Alexey Pechnikov wrote: > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword > for index? The DESC keyword creates the index in descending collation order, rather than ascending order (default). I believe this sort order may not be observed in older versions, but more recent ones do so. Chris Martin UNC-CH ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword for index? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I see. It turns out that the selectivity of "type" is highly variable - some types are very common and some are quite rare. What made me curious is that when I have an index on type and I look for the first few entries in ascending order, the query is very fast - it seems that it does the indexed search on type and then starts searching the matching rows in ascending order. For the descending order, it seems that it has to find all matching rows and then return the last one (I don't know for sure how it's working, but that seems to fit the performance measurements). Is there any way to have it use the index on type and then search the matching rows in descending order so no sorting is required? My confusion is that it seems to search in descending order when only the primary key is involved, but not when using an index, even if that index has DESC specified. As you say, I have put in the "+type" trick, and that speeds up my common case (where a matching type is nearby so the linear search isn't so bad), so now I'm in the much better situation of just worrying about the hypothetical case where it has to search a long way to find a matching type. Thanks, Jeff D. Richard Hipp wrote: > On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote: > > >> I'm including a copy of Alexey's relevant message below. Unless I >> misunderstand, he has a test case that demonstrates that for the >> table: >> >> CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) >> >> the query: >> >> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY >> eid DESC LIMIT 1; >> >> runs much faster if there is no index on type. The culprit seems to >> be >> the <= in conjunction with the descending ordering. If you change >> either, the query gets much faster. He tried the using indices >> events(type,eid) and events(type,eid desc). I also tried your >> original >> suggestion of just events(type) and got the same result. >> > > That would be a case of SQLite choosing a suboptimal index, which is > very different from ignoring an index all together, which is what your > original statement said. I see that if there is an index on > events(type) that index is used rather than the primary key. This is > because the query optimizer is assuming that type=22 is highly > selective. Running ANALYZE might help. But a sure-fire solution is > to change the query as follows: > > SELECT * FROM events > WHERE eid<=32619750 >AND +type=22 > ORDER BY eid DESC > LIMIT 1; > > Note the "+" operator in front of the "type" field in the WHERE > clause. This + size makes that term of the WHERE clause an > expression, rather than a constraint on a column, and this > disqualifies it from use by an index. That forces SQLite to use the > other query strategy, which is to use the integer primary key. > > Note that in this case, the correct index choice depends on the kind > of data contained in the table. If there is only a single row out of > 20 million for which type=22, but there are hundreds of thousands of > rows with eid<=32619750, then the use of the index on event(type) is > clearly the better strategy. Only when type=22 is a common occurrence > does it become better to use the integer primary key. SQLite does not > attempt to keep statistics on table contents, so it has no way of > knowing which approach is really better. It makes its best guess. In > this case, it happened to guess wrong. But, as I pointed out, a > programmer with higher-level knowledge of the table content can steer > SQLite toward the better choice with the judicious use of a "+" symbol. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Index and ORDER BY
On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote: > I'm including a copy of Alexey's relevant message below. Unless I > misunderstand, he has a test case that demonstrates that for the > table: > > CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) > > the query: > > SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > > runs much faster if there is no index on type. The culprit seems to > be > the <= in conjunction with the descending ordering. If you change > either, the query gets much faster. He tried the using indices > events(type,eid) and events(type,eid desc). I also tried your > original > suggestion of just events(type) and got the same result. That would be a case of SQLite choosing a suboptimal index, which is very different from ignoring an index all together, which is what your original statement said. I see that if there is an index on events(type) that index is used rather than the primary key. This is because the query optimizer is assuming that type=22 is highly selective. Running ANALYZE might help. But a sure-fire solution is to change the query as follows: SELECT * FROM events WHERE eid<=32619750 AND +type=22 ORDER BY eid DESC LIMIT 1; Note the "+" operator in front of the "type" field in the WHERE clause. This + size makes that term of the WHERE clause an expression, rather than a constraint on a column, and this disqualifies it from use by an index. That forces SQLite to use the other query strategy, which is to use the integer primary key. Note that in this case, the correct index choice depends on the kind of data contained in the table. If there is only a single row out of 20 million for which type=22, but there are hundreds of thousands of rows with eid<=32619750, then the use of the index on event(type) is clearly the better strategy. Only when type=22 is a common occurrence does it become better to use the integer primary key. SQLite does not attempt to keep statistics on table contents, so it has no way of knowing which approach is really better. It makes its best guess. In this case, it happened to guess wrong. But, as I pointed out, a programmer with higher-level knowledge of the table content can steer SQLite toward the better choice with the judicious use of a "+" symbol. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I'm including a copy of Alexey's relevant message below. Unless I misunderstand, he has a test case that demonstrates that for the table: CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER) the query: SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; runs much faster if there is no index on type. The culprit seems to be the <= in conjunction with the descending ordering. If you change either, the query gets much faster. He tried the using indices events(type,eid) and events(type,eid desc). I also tried your original suggestion of just events(type) and got the same result. Thanks, Jeff D. Richard Hipp wrote: > On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote: > >> Is it a problem in sqlite that it will only optimize: "WHERE >> primary_key<=X ORDER BY primary_key DESC" if it's not using an index? >> Is it supposed to? >> > > It would be a problem if it where the case. But in every test I have > tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk > DESC. If you can demonstrate a case where it does not, we will fix it. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Alexey Pechnikov wrote: > Really, there is problem with multi-column indexes. You must use only primary > key index for ">=" where clause and "ASC" sorting and "<=" where clause and > DESC sorting. > > > 1. I try with primary key: > > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db close > > So, "type" is equal ("eid" mod 50). > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.00 sys 0.00 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Result: this index is good. > > > 2. And I try with two-columns common order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_idx ON events(type,eid)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 1.400088 sys 1.696106 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_idx ORDER BY > > > Result: this index is bad. > > > 3. And I try with two-columns desc order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.600037 sys 0.608038 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY > > > And with modified query: > > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 9972|22 > CPU Time: user 0.00 sys 0.00 > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 22|22 > CPU Time: user 0.00 sys 0.004000 > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 32619772|22 > CPU Time: user 0.284018 sys 0.820051 > > > > > Result: this index is bad. > > > > P.S. Try with primary key index only and write your results. > ___ > 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] Index and ORDER BY
On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote: > > Is it a problem in sqlite that it will only optimize: "WHERE > primary_key<=X ORDER BY primary_key DESC" if it's not using an index? > Is it supposed to? It would be a problem if it where the case. But in every test I have tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk DESC. If you can demonstrate a case where it does not, we will fix it. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Terrific! sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND +type=22 ORDER BY eid DESC LIMIT 1; 3261891|4910298|1206924|1|22|9|4 CPU Time: user 0.002000 sys 0.017997 Now I have a workaround - thanks to everyone for the help. I assume that what sqlite is doing under the hood here is doing a linear search of rows in descending order (of the primary key eid) starting at 3261976 to find one where type=22. In the common case, it will find one nearby, and the query will be fast. If it has to search a long way, though, I'd expect it to be rather slow. Of course, I'm much better off than before being that at least common cases are fast, but I'm curious about the general case, where you really want to do an indexed lookup on type. Is it a problem in sqlite that it will only optimize: "WHERE primary_key<=X ORDER BY primary_key DESC" if it's not using an index? Is it supposed to? Thanks, Jeff On Jul 1, 2008, at 9:56 AM, Noah Hart wrote: > Jeff, try this select instead > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and +type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Regards-- Noah > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] > Sent: Tuesday, July 01, 2008 9:44 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Index and ORDER BY > > I agree. If I drop indices that use "type", I get my performance back > for this query: > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22 > ORDER BY eid DESC LIMIT 1; > 16643833|27906245|5972704|0|22|9|4 > CPU Time: user 0.001000 sys 0.001000 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > The problem is that indexing the type column gives me a huge > performance benefit for other queries in my application. Is there any > way I can force sqlite to not use an index for a particular query? > Thanks for your help! > Jeff > > > > CONFIDENTIALITY NOTICE: > This message may contain confidential and/or privileged information. > If you are not the addressee or authorized to receive this for the > addressee, you must not use, copy, disclose, or take any action > based on this message or any information herein. If you have > received this message in error, please advise the sender immediately > by reply e-mail and delete this message. Thank you for your > cooperation. > > > ___ > 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] Index and ORDER BY
Jeff, try this select instead sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and +type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY Regards-- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 9:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Index and ORDER BY I agree. If I drop indices that use "type", I get my performance back for this query: sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 16643833|27906245|5972704|0|22|9|4 CPU Time: user 0.001000 sys 0.001000 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY The problem is that indexing the type column gives me a huge performance benefit for other queries in my application. Is there any way I can force sqlite to not use an index for a particular query? Thanks for your help! Jeff CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I agree. If I drop indices that use "type", I get my performance back for this query: sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 16643833|27906245|5972704|0|22|9|4 CPU Time: user 0.001000 sys 0.001000 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY The problem is that indexing the type column gives me a huge performance benefit for other queries in my application. Is there any way I can force sqlite to not use an index for a particular query? Thanks for your help! Jeff On Jul 1, 2008, at 2:14 AM, Alexey Pechnikov wrote: > Really, there is problem with multi-column indexes. You must use > only primary > key index for ">=" where clause and "ASC" sorting and "<=" where > clause and > DESC sorting. > > > 1. I try with primary key: > > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { >for {set i 0} {$i<1} {incr i} { >set type [expr {$i % 50}] >db eval {insert into events values ($i,$type)} >} > } > db close > > So, "type" is equal ("eid" mod 50). > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.00 sys 0.00 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Result: this index is good. > > > 2. And I try with two-columns common order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { >for {set i 0} {$i<1} {incr i} { >set type [expr {$i % 50}] >db eval {insert into events values ($i,$type)} >} > } > db eval {CREATE INDEX ev_idx ON events(type,eid)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 1.400088 sys 1.696106 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_idx ORDER BY > > > Result: this index is bad. > > > 3. And I try with two-columns desc order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { >for {set i 0} {$i<1} {incr i} { >set type [expr {$i % 50}] >db eval {insert into events values ($i,$type)} >} > } > db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.600037 sys 0.608038 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE > eid<=32619760 and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY > > > And with modified query: > > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 > ORDER BY > eid DESC LIMIT 1; > 9972|22 > CPU Time: user 0.00 sys 0.00 > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 > ORDER BY > eid ASC LIMIT 1; > 22|22 > CPU Time: user 0.00 sys 0.004000 > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 > ORDER BY > eid ASC LIMIT 1; > 32619772|22 > CPU Time: user 0.284018 sys 0.820051 > > > > > Result: this index is bad. > > > > P.S. Try with primary key index only and write your results. > ___ > 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] Index and ORDER BY
В сообщении от Tuesday 01 July 2008 19:26:47 John Stanton написал(а): > I haven't looked closely at this problem but a cursory glance suggests > that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause. But primary key index work fine. Why? > Try doing the selection ASC and then sorting the output DESC as a > seperate action. It's impossible for big tables. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I haven't looked closely at this problem but a cursory glance suggests that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause. Try doing the selection ASC and then sorting the output DESC as a seperate action. Alexey Pechnikov wrote: > Really, there is problem with multi-column indexes. You must use only primary > key index for ">=" where clause and "ASC" sorting and "<=" where clause and > DESC sorting. > > > 1. I try with primary key: > > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db close > > So, "type" is equal ("eid" mod 50). > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.00 sys 0.00 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > > Result: this index is good. > > > 2. And I try with two-columns common order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_idx ON events(type,eid)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 1.400088 sys 1.696106 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_idx ORDER BY > > > Result: this index is bad. > > > 3. And I try with two-columns desc order index: > #!/usr/bin/tclsh > package require sqlite3 > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} > db transaction { > for {set i 0} {$i<1} {incr i} { > set type [expr {$i % 50}] > db eval {insert into events values ($i,$type)} > } > } > db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} > db close > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 32619722|22 > CPU Time: user 0.600037 sys 0.608038 > > sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 > and > type=22 ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY > > > And with modified query: > > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid DESC LIMIT 1; > 9972|22 > CPU Time: user 0.00 sys 0.00 > sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 22|22 > CPU Time: user 0.00 sys 0.004000 > sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY > eid ASC LIMIT 1; > 32619772|22 > CPU Time: user 0.284018 sys 0.820051 > > > > > Result: this index is bad. > > > > P.S. Try with primary key index only and write your results. > ___ > 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] Index and ORDER BY
Really, there is problem with multi-column indexes. You must use only primary key index for ">=" where clause and "ASC" sorting and "<=" where clause and DESC sorting. 1. I try with primary key: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db close So, "type" is equal ("eid" mod 50). sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 0.00 sys 0.00 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY Result: this index is good. 2. And I try with two-columns common order index: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db eval {CREATE INDEX ev_idx ON events(type,eid)} db close sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 1.400088 sys 1.696106 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev_idx ORDER BY Result: this index is bad. 3. And I try with two-columns desc order index: #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)} db transaction { for {set i 0} {$i<1} {incr i} { set type [expr {$i % 50}] db eval {insert into events values ($i,$type)} } } db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)} db close sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 32619722|22 CPU Time: user 0.600037 sys 0.608038 sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY And with modified query: sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY eid DESC LIMIT 1; 9972|22 CPU Time: user 0.00 sys 0.00 sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY eid ASC LIMIT 1; 22|22 CPU Time: user 0.00 sys 0.004000 sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY eid ASC LIMIT 1; 32619772|22 CPU Time: user 0.284018 sys 0.820051 Result: this index is bad. P.S. Try with primary key index only and write your results. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
When I try a similar query (i.e, no type comparison), I get the same results as you: sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 ORDER BY eid DESC LIMIT 1; 3261976|21 CPU Time: user 0.00 sys 0.027996 sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE eid<=3261976 ORDER BY eid DESC LIMIT 1; 0|0|TABLE EVENTS USING PRIMARY KEY ORDER BY As soon as I add type criterion to the where clause, though, the performance falls way off (the index is on events(type)): sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 3261891|22 CPU Time: user 3.069533 sys 0.485927 sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE EVENTS WITH INDEX ev4_idx ORDER BY The fact that it seems to be able to do a descending sort very quickly if it's only using the primary key, let me to try the following, which turned out to be more convoluted and was much slower. sqlite> SELECT e.eid,e.type FROM events e, (SELECT eid FROM events WHERE eid<=3261976 ORDER BY eid DESC) l WHERE e.eid=l.eid AND type=22 LIMIT 1; CPU Time: user 29.111574 sys 3.276502 sqlite> EXPLAIN QUERY PLAN SELECT e.eid,e.type FROM events e, (SELECT eid FROM events WHERE eid<=3261976 ORDER BY eid DESC) l WHERE e.eid=l.eid AND type=22 LIMIT 1; 0|0|TABLE events AS e WITH INDEX ev4_idx 1|1|TABLE events USING PRIMARY KEY It seems to me that sqlite can very efficiently do a descending sort on a primary key by itself, but not when it's used with an index. Does that sound correct? Also, I'm using sqlite 3.5.6, not 3.5.9. Does that make a difference? Thanks, Jeff Alexey Pechnikov wrote: > I try with this script on my laptop with 1 Gb RAM > > #!/usr/bin/tclsh > package require sqlite3 > > sqlite3 db index_order.db > db eval {DROP TABLE IF EXISTS events} > db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY)} > db eval {CREATE INDEX ev_desc_idx ON events(eid desc)} > db transaction { > for {set i 0} {$i<1} {incr i} { > db eval {insert into events values ($i)} > } > } > db close > > SQLite version 3.5.9 is used. > > I'm increasing ~ x10 rows count (and search for 32619760 row against 3261976 > in your query) and my database size is similar to your database: > $ ls -lh .|grep db > -rw-r--r-- 1 veter veter 2,4G Июн 29 12:08 index_order.db > > There are my results: > > sqlite> SELECT events.* FROM events WHERE eid<=32619760 ORDER BY eid DESC > LIMIT 1; > 32619760 > CPU Time: user 0.00 sys 0.00 > > sqlite> explain query plan SELECT events.* FROM events WHERE eid<=32619760 > ORDER BY eid DESC LIMIT 1; > 0|0|TABLE events USING PRIMARY KEY ORDER BY > > Index ev_desc_idx is not used. > > Check your SQLite version and try again with my script. > ___ > 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] Index and ORDER BY
I try with this script on my laptop with 1 Gb RAM #!/usr/bin/tclsh package require sqlite3 sqlite3 db index_order.db db eval {DROP TABLE IF EXISTS events} db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY)} db eval {CREATE INDEX ev_desc_idx ON events(eid desc)} db transaction { for {set i 0} {$i<1} {incr i} { db eval {insert into events values ($i)} } } db close SQLite version 3.5.9 is used. I'm increasing ~ x10 rows count (and search for 32619760 row against 3261976 in your query) and my database size is similar to your database: $ ls -lh .|grep db -rw-r--r-- 1 veter veter 2,4G Июн 29 12:08 index_order.db There are my results: sqlite> SELECT events.* FROM events WHERE eid<=32619760 ORDER BY eid DESC LIMIT 1; 32619760 CPU Time: user 0.00 sys 0.00 sqlite> explain query plan SELECT events.* FROM events WHERE eid<=32619760 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events USING PRIMARY KEY ORDER BY Index ev_desc_idx is not used. Check your SQLite version and try again with my script. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
sqlite> SELECT max(eid) from events; 16643833 sqlite> SELECT count(eid) FROM events; 16643833 sqlite> SELECT count(eid) FROM events WHERE type=22; 8206183 sqlite> SELECT count(eid) FROM events WHERE eid<=3261976; 3261976 sqlite> SELECT count(eid) FROM events WHERE eid<=3261976 AND type=22; 2062728 And for performance: sqlite> CREATE INDEX ev4_idx ON events(type); No ordering: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 LIMIT 1; 13|63922|6|0|22|9|4 CPU Time: user 0.00 sys 0.044993 Ascending order: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid ASC LIMIT 1; 13|63922|6|0|22|9|4 CPU Time: user 0.00 sys 0.00 Descending order: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 3261891|4910298|1206924|1|22|9|4 CPU Time: user 4.204361 sys 0.885865 (wall clock time is roughly double user+sys - I guess time waiting for disk isn't being counted in system time) Sanity check: sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev4_idx ORDER BY With a different index: sqlite> CREATE INDEX ev5_idx ON events(type,eid desc); sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 0|0|TABLE events WITH INDEX ev5_idx ORDER BY sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1; 3261891|4910298|1206924|1|22|9|4 CPU Time: user 4.282349 sys 0.901862 (again, wall-clock time is roughly double this amount) And ascending order is very fast: sqlite> SELECT events.* FROM events WHERE eid<=3261976 AND type=22 ORDER BY eid ASC LIMIT 1; 13|63922|6|0|22|9|4 CPU Time: user 0.00 sys 0.052992 It seems that sqlite wants to do its index scan in ascending order, so returning the first one is very quick, but returning the last one (first in descending order) is slow. Is there any way to give the engine an idea that it should do its index scan in descending order so that the ORDER BY is cheap? Thanks, Jeff On Jun 28, 2008, at 11:50 AM, Alexey Pechnikov wrote: > Show results of this queries: > > select max(eid) from events; > select count(eid) from events; > select count(eid) from events where type=22; > select count(eid) from events where eid<=3261976; > select count(eid) from events where eid<=3261976 and type=22; > ___ > 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] Index and ORDER BY
Show results of this queries: select max(eid) from events; select count(eid) from events; select count(eid) from events where type=22; select count(eid) from events where eid<=3261976; select count(eid) from events where eid<=3261976 and type=22; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Petite Abeille wrote: > On Jun 28, 2008, at 8:10 PM, [EMAIL PROTECTED] wrote: > >> I see. Do you have any suggestions on where I can find out about how >> to get more specific timing information? I'm pretty much an sqlite >> novice. > > In the sqlite3 command line: > > .timer ON|OFF Turn the CPU timer measurement on or off or in C you can use time or gettimeofday functions. see the man pages for them. -Steve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
On Jun 28, 2008, at 8:10 PM, [EMAIL PROTECTED] wrote: > I see. Do you have any suggestions on where I can find out about how > to get more specific timing information? I'm pretty much an sqlite > novice. In the sqlite3 command line: .timer ON|OFF Turn the CPU timer measurement on or off -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Show "explain query plan ..." for you query. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Unfortunately, I can't, being that the data is proprietary. It's also rather large (~ 2GB). Jeff On Jun 28, 2008, at 10:59 AM, Alexey Pechnikov wrote: > Can you send link to you database file? You results are strange. > > В сообщении от Saturday 28 June 2008 21:44:15 > [EMAIL PROTECTED] написал(а): >> Very strange. I modified my query to not use verbose or tid, so only >> the indexed columns are relevant. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type); >> >> The query runs in about 9 seconds. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type,eid desc) >> >> It runs in 11 seconds. >> >> I'm not using the most accurate timing in the world (not using >> database functions for the timing, since I don't know if that would >> distort the results) - literally, a wall clock. But it is noticeably >> a little slower. Any ideas? >> Thanks, >> Jeff >> >> On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: >>> В сообщении от Saturday 28 June 2008 02:28:05 Jeff >>> Gibson >>> >>> написал(а): When I do the following query: SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid DESC LIMIT 1; it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", it's very fast. >>> >>> As described in http://www.sqlite.org/lang_createindex.html >>> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >>> name .] >>> index-name >>> ON table-name ( column-name [, column-name]* ) >>> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >>> >>> You can try create additional index as >>> CREATE INDEX ev4_idx ON events (type,eid desc); >>> >>> ___ >>> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
I see. Do you have any suggestions on where I can find out about how to get more specific timing information? I'm pretty much an sqlite novice. Thanks, Jeff On Jun 28, 2008, at 12:02 PM, Stephen Woodbridge wrote: > [EMAIL PROTECTED] wrote: >> Very strange. I modified my query to not use verbose or tid, so only >> the indexed columns are relevant. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type); >> >> The query runs in about 9 seconds. >> >> With: >> >> CREATE INDEX ev4_idx ON events(type,eid desc) >> >> It runs in 11 seconds. >> >> I'm not using the most accurate timing in the world (not using >> database functions for the timing, since I don't know if that would >> distort the results) - literally, a wall clock. But it is noticeably >> a little slower. Any ideas? > > Well a few thoughts: > > 1) and index on (type) vs (type,eid,desc) will be smaller and get more > entries per page so you wil like have to bring less pages into memory. > > 2) I'm not sure of the statistics in your various indexes, but you > would > have to trade off whether or not the added resolution of > (type,eid,desc) > adds more value, then finding N records at (type) and doing a > seqential > scan of those vs find less and N records at (type,eid,desc) and > returning them. > > I think you need to look at each with more specific timing stats and > be > aware of what the cache status of pages are when you run the tests > because this might affect the timing results. > > -Steve > >> On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: >> >>> В сообщении от Saturday 28 June 2008 02:28:05 Jeff >>> Gibson >>> написал(а): When I do the following query: SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid DESC LIMIT 1; it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", it's very fast. >>> As described in http://www.sqlite.org/lang_createindex.html >>> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >>> name .] >>> index-name >>> ON table-name ( column-name [, column-name]* ) >>> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >>> >>> You can try create additional index as >>> CREATE INDEX ev4_idx ON events (type,eid desc); >>> >>> ___ >>> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
[EMAIL PROTECTED] wrote: > Very strange. I modified my query to not use verbose or tid, so only > the indexed columns are relevant. > > With: > > CREATE INDEX ev4_idx ON events(type); > > The query runs in about 9 seconds. > > With: > > CREATE INDEX ev4_idx ON events(type,eid desc) > > It runs in 11 seconds. > > I'm not using the most accurate timing in the world (not using > database functions for the timing, since I don't know if that would > distort the results) - literally, a wall clock. But it is noticeably > a little slower. Any ideas? Well a few thoughts: 1) and index on (type) vs (type,eid,desc) will be smaller and get more entries per page so you wil like have to bring less pages into memory. 2) I'm not sure of the statistics in your various indexes, but you would have to trade off whether or not the added resolution of (type,eid,desc) adds more value, then finding N records at (type) and doing a seqential scan of those vs find less and N records at (type,eid,desc) and returning them. I think you need to look at each with more specific timing stats and be aware of what the cache status of pages are when you run the tests because this might affect the timing results. -Steve > On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > >> В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson >> написал(а): >>> When I do the following query: >>> >>> SELECT events.* FROM events WHERE ( events.type=22) AND >>> ( events.tid=9) >>> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >>> DESC LIMIT 1; >>> >>> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >>> it's very fast. >> As described in http://www.sqlite.org/lang_createindex.html >> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >> name .] >> index-name >> ON table-name ( column-name [, column-name]* ) >> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >> >> You can try create additional index as >> CREATE INDEX ev4_idx ON events (type,eid desc); >> >> ___ >> 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] Index and ORDER BY
Can you send link to you database file? You results are strange. В сообщении от Saturday 28 June 2008 21:44:15 [EMAIL PROTECTED] написал(а): > Very strange. I modified my query to not use verbose or tid, so only > the indexed columns are relevant. > > With: > > CREATE INDEX ev4_idx ON events(type); > > The query runs in about 9 seconds. > > With: > > CREATE INDEX ev4_idx ON events(type,eid desc) > > It runs in 11 seconds. > > I'm not using the most accurate timing in the world (not using > database functions for the timing, since I don't know if that would > distort the results) - literally, a wall clock. But it is noticeably > a little slower. Any ideas? > Thanks, > Jeff > > On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > > > > написал(а): > >> When I do the following query: > >> > >> SELECT events.* FROM events WHERE ( events.type=22) AND > >> ( events.tid=9) > >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid > >> DESC LIMIT 1; > >> > >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", > >> it's very fast. > > > > As described in http://www.sqlite.org/lang_createindex.html > > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- > > name .] > > index-name > > ON table-name ( column-name [, column-name]* ) > > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > > > You can try create additional index as > > CREATE INDEX ev4_idx ON events (type,eid desc); > > > > ___ > > 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] Index and ORDER BY
Very strange. I modified my query to not use verbose or tid, so only the indexed columns are relevant. With: CREATE INDEX ev4_idx ON events(type); The query runs in about 9 seconds. With: CREATE INDEX ev4_idx ON events(type,eid desc) It runs in 11 seconds. I'm not using the most accurate timing in the world (not using database functions for the timing, since I don't know if that would distort the results) - literally, a wall clock. But it is noticeably a little slower. Any ideas? Thanks, Jeff On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND >> ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- > name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > 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] Index and ORDER BY
I tried taking the tid and verbose tests out of the WHERE clause, and it made very little difference in the performance. I was thinking that if I can at least speed it up with just eid and type, I could try to extend it to the other columns. Thanks, Jeff On Jun 28, 2008, at 6:25 AM, Emilio Platzer wrote: > (sorry about my poor english) > > I think that the problem doesn't correct by creating a DESC index. The > problema was at de 'where clausula': > > (events.eid<=3261976) > > For some reason SQLITE doesn't optimize the query to use the index to > locate the last index item that have type=22 and eid<=3261976. Of > course > if you have only a few items that have tid=9, SQL must have to read > every item starting with de last until he find the item that haves > tid=9. > > Do you try to the prevoius sugest: add a index that have type and tid? > > You must know that, SQLite must read the items to find verbose<=1 > > good luck! > > Emilio > > Alexey Pechnikov escribio': >> В сообщении от Saturday 28 June 2008 02:28:05 Jeff >> Gibson написал(а): >>> When I do the following query: >>> >>> SELECT events.* FROM events WHERE ( events.type=22) AND >>> ( events.tid=9) >>> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY >>> events.eid >>> DESC LIMIT 1; >>> >>> it's very slow. If I switch the ORDER BY to "ASC" instead of >>> "DESC", >>> it's very fast. >> >> As described in http://www.sqlite.org/lang_createindex.html >> "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- >> name .] >> index-name >> ON table-name ( column-name [, column-name]* ) >> column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" >> >> You can try create additional index as >> CREATE INDEX ev4_idx ON events (type,eid desc); >> >> ___ >> 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] Index and ORDER BY
Sounds promising. I'll give it a try. Thanks! Jeff On Jun 28, 2008, at 4:29 AM, Alexey Pechnikov wrote: > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND >> ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database- > name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > 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] Index and ORDER BY
16 million On Jun 28, 2008, at 4:25 AM, Alexey Pechnikov wrote: > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson > написал(а): >> I have a large table and a two column index: > > How much rows are you have? > ___ > 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] Index and ORDER BY
(sorry about my poor english) I think that the problem doesn't correct by creating a DESC index. The problema was at de 'where clausula': (events.eid<=3261976) For some reason SQLITE doesn't optimize the query to use the index to locate the last index item that have type=22 and eid<=3261976. Of course if you have only a few items that have tid=9, SQL must have to read every item starting with de last until he find the item that haves tid=9. Do you try to the prevoius sugest: add a index that have type and tid? You must know that, SQLite must read the items to find verbose<=1 good luck! Emilio Alexey Pechnikov escribio': > В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. > > As described in http://www.sqlite.org/lang_createindex.html > "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] > index-name > ON table-name ( column-name [, column-name]* ) > column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" > > You can try create additional index as > CREATE INDEX ev4_idx ON events (type,eid desc); > > ___ > 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] Index and ORDER BY
В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): > When I do the following query: > > SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) > AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid > DESC LIMIT 1; > > it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", > it's very fast. As described in http://www.sqlite.org/lang_createindex.html "sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name ON table-name ( column-name [, column-name]* ) column-name ::= name [ COLLATE collation-name] [ ASC | DESC ]" You can try create additional index as CREATE INDEX ev4_idx ON events (type,eid desc); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
В сообщении от Saturday 28 June 2008 02:28:05 Jeff Gibson написал(а): > I have a large table and a two column index: How much rows are you have? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
Thanks for your help. I created the index: CREATE INDEX ev4_idx ON event(type); According to "EXPLAIN QUERY PLAN", it's being used. When I run the query: SELECT events.* FROM events WHERE ( events.type=22) AND (events.eid<=3261976) AND (tid=9) AND (verbose<=1) ORDER BY events.eid DESC LIMIT 1; It takes about 10 seconds. If I remove the tid and verbose clauses conditions, it changes very little (maybe 9 seconds). If I switch the ordering to ascending, though, the query is seemingly instantaneous. Similarly, if I compare an eid for equality, the query is nearly instantaneous. This is part of a GUI application, so a 10-second delay is highly undesirable. Any other suggestions? Thanks, Jeff D. Richard Hipp wrote: > On Jun 27, 2008, at 6:28 PM, Jeff Gibson wrote: > > >> I have a large table and a two column index: >> >> CREATE TABLE events (eid INTEGER PRIMARY KEY, >> time INTEGER, >> aid INTEGER, >> subtype INTEGER, >> type INTEGER, >> tid INTEGER, >> verbose INTEGER); >> >> CREATE INDEX ev4_idx ON events (type,eid) >> >> When I do the following query: >> >> SELECT events.* FROM events WHERE ( events.type=22) AND >> ( events.tid=9) >> AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid >> DESC LIMIT 1; >> >> it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", >> it's very fast. The query plan for both ascending and descending >> sorts >> both say: >> >> 0|0|TABLE events WITH INDEX ev4_idx ORDER BY >> >> For my application, I sometimes need the first and sometimes need the >> last match. I tried selecting MAX(eid) instead of using an ORDER BY, >> but the speed was about the same. Is there any way I can get sqlite >> to >> use the index for the descending order-by? Do I need a different >> index? Or are there any other suggestions? >> > > Every index includes the INTEGER PRIMARY KEY as its last term. So the > second term in your index is redundant. It might be confusing > things. I suggest you set up your index as simply: > > CREATE INDEX ev4_idx ON event(type); > > Or perhaps: > > CREATE INDEX ev4_idx ON event(type, tid); > > Try that and see if it works better for you. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Index and ORDER BY
On Jun 27, 2008, at 6:28 PM, Jeff Gibson wrote: > I have a large table and a two column index: > > CREATE TABLE events (eid INTEGER PRIMARY KEY, > time INTEGER, > aid INTEGER, > subtype INTEGER, > type INTEGER, > tid INTEGER, > verbose INTEGER); > > CREATE INDEX ev4_idx ON events (type,eid) > > When I do the following query: > > SELECT events.* FROM events WHERE ( events.type=22) AND > ( events.tid=9) > AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid > DESC LIMIT 1; > > it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", > it's very fast. The query plan for both ascending and descending > sorts > both say: > > 0|0|TABLE events WITH INDEX ev4_idx ORDER BY > > For my application, I sometimes need the first and sometimes need the > last match. I tried selecting MAX(eid) instead of using an ORDER BY, > but the speed was about the same. Is there any way I can get sqlite > to > use the index for the descending order-by? Do I need a different > index? Or are there any other suggestions? Every index includes the INTEGER PRIMARY KEY as its last term. So the second term in your index is redundant. It might be confusing things. I suggest you set up your index as simply: CREATE INDEX ev4_idx ON event(type); Or perhaps: CREATE INDEX ev4_idx ON event(type, tid); Try that and see if it works better for you. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index and ORDER BY
I have a large table and a two column index: CREATE TABLE events (eid INTEGER PRIMARY KEY, time INTEGER, aid INTEGER, subtype INTEGER, type INTEGER, tid INTEGER, verbose INTEGER); CREATE INDEX ev4_idx ON events (type,eid) When I do the following query: SELECT events.* FROM events WHERE ( events.type=22) AND ( events.tid=9) AND (events.eid<=3261976) AND (events.verbose<=1) ORDER BY events.eid DESC LIMIT 1; it's very slow. If I switch the ORDER BY to "ASC" instead of "DESC", it's very fast. The query plan for both ascending and descending sorts both say: 0|0|TABLE events WITH INDEX ev4_idx ORDER BY For my application, I sometimes need the first and sometimes need the last match. I tried selecting MAX(eid) instead of using an ORDER BY, but the speed was about the same. Is there any way I can get sqlite to use the index for the descending order-by? Do I need a different index? Or are there any other suggestions? Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users