CODEWITHSUNDEEP
mysqldatabaselimit
TJ-
TJ-

Reputation: 14363

Return just the first result of mysql Query

In one of my applications, I have to determine if 'foo_id' is present in the 'foo' column of a table 'foo_table'.

My requirements will be met as soon as I know there is one such 'foo_id' present. Now, my question is how to restrict this to the first match.

When I checked these queries against mySql EXPLAIN:

- select count(foo) from foo_table where foo=<foo_id>;
- select foo from foo_table where foo=<foo_id> limit 1;

In both of the cases, the number of rows was 58. Is there a way in mySql such that I can restrict the number of rows to 1 (the first match), such that rows are not touched unnecessarily (because I don't need that).

Upvotes: 10

Views: 20800

Answers (4)

anubhava
anubhava

Reputation: 785196

Following explain MySQL statement shows total # of rows instead of what you specify in Limit:

explain select foo from foo_table where foo=<foo_id> limit 1

OUTPUT

+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | foo_table   | ALL  | NULL          | NULL | NULL    | NULL |    58 |       | 
+----+-------------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

Upvotes: 0

rkosegi
rkosegi

Reputation: 14658

explain plan will always show 58, because is number of records which match you criteria.

However LIMIT 1 is all you need.

Upvotes: 13

oezi
oezi

Reputation: 51807

LIMIT 1 is the way to go, so you already found the solution yourself:

SELECT 1 FROM foo_table WHERE foo = ? LIMIT 1

Upvotes: 0

Cameron S
Cameron S

Reputation: 2301

You can use EXISTS as such:

SELECT EXISTS(SELECT 1 FROM foo_table WHERE foo=<foo_id>).

The SELECT is ignored as EXISTS only checks the WHERE clause. As it is good practice to avoid using *, it is substituted by 1 here.

Here is the Documentation.

Upvotes: 2

Related Questions