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

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 ON COMMIT DROP AS
SELECT * FROM tags WHERE logid IN (SELECT logid FROM temp_logs);

With condition usually defining a date window. As we are experimenting
with this approach, date has become a forced criteria. I have
experimented with partitioning, but it led to the logid primary key not
being unique anymore, which was a problem when joining data with the
tags table.

So the queries are pretty much the same, the boost in speed being simply
due to the limitation of the search space.

> How are you partitioning the tags?  Is the partitioned query doing the
> same job as the non partitioned query?   Is date a forced criteria?
> (and if it is, have you considered date partition/brute force?)
>
> merlin