Optimized List View Count in Gyroscope Codegen November 22, 2023
There was a time when the List View uses a lazy approach for pagination. This query returns all the records:
$query="select car_id, car_name from cars ";
By setting a per-page limit and accepting the Page # from user input, we can leaf through all the records. For example:
$query="select car_id, car_name from cars limit $page, $perpage";
However, the calculation of the total number of pages requires a record count. Since we already have the record fetching query, let's just run it twice:
$rs=sql_prep($query, $db);
$count=sql_affected_rows($db, $rs);
This is quite inefficient. But we should avoid duplicating the query code. If, for example, the counting query is modified to bear additional filters but the same filters are not diligently applied to the record fetching query, then the results would be inconsistent.
Gyroscope's CodeGen treats the stem of the record fetching query as a nested black box:
$cquery="select count(*) as c from ($query)t";
$rs=sql_prep($cquery,$db);
$myrow=sql_fetch_assoc($rs);
$count=$myrow['c'];
The above method works slightly better, but the query expands to this:
select count(*) from (select car_id, car_name from cars)t
MySQL is not smart enough to know that the fields in the subquery are later discarded. Getting these fields incurs extra performance cost, especially if the query fetches unindexed fields.
The new CodeGen prepares a base query template:
$sel="car_id, car_name";
$basequery="select %%sel%% from cars";
Then the count query rewrites %%sel%% to "count(*) as c"; and the record fetching query rewrites %%sel%% to the content of the $sel variable.
