Most efficient way of getting mariaDB/SQL record count

Question

A question on a simple SQL statement, but one which I sometimes wonder over. Thought I'd see if anyone knew the answer to.

When counting the records in a table using a simple SQL statement, which has the least overheard:

1) SELECT COUNT(single_primary_field) FROM table, i.e. SELECT COUNT(user_ID) FROM users;

2) SELECT COUNT(*) FROM table

I initially thought the first may be quickest. But perhaps not having a specific field to associate with makes the second quicker?

Probably makes very little difference speed wise either way.

Thanks

Does this answer your question? [Which is faster/best? SELECT \* or SELECT column1, colum2, column3, etc](https://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc) — Simas Joneliunas, Nov 22 '19 at 06:43

score 2 · Answer 1 · answered Nov 22 '19 at 07:01

2

COUNT(column) counts only selected column and ignore the null values.

COUNT(*) count rows and don't care values in the columns.

Using COUNT(*) is a better way for counting rows.

answered Nov 22 '19 at 07:01

Serkan Arslan

score 0 · Answer 2 · answered Nov 22 '19 at 06:47

0

Count(*) is most efficent way to count according to mysql: Count

answered Nov 22 '19 at 06:47

Nouman Janjua

score 0 · Answer 3 · answered Nov 22 '19 at 06:59

0

Have a read through https://mariadb.com/kb/en/library/explain/ to look at what type of indexing your query is using, it usually hints at its performance.

I think Count(*) is going to be the fastest because maria does store a running count.

answered Nov 22 '19 at 06:59

Roland Warburton

3 Answers3