Hi all. We're trying to get the following query working at a better speed and I'm wondering if anyone has any ideas on optimizations we might be able to do.
The query groups e-mail addresses and gives us the total number of each address seen within a given time range of messages, ordering from the highest to lowest count. The first time we run the query it's very slow, but speeds up with subsequent runs. That doesn't help in production because by the time it comes around to running the report the cached info is well expired. Table structure: CREATE TABLE email_addresses (email_address_ID INTEGER PRIMARY KEY AUTOINCREMENT, EMailAddress TEXT UNIQUE); CREATE TABLE messages (message_ID INTEGER PRIMARY KEY AUTOINCREMENT, TimeStamp INTEGER); CREATE INDEX messages_timestamp_index ON messages (TimeStamp); CREATE TABLE recipients (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, message_ID INTEGER, email_address_ID INTEGER, FOREIGN KEY(message_ID) REFERENCES messages(message_ID) ON DELETE CASCADE); CREATE INDEX recipients_emailAddressID_index ON recipients (email_address_ID); CREATE UNIQUE INDEX recipients_constraint_index ON recipients (message_ID, email_address_ID); Query: SELECT email_addresses.EMailAddress, COUNT(*) as OrderColumn FROM email_addresses,recipients,messages WHERE email_addresses.email_address_ID = recipients.email_address_ID AND messages.message_ID = recipients.message_ID AND CAST (messages.TimeStamp AS INTEGER) BETWEEN 0 AND 9999999999 GROUP BY email_addresses.EMailAddress ORDER BY OrderColumn DESC Table stats (these are just the relevant tables): Messages: 942,279 Recipients: 531,186 Email_addresses: 226,337 DB size is just over 1Gb On a side note, the CAST in there was a leftover from when the timestamp was a date field. Taking the cast out slows the query down? Explain suggests that the query has 2 additional instructions to perform without the CAST. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
