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

[Matthew Braid]

Oh, it's certainly not for all queries - for simple "paginate through
the entire table based on nothing/this key/this index etc" the
select/count method works way better (especially if using the myisam
table type, which keeps a record of how many rows it has as a seperate
field - count(*) without a where is O(1)). But I'm looking at more
complex searches on a very large (1BN+ rows) innodb table. I sorely
wish I didn't have to, but thems the breaks :)

On Thu, Dec 16, 2010 at 1:11 AM, Emmanuel OTTON <otton at mines-albi.fr> wrote:
> 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
>
>
> _______________________________________________
> 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
>