CODEWITHSUNDEEP
phpmysqlsqlpdoinnodb
Janine Kroser
Janine Kroser

Reputation: 444

SQL Performance - SELECT COUNT vs SELECT id with LIMIT 1

having a mysql database with innodb and I want to find the fastest way to check whether a row exist.

So is it faster to do:

    $sql5 = "SELECT id, value FROM pc_taxes WHERE id = :taxId AND client_id = :clientID LIMIT 1";
            $stmt5 = getaccessdata::getInstance()->prepare($sql5);
            $stmt5->bindValue(':clientID', $client_id, PDO::PARAM_INT);
            $stmt5->bindValue(':taxId', $taxID, PDO::PARAM_INT);

            try {
                $stmt5->execute();
                $req5 = $stmt5->fetchAll(PDO::FETCH_ASSOC);
                $countTaxVal = 0;
                foreach ($req5 as $r5) {
                    $countTaxVal++;
                }
            } catch (Exception $e) {

            }

if($countTaxVal>0){
$rowExist=true;
}

or is this version faster on very large records:

$sql5 = "SELECT COUNT(*) FROM pc_taxes WHERE id = :taxId AND client_id = :clientID LIMIT 1";
            $stmt5 = getaccessdata::getInstance()->prepare($sql5);
            $stmt5->bindValue(':clientID', $client_id, PDO::PARAM_INT);
            $stmt5->bindValue(':taxId', $taxID, PDO::PARAM_INT);

numRows =0;
            try {
                $stmt5->execute();
                $numRows = $stmt5->fetchColumn();


            } catch (Exception $e) {

            }

if($numRows >0){
$rowExist=true;
}

Thanks for your advices.

Upvotes: 3

Views: 2486

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157892

It doesn't really matter.

Please, do not ask performance-related questions out of nowhere. Instead, bother with solution only if you have a problem at hand.

For the question you asked, the fastest way to check whether a row exist is to have an index for the column(s) involved in the query. This is the right answer and this is everything you need for the task. While particular SQL syntax doesn't matter.

So, for your question you need an index like this id_client(id,client_id)

-- THIS is what makes your query faster.

Out of common sense I would say that if you don't need to count but want just a flag, then select that flag:

$sql = "SELECT 1 FROM pc_taxes WHERE id = ? AND client_id = ? LIMIT 1";
$stmt = getaccessdata::getInstance()->prepare($sql);
$stmt->execute([$client_id,$taxID]);
$rowExist = $stmt->fetchColumn();

And for goodness sake, do not wrap your queries into empty try catch. You will deeply regret that day you started doing so.

Upvotes: 3

Related Questions