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.