Thanks for any responses to the following: Imagine a sqlite db with the core table of about 1-2 million rows, total size=about 4 gigs. Want to show a page or so at a time on a web based app.
========== Table structure ========== The core table is something like CREATE TABLE Main(ID TEXT,Date INTEGER, Time INTEGER,Subject TEXT, SENDER TEXT, Other Fields that don't matter) which tends to like LEFT JOINING with CREATE TABLE Recips(ID TEXT,Address TEXT, Otherfields that don't matter) and CREATE TABLE Events(ID TEXT, Event TEXT, Otherfields that don't matter) .============ queries ============ the user needs to make queries on one or more of these fields, sortable by just about any of these fields. ============= initial indices ============= This is what I thought was "reasonable" CREATE INDEX idx_core ON Main(ID,Date, Time, Subject, Sender); CREATE INDEX idx_Recipients ON Recips(ID,Address); CREATE INDEX idx_Events ON Events(ID,Event); I found that for many queries (example order by subject), the query was very slow. 20 minutes or so. =============== I then tried ============== Adding explicit individual indices (without touching the original ones) CREATE Index idx_date on main(date) CREATE Index idx_subject on main(subject) and found everything speed ============= So my question is ============= When is it appropriate to use compound indices? When single indices? Keep in mind here that the specific number of WHERE clauses and ORDER clauses needs to be flexible. It seems from the docs I've seen that compound indices only help you if you have a FROM or ORDER in the exact order of the compound indices (leaving off ones at the end being ok)?
