CODEWITHSUNDEEP
mysqlperformance
user213154
user213154

Reputation:

MySQL performance: query if an indexed column has a given value

Given the table

create table t (
  t_id int not null primary key,
  c int not null,
  ...
  key (c)
)

I want to query if any row in t has c equal to X. I just need a yes/no answer.

Since column c is indexed, MySQL should be able to look in the index and stop as soon as it finds one value X. What queries will get the best performance out of MySQL?

Do you have any better ideas than mine?

select 1 from t where c=X limit 1

or

select count(*)>0 from t where c=X

I prefer the former. The latter seems to require more cleverness from the optimizer.

Upvotes: 0

Views: 75

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78473

Your limit 1 approach is fine, as is using exists:

select exists (select 1 from t where c = x)

Definitely don't count. Doing so will make it actually count all matching rows before applying the > 0 condition.

Upvotes: 1

Related Questions