John Sample wrote:
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!
A new index only on the fields ZIPL or ZIPR would propably make your
queries even slower, because they are not part of the rest of fields.
Your query could be faster, if there are two many SIMILAR values for the
tuppel (NAME,TYPE,DIRP,DIRS), in this case I would add the following two
indices:
index1: NAME,TYPE,DIRP,DIRS,ZIPL
index2: NAME,TYPE,DIRP,DIRS,ZIPR
khamis
--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
* C/C++
* Java
* .NET (including CSharp, VB.Net and other .NET components)
* Classic Visual Basic
* PHP, HTML, XML, ASP, CSS
* Tcl/Tk,
* Perl
* Python
* SQL,
* m4 Preprocessor
* Cobol
