mysql copying to tmp table duration

Question

I have a web site which started to have performance issues because of a mysql query which is executed for each request.

I have the following table structure.

Post table (125000+ records) id: primary key, index on thread_id and type columns

Thread table (48000+ records) id : primary key

I'm running the following query

select t.id, t.subject, count(distinct p.id) as postCount
                from thread t
                inner join post p on p.thread_id = t.id
                where p.posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
                and p.type = 0
                group by p.thread_id
                order by max(p.posted_on) desc
                limit 0, 25

mysql explain result :

Mysql profile :

As you can see from profiling response main problem is copying the tmp table part. I tried increasing the max_heap_table_size and tmp_table_size parameters to 256MB.

Answer 1

Rather than tweaking configs for query optimizing internals, do the following

SUGGESTION #1

You may want to refactor the query as follows

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and 
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc
limit 0, 25;

SUGGESTION #2

Create a compound index for the subquery

ALTER TABLE post
    DROP INDEX type,
    ADD INDEX type_posted_on_ndx (type,posted_on)
;

GIVE IT A TRY !!!

ALTERNATE QUERY

select
    t.id, t.subject,
    count(distinct p.id) as postCount
from thread t left join
(
    select id,thread_id from post where type=0 and 
    posted_on >= DATE_SUB(CURDATE(), INTERVAL 3 DAY)
    order by thread_id
    limit 0,25
) p on t.id = p.thread_id
group by p.thread_id
order by max(p.posted_on) desc;

Answer 2

This is the new profile for the same query (the original query i posted) with the (type, posted_on) compound index. Much better results !