Mysql Check Exists

mysql

I have made some researches on this subject recently. The way to implement it has to be different if the field is a TEXT field, a non unique field.

I have made some tests with a TEXT field. Considering the fact that we have a table with 1M entries. 37 entries are equal to 'something':

SELECT * FROM test WHERE texte LIKE '%something%' LIMIT 1 with mysql_num_rows() : 0.039061069488525s. (FASTER)
SELECT count(*) as count FROM test WHERE text LIKE '%something% : 16.028197050095s.
SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%') : 0.87045907974243s.
SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%'
LIMIT 1) : 0.044898986816406s.
But now, with a BIGINT PK field, only one entry is equal to '321321' :

SELECT * FROM test2 WHERE id ='321321' LIMIT 1 with mysql_num_rows() : 0.0089840888977051s.
SELECT count(*) as count FROM test2 WHERE id ='321321' : 0.00033879280090332s.
SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321') : 0.00023889541625977s.
SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1) : 0.00020313262939453s. (FASTER)