Hello,
Running under Windows XP, using sqlite3.exe version:
3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177
As follows:
sqlite3 foo.db
The following SQL:
--------
DROP TABLE IF EXISTS rooms;
DROP TABLE IF EXISTS exits;
CREATE TABLE IF NOT EXISTS rooms (
roomid INTEGER PRIMARY KEY AUTOINCREMENT,
uid TEXT NOT NULL -- unique room ID
);
CREATE TABLE IF NOT EXISTS exits (
exitid INTEGER PRIMARY KEY AUTOINCREMENT,
fromuid STRING NOT NULL -- exit from which room (in rooms table)
);
CREATE INDEX IF NOT EXISTS fromuid_index ON exits (fromuid);
SELECT * FROM exits WHERE fromuid = '2E515665758C87202B281C7FC';
--------
Takes over 2 seconds to execute (in particular, the SELECT statement).
Two changes make it much faster. Either:
* Delete the CREATE INDEX line
or
* Change the select statement to:
SELECT * FROM exits WHERE fromuid = 'x2E515665758C87202B281C7FC';
I'm not sure what is going on, but it appears that somewhere internally SQLite3
is trying to calculate the very large number 2e515665758 (2 times 10 to the
power 515665758). And somehow the index is influencing this behaviour.
Trying under Mac OS/X (Lion) does not appear to exhibit this problem.
The string I am searching for is a hex hash string, generated by hashing
various other things (not shown here). Occasionally it would appear, the hash
"looks like" a decimal number with an exponent.
I draw your attention to the fact that the string being searched for is quoted,
and that it is declared as a text field in the database.
Any suggestions welcomed.
- Nick Gammon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
