On Jan 23, 2009, at 11:50 AM, manohar s wrote:
> I have a SQLite database which is of size 1.5 GB. The problem that
> it is
> taking a lot of time (12 seconds after execution of vacuum) to
> execute a *
> SELECT* query.
>
> Here is the create Table statement:
> CREATE TABLE IF NOT EXISTS [snapshot_master] (
> PRIMARY KEY (metric_id,
> timestamp_id),
> [timestamp_id] INTEGER
> NOT NULL,
> [metric_id] INTEGER NOT
> NULL,
> [metric_now] TEXT,
> [metric_diff] TEXT
> )
>
> Index on this Table for the column:
> *timestamp_id*
>
> Query :
> SELECT metric_id, MAX(timestamp_id) AS timestamp_id_max FROM
> snapshot_master GROUP BY metric_id
Have you tried:
CREATE INDEX xyzzy ON snapshot_master(metric_id, timestamp_id);
>
>
> I do not understand why this query has to take 12 seconds?? I tried to
> optimize by creating more indexes but no luck.
> BTW, before execution of vacuum, time taken for Query to execute is
> aprrox.
> 17 minutes(1074890649319 nano seconds)
>
> I am using sftp_profile for finding out the time.
>
> 1) What am I doing wrong here?
> 2) Is there any problem in the database design?
>
> Waiting for your response,
> Manohar.S
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
D. Richard Hipp
d...@hwaci.com
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
