What is the best practice to know if an Row exists in a SELECT in Mysql?

Question

For example, when we check if a user is already registered in the table, we do not need any data return by query, just check if the number of Rows is greater than 0 (num_rows > 0). Us scripts and frameworks i see using for this purpose the following consultations:

SELECT * FROM tabela WHERE usuario = 'exemplo';

SELECT usuario FROM tabela WHERE usuario = 'exemplo';

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

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

if (num_rows > 0)
//existe
else
//não existe

Doubt:

What is the best practice to achieve performance when we need to do a query to know if a value in a column already exists?

Answer 1

You need to see the goal you want, if you really just want to know if there are values in the database the simplest among these options would be:

SELECT 0 FROM tabela WHERE usuario = 'exemplo';

I put in the Github for future reference.

It will make very little difference if you do, but it’s better than all the others because you don’t have to bring any data from the database, you don’t have to do any other operation other than selecting the line.

There is one way to find out. Test with all options and see which one is faster. You can change depending on the version.

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

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

which is basically the same thing

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

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

Which is more advantageous, read the result or ask the function to indicate how many lines returned? I don’t know, I’d have to run a test, but I doubt if I can find a difference that would justify choosing one or the other. Essentially it will be the same, it will be derisory near the whole operation, even if it runs this 1 million times.

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

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

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

This form returns a boolean indicating if there are lines.

But if the problem is having duplicate, then all the ways are wrong. You can get involved in a running condition and the result will not be reliable.

Answer 2

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 on top of this field, this is the least expensive form for the database.

Answer 3

The option LIMIT 1 makes SQL end when it finds the first record, is the fastest way to check if a record exists.

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

Answer 4

You can try

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

or

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

Answer 5

If only to know EXISTE ROW COM usuario = 'exemplo', I recommend using SELECT COUNT(1) FROM tabela where usuario = 'exemplo'. For the count(1) go through the table, check if the condition is met, and skip the other table columns. And when you receive the result just put boolean existe = resultado > 0