I am wondering if there is a performance difference between these two queries that check if a record exists?
select count(1) from table where id = 1;
or
select id from table where id = 1;
Add a comment
|
3 Answers
I don't think there will be much of a difference between those two queries : the difference being selecting a field (which is part of an index) in the second case, and counting one line in the first... Not that much of a difference.
Still, out of curiosity, I did a very quick benchmark of those kind of queries on a database I have on my computer -- note there are only like 7 lines in the post table, so it might not be that close to a real situation, but as there is a PK on id, which means an index....
Here's what I got :
mysql> select benchmark(10000000000, 'select sql_no_cache id from post where id = 1');
+-------------------------------------------------------------------------+
| benchmark(10000000000, 'select sql_no_cache id from post where id = 1') |
+-------------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------------+
1 row in set (1 min 0,25 sec)
mysql> select benchmark(10000000000, 'select sql_no_cache count(1) from post where id = 1');
+-------------------------------------------------------------------------------+
| benchmark(10000000000, 'select sql_no_cache count(1) from post where id = 1') |
+-------------------------------------------------------------------------------+
| 0 |
+-------------------------------------------------------------------------------+
1 row in set (1 min 0,23 sec)
So, really not that much of a difference, it seems ^^
Comments
Although Pascal is correct in his resume, his example is erroneous.
benchmark(10000000000, 'select sql_no_cache id from post where id = 1')
The above statement evaluates a string with a statement, not the SELECT statement itself. The correct syntax for the BENCHMARK function would be:
benchmark(10000000000, (select sql_no_cache id from post where id = 1))
Also, at least in MySQL 5.5.22, using SQL_NO_CACHE in the statement provided to BENCHMARK results in an error:
mysql> SELECT BENCHMARK(1000000000,(SELECT SQL_NO_CACHE COUNT(1) FROM players WHERE id=1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(1) FROM players WHERE id=1))' at line 1
mysql> SELECT BENCHMARK(1000000000,(SELECT SQL_NO_CACHE id FROM players WHERE id=1));
ERROR 1054 (42S22): Unknown column 'SQL_NO_CACHE' in 'field list'
However, an experiment with actual statements, 40000 record table and query cache turned off confirm that COUNT would work just a bit faster than retrieving the ID, even though the difference might be considered negligible.
mysql> set query_cache_type=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT SQL_NO_CACHE benchmark(1000000000,(SELECT id FROM test WHERE id=1));
+-----------------------------------------------------------+
| benchmark(1000000000,(SELECT id FROM players WHERE id=1)) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
1 row in set (23.17 sec)
mysql> SELECT SQL_NO_CACHE benchmark(1000000000,(SELECT COUNT(1) FROM test WHERE id=1));
+-----------------------------------------------------------------+
| benchmark(1000000000,(SELECT COUNT(1) FROM players WHERE id=1)) |
+-----------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------+
1 row in set (22.50 sec)
Comments
The second statement is probably faster. But for practical purposes, the difference will be negligible
