-1

I want to count all of rows in table that match to my condition as fast as mysql can do.
So, i have four SQL and want you to explain all of them, how is it different for each SQL?
and which is fastest or best for query times and server performance?
or it has another way that can better than these. Thank you.

select count(*) as total from table where my_condition
select count(1) as total from table where my_condition
select count(primary_key) as total from table where my_condition

or 

select * from table where my_condition
//and then use mysql_num_rows()
Marcus
  • 295
  • 4
  • 16
  • Any of the first three should be optimised my MySQL to give equal performance. Last is highly inefficient because it requires MySQL to prepare all data to be returned – Mark Baker Jul 11 '14 at 16:28
  • 1
    The first three are all pretty equivalent. The last could be a performance nightmare, as all the data needs to be passed back to the application. – Gordon Linoff Jul 11 '14 at 16:28

1 Answers1

0

First of all don't even think about doing the last one! It literally means select all the data I have in the database, return it to the client and the client will count them!

Second, you need to understand that if you're counting by column name, count will not return null values, for example: select count(column1) from table_name; if there is a row where column1 is null, it will not count it, so be careful.

select count(*), select count(1), select count(primary_key) are all equal and you'll see no difference whatsoever.

Ali
  • 3,568
  • 2
  • 24
  • 31
  • 1
    technically wrong on the 4th one. it does force mysql to do a lot of unecessary work, but actual row data will NOT be transferred until you issue a fetch() call. That doesn't mean mysql won't start buffering some of the data on the server so it's ready for the actual fetch, plus copying to temp tables and whatnot. but it doesn't send anything to the client except metadata until you actually do that fetch call. – Marc B Jul 11 '14 at 16:32