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

Reply via email to