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
