Hi Tomas,
first query retrieves a msruns.description column, while the other queries don't retrieve this column.
The type of this column is text, and as they say in the documentation:
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
Quote
The internal representation of a table has a maximum row size of 65,535 bytes, even if the storage engine is capable of supporting larger rows. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size. For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer.
It means, that mysql, while running this query, doesn't store values from description column directly in the temporary table, but copies them into different area of memory (separate file - or maybe files - on disk).
Because the query is using joins (and one left join), values from description columns are "multiplied" several times, and stored in the temporary table.
The msruns table contains about 23 thousands rows, so I geuss that the query creates 23 thousands "copies" of values from this columns, and stores them in temp.
Try to retrieve only a substring of this column, for example:
SELECT substr( description, 1, 50), ......
I bet that with substring the query will run several times faster, because result of substr function is type of 'varchar', not 'text', and the query will store these values directly in the temporary table.
The first query can be rewritten using orrelated subquery, in this way:
SELECT ( SELECT description FROM msruns m
WHERE m.msrun_id = xxx.msrun_id
) description ,
xxx.peaks_count
FROM (
SELECT msrun_id, COUNT(msrun_peaks.id) AS peaks_count
FROM msruns
LEFT JOIN msrun_peaks USING (msrun_id)
JOIN samples USING (sample_id)
GROUP BY msrun_id
) xxx
ORDER BY xxx.id;
This query copies values from description only as many times as it is really required, and not "multiplies" these values in memory (disk).
Edited 1 time(s). Last edit at 02/10/2012 08:50PM by irek kordirko.