MySQL Forums
Forum List  »  Optimizer & Parser

Re: Strange performance results with JOIN/COUNT
Posted by: irek kordirko
Date: February 11, 2012 02:53AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Strange performance results with JOIN/COUNT
1380
February 11, 2012 02:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.