Best method for checking if a row exists.

2008-12-12 Thread Nicholas Ring 
Hello,

What is the best method to check if (one or more) row exists (note:
primary key is auto inc and table engine is InnoDB - but what if these
were not true) ?

1) SELECT * FROM table WHERE condition

Check to see if the result set is non-empty.


2) SELECT COUNT(*) AS cnt FROM table WHERE condition

Check to see if the field 'cnt' is non-zero.


3) SELECT primary key FROM table WHERE condition LIMIT 1

Check to see if the result set is non-empty.


4) SELECT 1 FROM table WHERE condition LIMIT 1

Check to see if the result set is non-empty.


Any other possibilities?



I hate (1) and don't mind the rest if PK is auto inc and table engine
is InnoDB but I think (4) would be the best where PK is not auto inc
and/or table engine is not InnoDB.

Comments?

Many thanks in advance,
Nick



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Best method for checking if a row exists.

2008-12-12 Thread Perrin Harkins 
SELECT EXISTS(
  SELECT * FROM table WHERE condition
)

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org