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
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
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
В сообщении от 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
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
