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