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.

2 Answers 2

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;
5
  • 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. May 8, 2015 at 16:20
  • Not if you are search for the last three days of info. The index should help your existing query May 8, 2015 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. May 8, 2015 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, 2015 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) May 9, 2015 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 and acknowledge you have read our privacy policy.

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