Actually I think you can drop the index on just rate.
On Thu, Sep 18, 2008 at 3:12 PM, John Stanton <[EMAIL PROTECTED]> wrote: > I would get rid of your separate columns for day, month and year and use > the Sqlite date format and use an index on it. > > 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? >> >> Thank you for any suggestions. >> >> >> >> _______________________________________________ >> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
