Re: Strange performance results with JOIN/COUNT
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)
Subject
Views
Written By
Posted
2419
February 08, 2012 06:25PM
1159
February 09, 2012 03:01AM
Re: Strange performance results with JOIN/COUNT
1533
February 09, 2012 03:13AM
1468
February 11, 2012 02:53AM
1116
February 12, 2012 06:44PM
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.