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