Is it possible to get count(*) and * more efficiently ?

Question

I'm now running two queries,which is not so efficient I think:

select count(*) from table where id>0;
select * from table where id>0 limit 10;

Answer 1

You can do it using SQL_CALC_FOUND_ROWS.

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

But check out this blog entry about the performance of SQL_CALC_FOUND_ROWS.

Answer 2

If you are using PHP just run mysql_num_rows on the query.

$query = mysql_query("SELECT * FROM ... WHERE ...");
$rows = mysql_num_rows($query);