1

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 post table

Thread table (48000+ records) id : primary key enter image description here

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 :

enter image description here

Mysql profile :

enter image description here

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.

0

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;
  • refactored query takes more than 4 seconds to execute (with or without the compound index). but existing query runs in 0.08 seconds with the updated index. thanks for the suggestion. do you think adding an index on a datetime column would degragade the performance in the long run. – user3610405 May 8 '15 at 16:20
  • Not if you are search for the last three days of info. The index should help your existing query – RolandoMySQLDBA May 8 '15 at 16:23
  • I just added another query you can try. BTW if someone else comes up with a better solution, you can unaccept my answer and accept that better answer. – RolandoMySQLDBA May 8 '15 at 17:46
  • The new index is vital. Notice how it also moves the optimizer away from starting with the smaller table. – Rick James May 8 '15 at 20:52
  • @RolandoMySQLDBA alternate query doesn't return the same result set and it is still slower than the original. I have another question about the compund index. would it make sense to change the order of columns in the index. i mean (posted_on, type). how would it affect insertion time. (we have 1000 - 1500 record insertions to post table every day) – user3610405 May 9 '15 at 8:42
0

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.