Most efficient way to count all rows in a table but select only one

Question

Currently I'm running these two queries:

SELECT COUNT(*) FROM `mytable`

SELECT * FROM `mytable` WHERE `id`=123

I'm wondering what format will be the most efficient. Does the order the queries are executed make a difference? Is there a single query that will do what I want?

Answer 1

Both queries are fairly unrelated. The COUNT doesn't use any indexes, while the SELECT likely uses the primary key for a fast look-up. The only thing the queries have in common is the table.

Since these are so simple, the query optimizer and results cache shouldn't have a problem performing very well on these queries.

Are they causing you performance problems? If not, don't bother optimizing them.

Answer 2

Does the order the queries are executed make a difference?

No, they reach for different things. The count will read a field that contains the number of colums of the table, the select by id will use the index. Both are fast and simple.

Is there a single query that will do what I want?

Yes, but it will make your code less clear, less maintenable (due to mixing concepts) and in the best case will not improve the performance (probably it will make it worse).

If you really really want to group them somehow, create a stored procedure, but unless you use this pair of queries a lot or in several places of the code, it can be an overkill.

Answer 3

What table engine are you using?

select count(*) is better on MyISAM compare to InnoDB. In MyISAM the number of rows for each table is stored. When doing count(*) the value is return. InnoDB doesn't do this because it supports transactions.

More info: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

Answer 4

Hi DisgruntledGoat

First of: Ben S. makes a good point. This is not worth optimizing.

But if one wants to put those two statements in one SQl statement I think this is one way to do it:

select *,count(*) from mytable
union all
select *,-1 from mytable where id = 123

This will give one row for the count(*) (where one ignores all but the last column) and
as many rows where id = 123 (where one ignores the last column as it is always -1)

Like this:

 | Column1  | Column2  | Column3  | ColumnN  | Count(*) Column |
 ---------------------------------------------------------------
 | ignore   | ignore   | ignore   | ignore   |     4711        |
 |valid data|valid data|valid data|valid data|     -1 (ignore) |

Regards
    Sigersted