What's the most efficient way to check the presence of a row in a table?

Question

Say I want to check if a record in a MySQL table exists. I'd run a query, check the number of rows returned. If 0 rows do this, otherwise do that.

SELECT * FROM table WHERE id=5
SELECT id FROM table WHERE id=5

Is there any difference at all between these two queries? Is effort spent in returning every column, or is effort spent in filtering out the columns we don't care about?

SELECT COUNT(*) FROM table WHERE id=5

Is a whole new question. Would the server grab all the values and then count the values (harder than usual), or would it not bother grabbing anything and just increment a variable each time it finds a match (easier than usual)?

I think I'm making a lot of false assumptions about how MySQL works, but that's the meat of the question! Where am I wrong? Educate me, Stack Overflow!

Answer 1

Optimizers are pretty smart (generally). They typically only grab what they need so I'd go with:

SELECT COUNT(1) FROM mytable WHERE id = 5

Answer 2

Limit your results to at most one row by appending LIMIT 1, if all you want to do is check the presence of a record.

SELECT id FROM table WHERE id=5 LIMIT 1

This will definitely ensure that no more than one row is returned or processed. In my experience, LIMIT 1 (or TOP 1 depending in the DB) to check for existence of a row makes a big difference in terms of performance for large tables.

EDIT: I think I misread your question, but I'll leave my answer here anyway if it's of any help.

Answer 3

For the first two queries, most people will generally say, always specify exactly what you need and leave the rest. Effort isn't all specific as bandwidth could be spent in returning data that you aren't even going to do anything with.

As for the previous answer will do for your result set, unless you're dealing with a language that supports affected rows. This can sometimes work when getting data to collect information on how many rows were returned in the last query. You'll need to look at your interface documentation as to how to get that information.

Answer 4

I would think this

SELECT null FROM table WHERE id = 5 LIMIT 1;

would be faster than this

SELECT 1 FROM table WHERE id = 5 LIMIT 1;

but the timer says the winner is "SELECT 1".

Answer 5

The most explicit way would be

SELECT WHEN EXISTS (SELECT 1 FROM table WHERE id = 5) THEN 1 ELSE 0 END

If there is an index on (or starting with) id, it will only search, with maximum efficiency, for the first entry in the index it can find with that value. It won't read the record.

If you SELECT COUNT(*) (or COUNT anything else) it will, under the same circumstances, count the index entries, but not read the records.

If you SELECT *, it will read all the records.

Answer 6

The difference between your 3 queries depends on how you've built your index. Only returning the primary key is likely to be faster as MySQL will have your index in memory, and not have to hit disk. Adding the LIMIT 1 is also a good trick that will speed up the optimizer significantly in early 5.0.x branches and earlier.

try EXPLAIN SELECT id FROM table WHERE id=5 and check the Extras column for the presence of USING INDEX. If its there, then you're query is coming straight from the index, and is going to be much faster.