I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this:
SELECT COUNT(*) AS total FROM table1 WHERE ...
and check to see if the total is non-zero or is it better to do a query like this:
SELECT * FROM table1 WHERE ... LIMIT 1
and check to see if any rows were returned?
In both queries, the WHERE clause uses an index.
A COUNT query is faster, although maybe not noticeably, but as far as getting the desired result, both should be sufficient.
You could also try using
SELECT EXISTS(SELECT * FROM table1 WHERE ...)
per the documenation
Per a comment below:
SELECT EXISTS(SELECT 1 FROM table1 WHERE ...)
COUNT(*) are optimized in MySQL, so the former query is likely to be faster, generally speaking.
For non-InnoDB tables you could also use the information schema tables:
I'd go with COUNT(1). It is faster than COUNT(*) because COUNT(*) tests to see if at least one column in that row is != NULL. You don't need that, especially because you already have a condition in place (the WHERE clause). COUNT(1) instead tests the validity of 1, which is always valid and takes a lot less time to test.
SELECT COUNT(*) is most likely faster. When you do SELECT COUNT(*), the database engine must send you only one number, possibly over network.
When you do SELECT *, the database engine must send you the whole row, which is significantly faster. Also in this case the database engine must also fetch more data from its memory and/or disk.
However, to get exact answer, you should measure how long it takes using each approach.
Ya know, we're sort of optimizing past a point where it's relevant here. If you're trying to learn, then by all means continue on...the more you know the better. If you are trying to write an application, though, you're much better off spending your time trying to grow your app to the point that it needs this sort of optimization than you are trying to optimize prematurely.
If you are storing BLOBs etc. in your table then getting count(*)'s performance can be really bad.