On 7 Mar 2011, at 11:07am, J Trahair wrote: > 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"
First you consider which records are needed, then which order to present them. So the naive guess is that you would index on (CostDate, CostDate, CustomerCode) but of course you don't need CostDate twice, so it's just what you had: (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" Naively: (CustomerCode, ProjectName, CostDate) but if you have lots of projects and few customers this would be better: (ProjectName, CustomerCode, CostDate) because it would allow you to find the appropriate records faster. > I am proposing therefore to have 2 indexes: > > CREATE INDEX idxCostItems1 ON CostItems(CostDate,CustomerCode); > and > CREATE INDEX idxCostItems1 ON CostItems(CustomerCode,CostDate); Apart from the fact they both have the same names, those are two indexes which would do very well. You have certainly avoided the common mistakes of people new to SQL. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
