All geek questions in one place

What is the fastest way to verify that a record exists in a database?

I am looking for the fastest way to verify that a record exists ...

All my life I have done something like this ...

SELECT COUNT(`id`) FROM `table_name`

Some people do not use COUNT(id) , but COUNT(*) . It's faster?

How about LIMIT 1 ?

PS With id I meant the primary key, of course.

Thanks, advice!

+6
source share
2 answers

In most cases, COUNT(*) faster than COUNT(id) in MySQL (due to how query groupings are performed with COUNT() , it can be optimized in future versions, so both versions work the same way). But if you want to find at least one line, you can use EXISTS

simply:

 ( SELECT COUNT(id) FROM table_name ) > 0

bit faster:

 ( SELECT COUNT(*) FROM table_name ) > 0

much faster:

 EXISTS (SELECT * FROM table_name)
+7
source

If you don't care about accuracy, explain select count(field) from table incredibly fast.

http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

This link explains the difference between count(*) and count(field) . If in doubt, count(*)

As for checking that the table is not empty ...

SELECT EXISTS(SELECT 1 FROM table)

+5
source

Source: https://habr.com/ru/post/898141/

More articles:


All Articles