MySQL Forums
Forum List  »  Optimizer & Parser

Re: Strange performance results with JOIN/COUNT
Posted by: Tomas Pluskal
Date: February 09, 2012 03:13AM

Sure, here you go:


mysql> explain extended SELECT msruns.description, 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 ORDER BY msruns.id;
+----+-------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | samples | index | sample_id | sample_id | 92 | NULL | 329 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | msruns | ref | msruns_ibfk_1 | msruns_ibfk_1 | 92 | metabolometomas.samples.sample_id | 1 | 100.00 | |
| 1 | SIMPLE | msrun_peaks | ref | msrun_peaks_ibfk_1 | msrun_peaks_ibfk_1 | 92 | metabolometomas.msruns.msrun_id | 708 | 100.00 | Using index |
+----+-------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain extended SELECT msruns.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 ORDER BY msruns.id;
+----+-------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | samples | index | sample_id | sample_id | 92 | NULL | 329 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | msruns | ref | msruns_ibfk_1 | msruns_ibfk_1 | 92 | metabolometomas.samples.sample_id | 1 | 100.00 | |
| 1 | SIMPLE | msrun_peaks | ref | msrun_peaks_ibfk_1 | msrun_peaks_ibfk_1 | 92 | metabolometomas.msruns.msrun_id | 708 | 100.00 | Using index |
+----+-------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> explain extended SELECT msruns.description, (SELECT COUNT(id) FROM msrun_peaks WHERE msrun_peaks.msrun_id = msruns.msrun_id) AS peaks_count FROM msruns JOIN samples USING (sample_id) ORDER BY msruns.id;
+----+--------------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | samples | index | sample_id | sample_id | 92 | NULL | 329 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | msruns | ref | msruns_ibfk_1 | msruns_ibfk_1 | 92 | metabolometomas.samples.sample_id | 1 | 100.00 | |
| 2 | DEPENDENT SUBQUERY | msrun_peaks | ref | msrun_peaks_ibfk_1 | msrun_peaks_ibfk_1 | 92 | metabolometomas.msruns.msrun_id | 708 | 100.00 | Using index |
+----+--------------------+-------------+-------+--------------------+--------------------+---------+-----------------------------------+------+----------+----------------------------------------------+
3 rows in set, 2 warnings (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Strange performance results with JOIN/COUNT
1471
February 09, 2012 03:13AM


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.