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 <[email protected]> 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/[email protected] > _______________________________________________ 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/[email protected]
