[Dbix-class] DBIx::Class, mysql and 'SQL_CALC_FOUND_ROWS'/'SELECT FOUND_ROWS()'

[Matthew Braid]

I already know how to get paged results. I already know about the
pager object. Guess how the pager object gets its 'total number of
records' count when using mysql? SELECT COUNT(*)..., even though mysql
has SQL_CALC_FOUND_ROWS and FOUND_ROWS() available, which means
DBIx::Class does NOT use the most efficient means of getting paged
results when it comes to mysql - otherwise it'd already be using
SQL_CALC_FOUND_ROWS under the hood (which it isn't, at least according
to the debug output when DBIC_TRACE is true). Portability is nice, but
I'd like it to _work better_. Purity in this case can take a back seat
to practicality.

>From everything I've seen, DBIx::Class simply does not support the
SQL_CALC_FOUND_ROWS and FOUND_ROWS() options of mysql (even invisibly
under the hood), and thus is a sub-optimal choice when it comes to
pagination of large sets of data.

I'm currently looking at adding functionality to
DBIx::Class::ResultSet and DBIx::Class::Storage::DBI::mysql so that
the 'hit the database' class of methods in DBIx::Class::ResultSet will
accept an attribute of something like 'concurrent_count', and
DBIx::Cass::Storage::DBI::mysql will change it's select query when it
is in effect (and hopefully other storage engines will ignore it).
It's a little fiddly, but I think it's doable. I don't want to add
SQL_CALC_FOUND_ROWS to _all_ select statements under mysql because for
queries with LIMIT that you don't care about the count for it is less
efficient again.

Fingers crossed.

MDB

On Wed, Dec 15, 2010 at 7:50 PM, Emmanuel OTTON <otton at mines-albi.fr> wrote:
> Le 15 déc. 2010 à 02:21, Matthew Braid a écrit :
>
>> Hi all,
>>
>> I need to do pagination for a site I'm building in Catalyst, and my
>> underlying RDMS is mysql. Rather than using count and search, I want
>> to use mysql's SQL_CALC_FOUND_ROWS modifier and the 'SELECT
>> FOUND_ROWS()' statement (since that's a lot faster than count+search).
>>
>> Unfortunately I have no idea how to actually do this with DBIx::Class.
>>
>> My initial guess for the first part was something along the lines of:
>>
>>  $rs->search({column => 'value'}, {select => ['SQL_CALC_FOUND_ROWS *'], ....})
>
> You should NOT use specific mysql dialect, DBIx::Class is built, among other advantages, to bring you portability, that means it includes a good solution for nearly everything you could need, including paginate resultsets.
>
> You just have to add rows and page parameters to the resultset constructor , and your rs will include a pager object.
>
> See the manual, for example this one:
> http://search.cpan.org/~elliott/DBIx-Class-0.06002/lib/DBIx/Class/Manual/Cookbook.pod#Paged_results
>
> If your RDBMS and the DBD::YourRdbms driver support it, DBIx::Class will even do "the right thing", i.e. issue the right SQL statements (for mysql: LIMIT and OFFSET) to fetch only the requested rows only, instead of fetching the whole table and paging through it.
>
> --
> Emmanuel OTTON - Responsable informatique - Ecole des mines d'Albi-Carmaux - Tél: 05 63 49 30 86
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>