Someone correct me if I'm wrong, but I don't think that UNIQUE (id_norm,word,wform) is going to have the desired result. Won't that create a single tricolumn unique index? I suspect this table needs a separate index for each. Just put the UNIQUE keyword (with no arguments) after the type on each column.
Secondly, I think the problem is AUTOINCREMENT. SQLite will alias id to the rowid (the internal autoincrementing id) if and only if the type is "INTEGER PRIMARY KEY". It is very picky about this. AUTOINCREMENT is probably messing it up (and meaningless in SQLite anyway). If that doesn't help, we probably need to know the error. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Unabashed Sent: Wednesday, October 21, 2009 9:47 AM To: sqlite-users@sqlite.org Subject: [sqlite] Slow SELECTs in application Hello! I'm using SQLite as DB in my application. My problem consists of two aspects. First , it works great, but on large data SELECTs are very slow (10-20s!). Queries are in one transaction. My table structure is: CREATE TABLE mgWords ( id INTEGER PRIMARY KEY AUTOINCREMENT, id_norm INTEGER, word TEXT, wform TEXT) It seems, that the problem is in sql. How to optimize table to have fast selects? Second part of my trouble is in using unique values. When i'm trying to change structure to CREATE TABLE mgWords ( id INTEGER PRIMARY KEY AUTOINCREMENT, id_norm INTEGER, word TEXT, wform TEXT, UNIQUE (id_norm,word,wform)) and use INSERT INTO mgWords (id_norm,word,wform) VALUES (0,'aword','awform') it clauses error. I'm not good in sql, so I'll be very glad to receive a professional answer. Sorry, please, for my English - this is not my native language. Thank you fo reading it :) -- View this message in context: http://www.nabble.com/Slow-SELECTs-in-application-tp25992880p25992880.ht ml Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ 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
