The other day I was trying to figure out how many records there were in my DB I found in a website that I needed to use COUNT(*). The example given went something like this:
SELECT COUNT(*) FROM 'table'
and while this worked as advertised, I got a very inconvenient side effect. The query was consuming more than 40% processor resources and it was taking longer than 18s to complete! now that is a lot of time to count records! even if we have upwards of 50,000.

The problem laid on the ‘*’, this query was loading every single row and every single column in that row. even thought it was only counting the rows it was consuming ridiculous amounts of resources. Of course any experienced SQL programmer would have noticed the problem, but being the first time I used COUNT() it completely bypassed me. I never found anything on that site or others that hinted at this problems.

The solution to the problem was extremely simple thought. I just changed the ‘*’ for the smallest field on the table, ‘id’ in my case,
and now a 40%-CPU-usage-18sec-completion time has been reduced to 0.001 seconds. Here is what the new query looks like

SELECT COUNT(id) FROM table

What I took from this experience is that we always should look at our queries execution time after they are deployed. it is not enough to look at them during development since, most times, the test data is far from real world.