Somebody decided that CodeIgniter4's database functionality had no need for a num_rows function like mysqli_num_rows, pg_num_rows, and sqlsrv_num_rows. This dev claims that the presence of such a function causes performance issues without specifying what they might be.

From what I can tell, there are numerous DBMSes that don't offer such a function (e.g., Oracle, SQLite) but this seems like a poor decision to me. I'd be willing to bet that the vast majority of CodeIgniter projects use MySQLi, Postgres, or SQLSRV. Furthermore, it should be almost trivial to expose this function for this DBMS modules that do have such a function and throw an exception for those that don't.

Can anyone tell me if those num_rows functions cause additional communication with the db server or whether they just check an integer in memory? I was under the (perhaps incorrect) impression that a SELECT query in PHP would contact the db server and that all records corresponding to the select query would be copied to PHP memory space. I tried looking up the source code for mysqli_num_rows and did manage to pick thru the macro definitions to this spot in the code, but I can't really figure out what it's going. Looks to me like it might be accessing a struct -- a very quick operatio -- but my C/C++ knowledge has me stumped here.

    Hmm, per https://php.net/mysqli_num_rows :

    The behaviour of mysqli_num_rows() depends on whether buffered or unbuffered result sets are being used. For unbuffered result sets, mysqli_num_rows() will not return the correct number of rows until all the rows in the result have been retrieved.

    So if they're using unbuffered result sets, I guess you might have to do a separate query? 🤷‍♂️

    Oh, and if they're using PDO, per https://www.php.net/manual/en/pdostatement.rowcount.php :

    If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

    So I guess it's kind of messy if you're trying to support multiple DBMS's? (I've been solely in a PostgreSQL world for the past 9 years now, so haven't had to worry about it, as PDOStatement::rowCount() has always worked for me in that context.)

    PS: To take a stab at answering the actual subject question ( 😉 ), I don't think that function would make another DB query. The main issue would instead be whether you can count on it being correct without having to do your own separate select count(*) ... sort of query to be sure you have what you need (or else either doing a fetch-all or counting how many row fetches you do to get the total after the fact).

    NogDog Thanks for the very helpful details here. I was dimly aware of possible issues with record counts and buffering, but you have provided a lot of important detail here. Surely a DBMS response would provide some integer in memory to tell you how many records you can iterate through without incrementing some internal array counter? As I mentioned in my OP, my C knowledge isn't quite sharp enough to be sure, but that code I linked does appear to be using some kind of struct or something to just quickly peak at a memory location. I've checked my PHP code and it looks like I do rely on this numRows/rowCount functionality to occasionally report some record counts, but it is most often used just to check if any records were returned at all.

      I got lost in the maze of twisty little macros as well; one might have to experiment while monitoring the dbms to see if an additional query does get made. The hazard I see is that if an additional query is involved then if there's no transaction isolation someone could move your cheese between the two queries.

      sneakyimp most often used just to check if any records were returned at all.

      That could be worked around of course by just having a flag that's cleared before iteration and set within the loop. If it's still clear after the loop has executed then there weren't any records. The downside is that it does tie the "check if there are results" task to the "iterate over the results" task.

      Um. I'm twangered. HNY

        Write a Reply...