CODEWITHSUNDEEP
mysqlsql
ycomp
ycomp

Reputation: 8563

Count the records of a query

All I need to do is to count all records that match this query... that is the only info I need.. is the count of the records... what is the most efficient way to do that?

SELECT id, country, city
FROM sales
WHERE country='Germany'
AND city='Munich'
AND closed<>0

I mean, I assume this is correct but is there a more efficient way to do this? I will be running this counting query often...

SELECT COUNT(*)  
FROM sales
WHERE country='Germany'
AND city='Munich'
AND closed<>0

Should I use * or something more specific?

Upvotes: 0

Views: 97

Answers (2)

plain jane
plain jane

Reputation: 1007

Please refer the following link.

MySQL: Fastest way to count number of rows

Hope this helps you.

Upvotes: 1

Tejas Vaishnav
Tejas Vaishnav

Reputation: 458

If you need to count the Null value also for your record count then use count(*) or if ignore the Null value to be counted then use count().

DECLARE @AA AS TABLE (ID INT, NAME VARCHAR(10))

INSERT INTO @AA VALUES(1,NULL)
INSERT INTO @AA VALUES(2,'A')
INSERT INTO @AA VALUES(3,'B')
INSERT INTO @AA VALUES(4,'C')

SELECT * FROM @AA

SELECT COUNT(*) FROM @AA

SELECT COUNT(NAME) FROM @AA

As you execute the above query you can find that there will be four records in @AA table but if you use COUNT(*) if will result 4 and if you use COUNT(NAME) it will result 3 this example is for SQL Server, but the mysql will do same.

Upvotes: 2

Related Questions