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

[Emmanuel OTTON]

Le 15 déc. 2010 à 11:29, Matthew Braid a écrit :

> 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

Sorry, made a fool of myself by answering too fast without fully understanding your question.

Just two cents more, for what it's worth: depending on the complexity of the query, some users measured faster response time with the double (select + select count) method than with the found_rows (which may be the reason why DBIx::Class makers, in all their wisdom, chose to use this method ?).

But I assume you measured the efficiency of the two methods on your particular queries, may I ask if you saw an important difference, and on what kind of queries (complexity, number of rows counted/returned,..) ?
--
Emmanuel OTTON - Responsable informatique - Ecole des mines d'Albi-Carmaux - Tél: 05 63 49 30 86