Efficient use of mysqli_result::$num_rows

I frequently see this pattern in legacy applications using mysqli:

$result = $mysqli->query("
    SELECT *
    FROM table_name
    WHERE conditions = 'whatever'
");
if ($result && $result->num_rows > 0) {
    return;
}

The developer’s intent here is to see if there are any rows at all in the database that match a certain condition. He does so by issuing a query, then asking the result object how many rows it has. The developer doesn’t actually want any data from the result, and doesn’t care about the actual row-count itself; this is just a check to see if at least one row exists in the database.

This is a poor conservation of resources. The database does the work needed to select all the columns for all the rows matching the conditions, allocates memory for them, and returns them. But the developer discards all that immediately.

To accomplish the same end, it is less resource-intensive and just as effective to query for a single column and limit the results to a single row:

$result = $mysqli->query("
    SELECT col_name
    FROM table_name
    WHERE conditions = 'whatever'
    LIMIT 1
");
if ($result && $result->num_rows > 0) {
    return;
}

Now the database only does the work needed for a single column and a single row.

(As a side note, I find it interesting that I have not seen this pattern at all in projects using PDO. I’m not sure why this would be. Perhaps there is some originating example code for mysqli somewhere that has gained a life of its own through copying and reuse.)

UPDATE: Perhaps a better way to conserve resources, courtesy of Reddit user marcjschmidt, is to use a COUNT() in the query, then fetch the count of rows, something more like this …

$result = $mysqli->query("
    SELECT COUNT(*)
    FROM table_name
    WHERE conditions = 'whatever'
");
if ($result && $result->fetch_array()[0] > 0) {
    return;
}

… thereby avoiding the use of mysqli_result::$num_rows completely.

UPDATE 2: Lots of commentary in the above Reddit thread. To summarize this blog post: selecting all columns of all rows, then examining $num_rows, and then discarding the result set, is a terrible way of determining if there are any matching rows at all in the database. It is trivially easy to something much better, whether by using a LIMIT 1 and $num_rows, or some form of COUNT(), or perhaps some other approach.

Are you stuck with a legacy PHP application? You might like my book because it gives you a step-by-step guide to improving your codebase, all while keeping it running the whole time.
Share This!Share on Google+Share on FacebookTweet about this on TwitterShare on RedditShare on LinkedIn

6 thoughts on “Efficient use of mysqli_result::$num_rows

  1. The count is a bad idea. For calculating the count the database engine has to calculate all rows matching the expression. With the limit it can stop after the first row (as long as there’s no ORDER BY clause which might need more calculation. Even less to fetch might be by doing SELECT 1 FROM t WHERE …. LIMIT 1, then looking at the index certainly is enough.

  2. I do agree with Johannes. Either choose SELECT 1 FROM t WHERE … LIMIT 1.
    If you have an SQL engine that does not have LIMIT construct (For example Oracle), you can perform queries with the actual answer you need: SELECT EXISTS(SELECT 1 FROM t WHERE …)

  3. `SELECT 1 FROM … LIMIT 1` from the comment above should be the fastest, because it combines using just the index with stopping after first match. Keep in mind that using `COUNT(*)` is not totally semantically correct either, because you don’t care how many results are there, just that there’s at least one. Using COUNT(*) still causes a full index scan.

    This all obviously assuming the WHERE only hits indexes, which is not a given considering the quality of the code in the original example.

  4. Its better your solution with an addition. Use a column that have an index.
    To probe this we can use EXPLAIN

Leave a Reply

Your email address will not be published. Required fields are marked *