Wow! Thank you, all! Just adding the index dropped the query time down into the 3 second range. These two versions had a similiar effects: CREATE INDEX idx1 ON settings(rate, year, month); CREATE INDEX idx1 ON settings(rate); I'll experiment with this whole new world of INDEX and maybe I can get it down even further. Thank you!
On Thu, Sep 18, 2008 at 3:31 PM, Jeffrey Becker <[EMAIL PROTECTED]>wrote: > 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
