limit vs exists vs count(*) vs count(id) in MySQL [duplicate]

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.

Answer 1

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 count 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