I have looked at some information on indexing, and watched a 44 minute presentation by Dr Richard Hipp.
I looked at the project I am converting to SQLite, and I have identited all the SELECTs for all the tables. They do not fall easily into the 'SELECT x, y, z FROM Table1 WHERE w = 5 AND x = 6 ORDER BY x, y' example. Picking one of my tables at random (the first one, in fact): CREATE TABLE CostItems( RecNo INTEGER PRIMARY KEY AUTOINCREMENT, CustomerCode TEXT, ProjectName TEXT, SupplierName TEXT, WhatExactly TEXT, CostDate TEXT etc. ); I have 2 SELECTs for this table: Find all cost items between two dates and present in groups of date, and in order of Customer Code within any date: "SELECT * FROM CostItems WHERE CostDate >= '" & Format(dateStartDate, "yyyy-MM-dd") & "' AND CostDate <= '" & Format(dateEndDate, "yyyy-MM-dd") & "' ORDER BY CostDate, CustomerCode" and Find cost items for one of a customer's projects for all dates and present in date order: "SELECT * FROM CostItems WHERE CustomerCode = '" & gstrCustomerCodeOnly & "' AND ProjectName = '" & ComboBox2.Text & "' ORDER BY CostDate" I am proposing therefore to have 2 indexes: CREATE INDEX idxCostItems1 ON CostItems(CostDate,CustomerCode); and CREATE INDEX idxCostItems1 ON CostItems(CustomerCode,CostDate); Questions: 1. Do the differing orders of fields in the 2 SELECTs require me to use 2 indexes as above? 2. In the event that other SELECTs for other tables turn out to be more complex than these two (which they are), would a maximum of (say) 4 indexes per table be too many? Thanks in advance. Jonathan Trahair _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
