CODEWITHSUNDEEP
mysqlsql
Ted Wilmont
Ted Wilmont

Reputation: 463

Modifying a MySQL Query to Stop Writing Temporary Table to Disk

We are improving some of our SQL queries around the site and have noticed a bottleneck with a particular SQL query that seems to dump the results into a temporary disk table. This is expensive and of course awfully slow.

We have considered moving the MySQL temporary directory over to a RAMDISK but thought we could try to optimize it first.

Here is the command that dumps to disk:

select
  d.url,
  d.lid,
  d.title, 
  d.description, 
  d.date, 
  d.hits, 
  d.downloadratingsummary, 
  d.totalvotes, 
  d.totalcomments, 
  d.filesize, 
  d.version, 
  d.homepage, 
  d.ns_compat, 
  d.ns_des_img, 
  t.type
from downloads_downloads d
LEFT JOIN downloads_type t on d.lid = t.lid
where cid=91
ORDER BY FIELD(COALESCE(t.type,-1),1,-1,2,4,5,0,3), date DESC limit 0,20

Is there any simple way to reorganize/modify the above query to minimize temporary table creations on disk?

While we have experience with MySQL, performance optimization is still something new to us so any help anyone can give is always greatly appreciated.

Thanks for any help in advance.

Structure of both referenced tables

downloads_downloads

downloads_downloads table structure

Indexes

indexes

downloads_type

downloads_type table structure

Indexes

indexes2

Output of EXPLAIN

EXPLAIN output

Environment:

OS X running MySQL 5.5.23 with 16GB RAM.

Upvotes: 3

Views: 2694

Answers (1)

krokodilko
krokodilko

Reputation: 36127

I think these links can help to optimise this query:
http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html
http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

First conclusion - A filesort (a temporary table) cannot be avoided, since the query in the ORDER BY clause contains columns from two tables, and contains also an expression, therefore mySql cannot optimize it using indices:

In some cases, MySQL cannot use indexes to resolve the ORDER BY ................
These cases include the following:
- You use ORDER BY with an expression that includes terms other than the key column name
- You are joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows.


Second conclusion - because the table contains a TEXT column, MySql cannot use a modified (optimized) filesort algorithm, but has to use an oryginal algorithm. A drawback of the oryginal algorithm is that it reads table rows twice, and reads them in a random manner, which is much slower than a sequential read:

One problem with this approach is that it reads rows twice: One time when evaluating the WHERE clause, and again after sorting the pair values. And even if the rows were accessed successively the first time (for example, if a table scan is done), the second time they are accessed randomly. (The sort keys are ordered, but the row positions are not.)


Third conclusion - since the table contains a TEXT column, MySql cannot use an in-memory temporary table, but has to store it on the disk:

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
- Presence of a BLOB or TEXT column in the table



Taking the above into consideration, move the TEXT column into another table:

create table downloads_description(
  lid int(11) not null unique,
  description text,
  constraint dd_fk foreign key  (lid)
  references downloads_downloads( lid )
);

insert into downloads_description( lid, description )
select lid, description
from downloads_downloads;

alter table downloads_downloads
drop column description;

and then rewrite the query into:

SELECT 
  d.url,
  d.lid,
  d.title, 
  dd.description, 
  d.date, 
  d.hits, 
  d.downloadratingsummary, 
  d.totalvotes, 
  d.totalcomments, 
  d.filesize, 
  d.version, 
  d.homepage, 
  d.ns_compat, 
  d.ns_des_img, 
  t.type
FROM downloads_downloads d 
JOIN(
    select   d.lid, t.type
    FROM downloads_downloads d
    LEFT JOIN downloads_type t
    ON d.lid = t.lid
        WHERE cid = 81
    ORDER BY FIELD(COALESCE(t.type,-1),1,-1,2,4,5,0,3), 
          d.date DESC 
    limit 0,20
) t ON d.lid = t.lid
JOIN downloads_description dd
ON dd.lid = d.lid
ORDER BY FIELD(COALESCE(t.type,-1),1,-1,2,4,5,0,3), 
         d.date DESC 
;

Upvotes: 2

Related Questions