CODEWITHSUNDEEP
mysqlcount
Alex
Alex

Reputation: 1309

MySQL SELECT 1 vs SELECT `field_id` AND COUNT 1 vs COUNT (*) or COUNT (`field_id`) Performance wise

I have a very simple question. I want to know if a certain database row exists.

I generally use :

SELECT 1 FROM `my_table` WHERE `field_x` = 'something'

Then I fetch the result with :

$row = self::$QueryObject->fetch();

And check if any results : 

if(isset($row[1]) === true){
    return(true);
}

You can do this also with :

COUNT 1 FROM `my_table` WHERE `field_x` = 'something'

And similar to COUNT * FROMmy_tableandCOUNT field_id FROM `my_table

But I was wondering.. How does this relate to performance?

Are there any cons to using SELECT 1 or COUNT 1?? My feeling says that select INTEGER 1 means the lowest load.

But is this actually true??

Can anyone enlighten me?

Upvotes: 0

Views: 1377

Answers (2)

fancyPants
fancyPants

Reputation: 51888

Actually all your solutions are suboptimal :) What you do with your queries is reading every row there is to be found, even if you add limit. Do it like this:

SELECT EXISTS ( SELECT 1 FROM `my_table` WHERE `field_x` = 'something');

EXISTS returns 1 if something was found, 0 if not. It stops searching as soon as an entry was found. What you select in the subquery doesn't matter, you can even select null.

Also keep in mind, that COUNT(*) or COUNT(1) are very different from COUNT(column_name). COUNT(*) counts every row, while COUNT(column_name) only count the rows that are not null.

Upvotes: 4

Next Developer
Next Developer

Reputation: 1229

If you add the LIMIT 1 to the end of the query then SELECT works better than COUNT especially when you have a large table.

Upvotes: 0

Related Questions