On Sep 18, 2008, at 12:46 PM, chris wrote:
> I'm at a loss and need some guidance. My queries are taking way
> longer than I can use but I'm not sure what steps to take next.
>
> I'm using SQLite 3.6.2 on a small reasonable powerful embedded
> platform with 256M ram. I'm using Python to access the database.
> I've created a very simple database file with two tables:
>
> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY
> AUTOINCREMENT, year INTEGER, month INTEGER, day INTEGER, hour
> INTEGER, minutes INTEGER, seconds INTEGER, curtemp TEXT, relhum
> TEXT, watts TEXT, rate TEXT )
>
> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY
> AUTOINCREMENT, year INTEGER, month INTEGER, day INTEGER, hour
> INTEGER, minutes INTEGER, seconds INTEGER, point TEXT )
>
> The SETTINGS table has about 95,000 entries.
>
> This single query takes between 9 and 12 seconds to run:
> SELECT rate, day, month, rate * SUM(watts) / 12000 AS
> TotalBillAtRate FROM settings WHERE (day <= 18) AND (month = 9) AND
> (year = 2008) GROUP BY rate
>
> Is there something I can do to get that time down substantially?
Try this and see if it helps:
CREATE INDEX idx1 ON settings(rate, year, month);
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
