Most efficient method of checking for existance of record

schwim

Hi there everyone,

After searching online, I found what was explained as the most efficient method of searching for the existence of a record. This is what I'm currently using:

$query = "SELECT * FROM addon_guild_achievements WHERE id = '$aid' LIMIT 1";
$result = mysql_query ($query) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query);
$result_rows = mysql_num_rows($result);

Does this look like a decent method of handling this? The information provided at the link I was reading showed this as being faster than both EXISTS and COUNT. Your thoughts on the matter would be greatly appreciated.

laserlight

What article were you reading?

This would likely be faster than a COUNT unless the row count is being cached, or if there is no more than one row to count. As to whether it is faster than an EXISTS: I suggest that you verify by measurement if you really do need to optimise.

schwim

This was what I was reading: http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table

The comment made by Laurent Wartel with results was my inspiration.

I'm only trying to find a better way to do something like this, as until I read his comment, I wasn't limiting the search to one on a search for a unique record. I thought that must be very inefficient, which made me wonder if I was doing some other things that were just as bad when doing something as simple as checking for the existence of a record.

laserlight

schwim wrote:
The comment made by Laurent Wartel with results was my inspiration.

Ah, but didn't you see that that comment does not state that the method that you use now is the most efficient? Rather, it gave the results of some tests with various possibilities on a particular database engine with a particular configuration on a particular system, and the results were that in one case SELECT * with LIMIT 1 was fastest and in another case SELECT EXISTS (SELECT 1 ... LIMIT 1) was fastest.

Personally, I think the use of EXISTS makes the most sense when you just want to know if such a row exists. Hence, I suggest that you go for the SELECT EXISTS (SELECT 1 ... LIMIT 1) version, unless you find that that is too slow for some reason, in which case you should measure to find out which is the most efficient.

Weedpacket

I'd expect most query planners to take into account the fact "SELECT EXISTS (SELECT 1 WHERE ...)" would return true as soon as any record matching "..." was found, and wouldn't bother continuing the search. Also, that by using SELECT 1 (and not mentioning a table), it wouldn't need to look at any column values except for those involved in the search criteria.

For example,

PostgreSQL documentation wrote:
The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion. ... Since the result depends only on whether any rows are returned, and not on the contents of those rows, the output list of the subquery is normally unimportant. A common coding convention is to write all EXISTS tests in the form EXISTS(SELECT 1 WHERE ...).

Of course, all this depends on the dbms and how its planner works, and how it uses what indexes it has to guide it through the search criteria, hence the need for measurement.