Most efficient way to check if value(s) exist in a table?
Having a brain block day, hence the noobish question.
Say I wanted to know if a value existed within a table, I'd usually be inclined to use a count(*):
(Or even SELECT COUNT(*)>0 if I was bothered about getting a boolean result).
SELECT COUNT(*) FROM myTable WHERE myValue='x'
Which is all well and good but, in cases where I don't care how many there are, just that there is at least one, is there a more efficient way? A way that would stop a table scan as soon as it found the first value, return true, and not need to scan any more?
I did consider using EXISTS and a subquery:
But an explain suggested that this was making the process more complex, not simpler.
SELECT EXISTS (
SELECT * FROM myTable WHERE myValue='x'
I thought about using a LIMIT 1:
But it looks the same as using a COUNT.
SELECT myValue FROM myTable where myValue='x' LIMIT 1
Is there a more efficient way?
As it happens I've got an index on this column so I'd not be expecting much of a difference (but might do with massive tables), but the question was still bugging me.