SELECT * vs SELECT COUNT(*) vs ... - which is faster?

Question

PHP

Before inserting into the table, you need to check for the existence of a record in it by city_id.
2,000,000 records in the table, you need to check and insert another 10,000,000.
What will work faster?

SELECT 1 FROM `sl_cities` WHERE `city_id` = 3995124 LIMIT 0, 1;

SELECT COUNT(*) FROM `sl_cities` WHERE `city_id` = 3995124;

or...?

Answer 1

Why check something?
If you don't want to insert duplicate records, then hang unique and insert using INSERT IGNORE - it will obviously be faster than any additional selects there :)

Answer 2

As always - a stupid question, and even stupidly worded, gives rise to a bunch of stupid answers.
IF you answer the question outside the context of the insert, but only looking at the requests, then the answer is the SAME. In both cases, there is no calculation , but only a selection by key.
IFto delve into the context of the task a little deeper, then there are options to speed up MULTIPLE checks, such as prepared statements (that rare case when their feature with multiple execution can shoot).
IF you delve into the problem completely, then the answer of @zeromodule will be correct . Moreover, the insertion must either be done multiple, for a thousand records, or wrapped in a transaction - since INDEX UPDATE with such a number of insertions will slow down the work much more than the poor selects that frighten the author so much. And again, use prepared statements.

Answer 3

I never understood such questions, you can check what is faster 5 times while you wrote this question on the toaster.

Answer 4

first option