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