Suppose I have a huge table with 500,000 rows in MySQL.
Which is faster,
- getting the number of rows in a table or
- getting all the data with 8 columns?
Asked
Active
Viewed 66 times
0
-
Please read [ask] and try searching before asking a new question. – CodeCaster Aug 31 '18 at 12:17
1 Answers
1
If you are calling MySQL from your ASP.net code, then the most efficient way by far to get a count of all records in a table is to do SELECT COUNT(*), or something equivalent to this. The reason is that returning all records takes a potentially huge amount of bandwidth. If all you want is a count of all records, then you don't really care about the data in those records.
Besides bandwidth/network overhead, there is the cost of counting the records themselves. Databases were designed with such aggregate operations in mind, application languages less so.
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
-
@Tim Biegeleisen I have read somewhere that COUNT(id) is faster than COUNT(*); is it correct ? – Madhur Bhaiya Aug 31 '18 at 12:09
-
@MadhurBhaiya Good question, I don't know the answer. Are you asking for MySQL or any SQL database in general? – Tim Biegeleisen Aug 31 '18 at 12:11
-
@TimBiegeleisen Although MySQL, but shouldn't it apply for SQL in general ? – Madhur Bhaiya Aug 31 '18 at 12:12
-
1@MadhurBhaiya It usually doesn't matter: https://stackoverflow.com/questions/5179969/what-is-better-in-mysql-count-or-count1 (also true for SQL Server). – Tim Biegeleisen Aug 31 '18 at 12:14
-
@TimBiegeleisen Thanks - but it is optimized for MyISAM only and when using a single table in select. I think nowadays, lot of people have started using InnoDB, and in case of JOINs, it is better to use the specific id in the COUNT() – Madhur Bhaiya Aug 31 '18 at 12:24