Why don't you create and a very small version of the database, create the indices and use the EXPLAIN command to see what it does for your queries.
Steve -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of John Sample Sent: 12 August 2005 16:18 To: sqlite-users@sqlite.org Subject: [sqlite] Index and General Optimization Question Hello all. I'm a new Sqlite user porting a large db for an open source project from SQL Server. I have a question regarding whether an index makes sense or will be used for a certain scenario. Since the table in question is so large it takes up to 8 hours for me to create new indexes, so I'd like to see if I can get a best guess before trying. A little background info: This is for a GIS system. The Sqlite database is 2+ gigs at the moment. The table in question has 17,000,000 rows and is performing well so far. It has a multicolumn index on 4 columns which are always part of the queries. NAME TYPE DIRP DIRS There are 2 more columns that make up the rest of the select clause, but it is an OR statement. A pseudo select clause would look like this: NAME='char' and TYPE='char' DIRP='char' DIRS='char' and (ZIPL =int OR ZIPR=int) Now to the question: Can/will a multicolumn index (ZIPL,ZIPR) be used for an OR clause? If so, what if it was part of the other multicolumn index? Thanks for any guidance!
