limit vs exists vs count(*) vs count(id) in MySQL

Question

I just want to know which one is the fastest.

What I'm trying to do is to just check if the data is existing on the table. I've been using "LIMIT" most of the time but in your opinion or if you have basis, which one is the fastest to check if data is existing.

Example:

limit 1:

SELECT ID 
       FROM TABLE 
       WHERE ID=1 LIMIT 1;

exists:

SELECT EXISTS(
              SELECT * 
              FROM TABLE 
              WHERE ID=1);

count(*):

SELECT (*) 
FROM TABLE;

count(ID):

SELECT (ID) 
FROM TABLE;"

Additional: I'm using InnoDB.

I do not understand why the first queries have "WHERE ID=1" and then you removed that in the last 2 queries. Please fix. Now the edit queue is full. — Valerio Bozz, Jun 21 '22 at 14:11
In case that `ID` isn't your `NOT NULL` `PRIMARY KEY` and could contain `NULL` values, then case 3 and 4 would return different results, as `COUNT` counts up all the non-null values given. In these cases, I prefer to use count with a hardcoded value, like `COUNT(1)` — LukasKroess, Sep 01 '23 at 06:24

score 24 · Accepted Answer · edited Aug 30 '23 at 16:50

24

Limit is always the fastest, because it iterate one line of the table.

Exists has little difference with Limit because you just add another select statement, we can say it has the same efficiency as the first one.

Count will iterate all the table and count the result. When you use count(), by default, mysql counts the primary key of the table. I've done some tests of count(id), count(), count(field) and count(1) in big table, there is no big difference. In my opinion, 'count' will always try to count the index unless the field you count is not an index, but many people said that we should use count(id) rather than use count(*).

In a small table, the four ways all work fine. But if you join with some big table, count will take a very very long time.

So in all, the time used is count(*) > count(id) >> exists > limit

edited Aug 30 '23 at 16:50

danronmoon

3,814
5
34
56

answered Nov 15 '13 at 10:20

user2208436

359
1
2

nice explanation 'bro.. :D, I guest I will stick to LIMIT – Echusen Nov 15 '13 at 10:37
Not always true that each row is iterated, on an indexed column count will be near immediate. SELECT COUNT(\*) FROM urls_old; Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0.050 sec. SELECT hisID FROM urls_old LIMIT 1; Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0.047 sec. In my test with 173 million rows limit 1 was still slightly faster, but still not iterating through all the rows with count. – Jeff Apr 16 '21 at 19:22
It really depends by the MySQL/MariaDB version and the use case and the number of rows and the specific query (the one in the question is too generic) and your indexes etc. Moreover the question is a duplicate of this: https://stackoverflow.com/questions/5264658/is-exists-more-efficient-than-count0 that says a different thing about "LIMIT". – Valerio Bozz Jun 21 '22 at 14:05

score -2 · Answer 2 · answered Nov 15 '13 at 09:34

-2

I think they are all fine; except I would remove the WHERE ID = 1 clauses. If you ever clear your table and start re-inserting then ID 1 will not exist. Just LIMIT 1 will do the trick. Personally I don't favour the exists and count(*) options. I would prefer count(ID) then, as you would normally have an index on ID so I would expect that to run fairly quickly. To be sure, you would have to time them (on a really big table) - I expect them to come out something like exists, limit 1, count(id), count(*) from fastest to slowest. (I am in doubt about the exists though - if it actually evaluates the whole SELECT * it may come out worst).

answered Nov 15 '13 at 09:34

CompuChip

9,143
4
24
48

2

I think the TS wants to check if a specific row exist in the table, not if a row exists, and I asume `1` is just an example – Martijn Nov 15 '13 at 09:35
1

In that case not all four statements he provided are equivalent, and he should clarify! – CompuChip Nov 15 '13 at 09:43
1

`(I am in doubt about the exists though - if it actually evaluates the whole SELECT * it may come out worst).` no, `exists` doesn't evaluate, it completely ignore what you've selected – Andre Figueiredo Feb 09 '21 at 20:39

limit vs exists vs count(*) vs count(id) in MySQL

2 Answers2