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

[Octavian Rasnita]

From: "Emmanuel OTTON" <otton at mines-albi.fr>

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 ?).


It happened to me to have much slower searches when *using* found_rows in some cases, so yes, it is possible.
It wouldn't be bad if there would be a way of choosing the wanted method though.

Something like:

rows => 20,
page => $page,
type_of_counting => 'select_rows',

type_of_counting may have importance only for MySQL so the code might be portable...

Octavian