OK, just for yucks, I tried two versions of this in the SQL Server Query
Analyzer. The group by version had 1/3 the cost of the subquery version. The
last_name field had a clustered index.
I then did the same thing in MySQL, and lacking any other cost measurement,
looked at execution time. Before indexing last_name, the group by version
took either 20 or 10ms after the first run, and the subquery version took
10ms. After indexing, the both versions report 0ms.
These measurements are no doubt influenced by the nature of the data. I used
an existing db I have, with 4000+ rows, 35 of which are names that got
counted. For SQL Server, the table had a bunch of other columns, where the
MySQL version had just last_name and first_name.
(Obviously, the format of the results is different for the two queries. The
subquery version returns a single row, with a column for each value counted.
The group by version returns value and count columns, one row for each value
counted.)
I'd conclude that the group by version is faster, and also that you ought to
test your own data (:-).
Here are the two queries I used:
--------------------
SELECT
(SELECT count(*) FROM patients WHERE last_name = 'smith') AS count_smith,
(SELECT count(*) FROM patients WHERE last_name = 'jones') AS count_jones,
(SELECT count(*) FROM patients WHERE last_name = 'thomas') AS count_thomas
--------------------
SELECT
last_name,
COUNT(*) AS the_count
FROM patients
WHERE last_name IN ('smith', 'jones', 'thomas')
GROUP BY last_name
--------------------
Dave Merrill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
