-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Rachmat Febfauza
Sent: Sunday, December 28, 2008 9:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] confusing with how to to this in sqlite
thanks simon for the explanation.
after holiday, i works on how to optimize my query. actually awal1 table
consist 12000 rows and akhir1 too. how to improve performance?
i added index on table awal1 and akhir1 with following syntax :
create index awal1i1 on awal1(Code,Category,Product,Location,"Begin");
create index akhir1i1 on akhir1(Code,Category,Product,Location,"End");
is this create index syntax right? or i must specify each column with
individual index?? like create index awal1i1 on awal1(Code); create
index awal1i2 on awal1(Product); etc
and i want to know to to improve performance of my query? some hint?
i have one question again, is sqlite suitable for large database file?
coz my apps may grow up to 1 giga database file.
thanks again
=========================================================
=========================================================
Regarding syntax:
If you don't get an error, the syntax is acceptible. ;-)
Sqlite *does* support compound indicies. However:
-- You may want to use "EXPLAIN QUERY PLAN" as a prefix to your
SELECT (just running as a test) to ensure than your index is used.
-- You can quickly experiment with using a simple index on "BEGIN" or
"PRODUCT" instead and measure times.
-- As you measure times, be aware of possible "caching effects" --
i.e. the first run may be slower than subsequent runs of the a query on
the same tables.
-- Make sure you see the link on performance, below.
-- Make sure you include many INCLUDES within a single TRANSACTION
(if appropriate to your application). This can make a dramatic
difference.
-- You want to be familiar with the PRAGMA's that can affect
performance.
http://www.sqlite.org/pragma.html (but note that some of
these can be used to trade data safety for performance -- make sure
you're making an informed choice)
Regarding:
" is sqlite suitable for large database file? coz my apps may grow
up to 1 giga database file."
Have you read http://www.sqlite.org/whentouse.html
And http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations ? If
not, you'll want to.
Many folks successfully run sqlite on multi-gigabyte databases, BUT
-- in those cases, the simplicity and small footprint of sqlite may
be less compelling,
-- Are there any features in http://www.sqlite.org/omitted.html
that you will grieve in their absence? You might go over the detailed
feature lists for postgres, Mysql, etc. with the same question in mind.
-- How much concurant access do you anticipate?
-- Will you control the queries (so as to optimize them and the
indicies) or will the database be subjected frequently to ad hoc queries
(which *might* benefit from a sophisticated query optimizer)?
Hope this helps,
Donald
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
