What is the best practice to know if a row exists in a SELECT in MySQL?

You need to see the purpose you want, if you really only want to know if there are values in the database, the simplest of these options would be:

SELECT 0 FROM tabela WHERE usuario = 'exemplo';

It will make very little difference if you do, but it is better than all the others because you do not have to bring any data from the database, you do not have to do any other operations other than selecting the line.

There's a way to know. Take the test with all the options and see which one is faster. It may change depending on the version.

This form is great if you want to know how many already exist or if you are sure there will be 0 or 1 element. But it sucks if there can be several because it will return several lines unnecessarily. Then the best would be:

SELECT COUNT(0) FROM tabela WHERE usuario = 'exemplo';

That's basically the same thing as

SELECT COUNT(*) FROM tabela WHERE usuario = 'exemplo';

The advantage of this is that it already returns the number of rows found, so it is guaranteed that the result will only have one row, less traffic generated. And you can read the query result to see if it has more than 0 rows.

What is more advantageous, reading the result or asking the function to indicate how many rows returned? I do not know, I'd need to do a test, but I doubt I'll find a difference that justifies choosing one or the other. Essentially it will be the same, it will be derisory close to the whole operation, even if it runs 1 million times.

The only question this way is that it will not be able to use the proposed verification code if (num_rows > 0) , after all, the number of lines in this query will always be 1, since what counts is the row count that satisfies the condition and not the lines themselves.

If you can change this check, then it may be interesting to return a boolean indicated by the existence of rows that match the condition:

SELECT (SELECT COUNT(*) FROM tabela WHERE usuario = 'exemplo') > 0;

This form returns a boolean indicating whether there are rows.

But if the problem is to have a duplicate, then all the ways are wrong. You can get involved in a race condition and the result will not be trusted.

The ideal would be:

SELECT usuario FROM tabela WHERE usuario = 'exemplo';

Considering that the 'user' field will not be repeated (it will be unique or primary key) and there will be an index created over that field, this is the least expensive form for the database.

If it's only to know EXISTE ROW COM usuario = 'exemplo' , I recommend using SELECT COUNT(1) FROM tabela where usuario = 'exemplo' . % W / w% will scroll through the table, check if the condition is satisfied, and ignore the other columns in the table. And when you get the result just put count(1)

You can try

SELECT EXISTS(SELECT * FROM tabela WHERE usuario = 'exemplo')

or

SELECT EXISTS(SELECT 1 FROM tabela WHERE usuario = 'exemplo')

LIMIT 1 causes SQL to terminate when you find the first registry, is the fastest way to check if a registry exists.

SELECT
  1
FROM
  tabela
WHERE
  usuario = 'exemplo'
LIMIT 1
;