Re: [GENERAL] temporary table as a subset of an existing table and indexes

2010-11-09 Thread Matthieu Huin
Basically, I take the same query as above and replace all occurences of tables logs and tags with temp_logs and temp_tags, created as follow: CREATE TEMPORARY TABLE temp_logs ON COMMIT DROP AS SELECT * FROM logs WHERE condition ORDER BY date DESC LIMIT max_size; CREATE TEMPORARY TABLE temp_tags

Re: [GENERAL] temporary table as a subset of an existing table and indexes

2010-11-09 Thread Merlin Moncure
On Tue, Nov 9, 2010 at 4:47 AM, Matthieu Huin wrote: > Hello Merlin, > > So far the improvement in responsiveness has been very noticeable, even > without indexing the temporary tables. Of course, this is just trading > accuracy for speed as I simply narrow arbitrarily the search space ... > > The

Re: [GENERAL] temporary table as a subset of an existing table and indexes

2010-11-09 Thread Matthieu Huin
Hello Merlin, So far the improvement in responsiveness has been very noticeable, even without indexing the temporary tables. Of course, this is just trading accuracy for speed as I simply narrow arbitrarily the search space ... The schema I am working on is close to the one I am referencing i

Re: [GENERAL] temporary table as a subset of an existing table and indexes

2010-11-08 Thread Merlin Moncure
On Mon, Nov 8, 2010 at 12:15 PM, Matthieu Huin wrote: > Greetings all, > > I am trying to optimize SELECT queries on a large table (10M rows and more) > by using temporary tables that are subsets of my main table, thus narrowing > the search space to a more manageable size. > Is it possible to tra