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

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 3

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.