I am creating a SQL Query that doesn't need to return any results from the database. All I need to do is count how many rows there are. Is there a "preferred" way of doing this, or is this the best way:
$sql = mysql_query("SELECT id FROM table WHERE stuff > 123");
$sql_count = MYSQL_NUMROWS($sql);
I have a lot of these to do, so I wanted to make sure this is the most efficient way to do a SQL count in PHP.
It used be the case in Oracle that "SELECT COUNT(1) FROM..." was faster than "SELECT COUNT(*) FROM...", but that hasn't been true in a long time. The SQL compiler built into Oracle can recognize you're doing a count and avoid doing a lot of heavy lifting not needed.
SELECT COUNT(*) as number FROM table WHERE whatever > 1234;
Instead ONLY count single field elements (keys preferably)
SELECT COUNT(id) as number FROM table WHERE whatever > 1234;
This is not really true. The only time COUNT(*) vs COUNT(id) makes a difference is when your table does not have an index defined, in which case you're designing your tables wrong. 99% of the time, your table will have an 'id' type column, which is defined as a primary key - an index column. The COUNT(*) function relies on the index column, and will not incur any additional overhead than COUNT(id) to execute.
Thank you for your clarification Czaries. He had me worried that I'd been doing it wrong for 5 years...scary
No problem... Just remember that "COUNT(*)" != "SELECT *". If all you are asking for is a integer count of the records, the specific columns are irrelevant. With SELECT, you are only asking FOR those specific columns so they are very relevant. That is the key difference between the two functions, and they are optimized accordingly.