Thesql copying to tmp table duration

MySQLprofiler

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
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.

Best Answer

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;

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Mysql – How to optimize the thesql setup to create the index faster

For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.

Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:

Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab

none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

Next, create a folder called /mysqltmp and mount the RAM disk on it

mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

Add this to my.cnf and restart mysql

[mysqld]
tmpdir=/mysqltmp

Now, any tmp table made via DDL lands in the RAM disk.

Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?

CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

This will prevent any moving around of BLOB data when indexing.

From here, you would have to always join data_store using its name like this:

SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

Making these changes will sidestep this whole mess of dealing with keycache, RAM disks, and tmp tables.

Give it a Try !!!