Ritesh Kapoor wrote:
Hi,
I need to optimize/speed up my 'select' queries. The query creates
about 3 to 6 left joins from 7 different tables depending on the
different conditions passed. The problem is that this is taking a lot
of time.
The table column's are of both varchar and integer types - on which
comparisions are done to collect the final data. Could you advise me on
how to create indexes to speed up my queries.
1. A little background on how indexes work - or a link containing info
on this.
2. Do I need to create an index on each column for each table or create
an index for each table on all of its columns?
3. Please note that the columns on which comparisions are done are of
varchar type also so do indexes work on them as well?
4. Any other suggestions which the experienced folks would have come
across.
Thanks and Regards,
Ritesh Kapoor
Atrenta Pvt. Ltd.
Check out these pages for information on optimizing SQLite queries.
http://www.sqlite.org/php2004/page-001.html
http://www.sqlite.org/optoverview.html
http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning
http://www.sqlite.org/cvstrac/wiki?p=QueryPlans
http://www.sqlite.org/lang_explain.html
http://www.sqlite.org/lang_analyze.html
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
There is also an undocumented EXPLAIN QUERY PLAN command that can help
when optimizing complex queries. It work like the EXPLAIN command, but
displays the order that tables are scanned, and which indexes are used.
HTH
Dennis Cote
