Try creating an index on settings(rate)
On Thu, Sep 18, 2008 at 12:46 PM, chris <[EMAIL PROTECTED]> 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
