On 12 Nov 2011, at 3:43am, Josh Gibbs wrote: > We are struggling to find a way to rework this query in a way > that performs efficiently for large data sets.
It's all about the indexes. > The goal is to find the top x most active senders of e-mails > within a date range. Do you frequently do this for many of random date ranges ? Or do you have a set of standard requirements, like 'Most popular posters today, this week, this month.' ? > WHERE > CAST (messages.TimeStamp AS INTEGER) BETWEEN 0 AND 9999999999 It's a little late at night, so forgive me. What is this for ? What formats are the TimeStamps without CASTing ? Can you store the TimeStamps as integers, or even get rid of this entirely ? Oh hold on ... this is just for testing and in real life you set it to a smaller span ? > ORDER BY Total DESC This is what you say is killing you. Just noting it. > CREATE INDEX messages_timestamp_index ON messages (TimeStamp); Because you do a CAST in your 'WHERE' clause, I don't know whether this index would actually be used. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
