Re: [sqlite] Indexes on the table
Thanks Simon for detail explaination about the indexes! JP From: Simon Slavin <slav...@hearsay.demon.co.uk> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, July 21, 2009 3:57:22 PM Subject: Re: [sqlite] Indexes on the table 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexes on the table
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
[sqlite] Indexes on the table
Hi All, I need to create the indexes on the tables and these indexes have 4 columns. Let say the table definition as below: 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? Thanks JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
