Hi, I tried with concatenated index (by dropping an index which was already present on timestamp_id) It took 12 seconds for completion, so there was no improvement in speed.
> If we might assume your table is 1.5 GB in size and you do that query you > have to do a full table scan for it. (unless SQLite can use the PK index if > it exists to speed it up). It does have a concatenated PK on timestamp_id and metric_id, so it does not have to do full table scan right? Regards, Manohar.S On Fri, Jan 23, 2009 at 10:31 PM, Michael Schlenker <m...@contact.de> wrote: > manohar s schrieb: > > 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. > > > > Query : > > SELECT metric_id, MAX(timestamp_id) AS timestamp_id_max FROM > > snapshot_master GROUP BY metric_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? > > If we might assume your table is 1.5 GB in size and you do that query you > have to do a full table scan for it. (unless SQLite can use the PK index if > it exists to speed it up). > > So your probably I/O bound, 1.5 GB in 12 seconds is about 130MB/s, sounds > like full read speed of a current harddisk. > > Michael > > -- > Michael Schlenker > Software Engineer > > CONTACT Software GmbH Tel.: +49 (421) 20153-80 > Wiener Straße 1-3 Fax: +49 (421) 20153-41 > 28359 Bremen > http://www.contact.de/ E-Mail: m...@contact.de > > Sitz der Gesellschaft: Bremen > Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe > Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users