On 21 Jul 2009, at 11:12pm, Joanne Pham wrote: > CREATE TABLE myTable( > startTime INTEGER ... > appId INTEGER > myId INTEGER ... > trafficType INTEGER > ...... > ) > StartTime can be from 1...59 > appId can be from 1...256 > myId can be from 1...5000 > trafficType can be from 1..3 > > I would like to create index for this table on these columns > StartTime ,appId, myId, trafficType as : > create unique index myTableIndex on myTable(appId, myId, > trafficType, startTime). > Is the order of the columns in the create index statement > importance? If yes then what is rule of thumb here?
You choose what indexes to create depending on what SELECT commands you're going to use. So if none of your SELECT instructions use trafficType in the WHERE or ORDER BY clause there is no need for it in any index. Once you know which fields you want in an index, the principle is to reject as many rows as you can as soon as you can. This leaves the software fewer records to worry about at the next step, which means it needs less memory and has less processing to do. Suppose you have a thousand records and want something like SELECT * FROM myTable WHERE appId = 40 AND trafficType = 2 Suppose 1/3rd of your records have each traffic type, but 1/256th of your records have each appId. Then selecting on trafficType first would reject 2 records out of every 3, meaning that the next step has to process just 333 records, which is good. But selecting on appId first instead would reject 255 records out of every 256, meaning that the next step has to process just 4 records which is much better. So in this case an index on (appId, trafficType) would be research in a faster SELECT than (trafficType, appId). Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
