PHP mysqli query to check if a row exist [duplicat

问题:

This question already has an answer here:

• check if row exists with mysql 3 answers

I have a mysql table, I want to check if a row exists where columnA=$a and $columnB=$b. I dont need to select anything from there. What should be efficient query for that? Currently Im doing like,

if ($stmt = mysqli->prepare("SELECT * FROM TABLE WHERE columnA=? && columnB= ? LIMIT 1")) {
    $stmt->bind_param("ss", $a, $b);
    $stmt->execute();
    $stmt->store_result();
    $count=$stmt->num_rows;
    $stmt->close();
    }
    return ($count > 0 ? true : false);

回答1:

Try with this:

if ($stmt = $mysqli->prepare("SELECT COUNT(*) FROM TABLE WHERE columnA=? && columnB=?")) {
    $stmt->bind_param("ss", $a, $b);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();
}

return ($count > 0 ? true : false);

Now you should be able to get it done

回答2:

That is efficient query if you want to improve that query you should select one column only to speed up the query.

if ($stmt = mysqli->prepare("SELECT id FROM TABLE WHERE columnA=? && columnB= ? LIMIT 1")) {

回答3:

Select just one field from the row instead of all of them (*). Even though you're not using them, mysql still has to fetch them from storage and get them ready for transmission.

SELECT columnA FROM table WHERE ...

Otherwise there's not much else you can do to improve things, other than putting indexes on columnA and columnB to make the searching go faster.