0

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.

Improve this question
2
  • 1
    The former is going to be better because count(*) with a condition will do a full-row scan. Commented Jun 11, 2011 at 13:17
  • Really? Even if that column is indexed? Commented Jun 11, 2011 at 13:28

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Is there not a chance (depending what's in t) that the subquery could generate a large temp table? Or is the optimizer clever enough to know from the subquery’s context to terminate when it find the first row with c = x?
@fsb: exists() will look up the index, open the relevant disk page, check that the row isn't dead, and return true as soon as it finds one that isn't. Better yet, on some engines, if statistics are so that using an index is not worth doing, it'll actually seq scan the disk pages and return true upon finding a live row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.