FAQ
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 *'], ....})

This did the right thing database-wise (DBIC_TRACE revealed exactly
the right SQL statement), but the resulting rows' accessors (like id,
name etc) all returned nothing - I ended up with a big table of no
data.

So I changed it to:

? $rs->search({column => 'value'}, {columns => ['SQL_CALC_FOUND_ROWS *'], ...})

But this resulted in an invalid SQL statement since 'me.' had been
prepended to 'SQL_CALC_FOUND_ROWS'.

Even if I had succeeded in getting the first part working, I have no
idea how to go about formulating the statement 'SELECT FOUND_ROWS()'
with DBIx::Class - there's no FROM table, and from what I can tell
DBIx::Class seems to assume there is always a table.

Is there a method for doing both of this statements? Falling back to
using the raw database handle $schema->storage->dbh seems to be a bit
of a shame here....

Thanks,
MDB

Search Discussions

  • Hernan Lopes at Dec 15, 2010 at 1:37 am
    This is how i have done pagination with ease in catalyst:

    1. On the controller
    my $results = $c->model('DB::Person')->search({});
    $c->stash(
    results => $results,
    pager => $result->pager,
    );

    2. create a pagination.tt and include on your views:
    <!-- PAGINATION VIEW BEGIN -->
    [% IF pager
    %]

    <center>
    <div class="pagination-space">
    <div class="span-7 height35 paddingTop5 paddingBottom5 quiet">
    Total <strong class="loud">[% pager.last_page %]</strong> páginas,
    <strong class="loud">[% pager.total_entries %]</strong> itens.
    </div>
    <div class="span-9 height35 paddingTop5 paddingBottom5 last">
    [% IF pager.previous_page %]
    <a href="[% c.req.uri_with( page => pager.first_page ) %]"
    class="noborder">&laquo; Primeira</a>
    <a href="[% c.req.uri_with( page => pager.previous_page ) %]"
    class="noborder">&lt; Anterior </a>
    [% END %]
    [% IF pager.next_page %]
    <a href="[% c.req.uri_with( page => pager.next_page ) %]"
    class="noborder">Próxima &gt; </a>
    <a href="[% c.req.uri_with( page => pager.last_page ) %]"
    class="noborder">Última &raquo; </a>
    [% END %]
    </div>


    <div class="span-16 height35 paddingTop5 paddingBottom5">
    Páginas.
    [%# Página pager.current_page %]

    [%
    start = (pager.current_page - 6) > 0 ?
    (pager.current_page - 6) : 1;
    end = (pager.current_page + 6) < pager.last_page ?
    (pager.current_page + 6) : pager.last_page;
    FOREACH page IN [ start .. end ]
    %]
    [% IF pager.current_page == page %]
    <a class="current"> [% page %] </a>
    [% ELSE %]
    <a href="[% c.req.uri_with( page => page ) %]">[% page %]</a>
    [% END %]
    [% END %]
    </div>

    </div>
    </center>
    [% END %]
    <!-- PAGINATION VIEW END -->

    --hernan
    On Tue, Dec 14, 2010 at 11:21 PM, Matthew Braid wrote:

    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 *'],
    ....})

    This did the right thing database-wise (DBIC_TRACE revealed exactly
    the right SQL statement), but the resulting rows' accessors (like id,
    name etc) all returned nothing - I ended up with a big table of no
    data.

    So I changed it to:

    $rs->search({column => 'value'}, {columns => ['SQL_CALC_FOUND_ROWS *'],
    ...})

    But this resulted in an invalid SQL statement since 'me.' had been
    prepended to 'SQL_CALC_FOUND_ROWS'.

    Even if I had succeeded in getting the first part working, I have no
    idea how to go about formulating the statement 'SELECT FOUND_ROWS()'
    with DBIx::Class - there's no FROM table, and from what I can tell
    DBIx::Class seems to assume there is always a table.

    Is there a method for doing both of this statements? Falling back to
    using the raw database handle $schema->storage->dbh seems to be a bit
    of a shame here....

    Thanks,
    MDB

    _______________________________________________
    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
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101214/5c55affd/attachment.htm
  • Hernan Lopes at Dec 15, 2010 at 2:01 am
    i think i missed the rows and page

    my $results = $c->model('DB::Person')->search({}, {
    rows =>
    10,

    page => $c->req->params->{page}||1,
    });
    On Tue, Dec 14, 2010 at 11:37 PM, Hernan Lopes wrote:

    This is how i have done pagination with ease in catalyst:

    1. On the controller
    my $results = $c->model('DB::Person')->search({});
    $c->stash(
    results => $results,
    pager => $result->pager,
    );

    2. create a pagination.tt and include on your views:
    <!-- PAGINATION VIEW BEGIN -->
    [% IF pager
    %]

    <center>
    <div class="pagination-space">
    <div class="span-7 height35 paddingTop5 paddingBottom5 quiet">
    Total <strong class="loud">[% pager.last_page %]</strong> páginas,
    <strong class="loud">[% pager.total_entries %]</strong> itens.
    </div>
    <div class="span-9 height35 paddingTop5 paddingBottom5 last">
    [% IF pager.previous_page %]
    <a href="[% c.req.uri_with( page => pager.first_page ) %]"
    class="noborder">&laquo; Primeira</a>
    <a href="[% c.req.uri_with( page => pager.previous_page ) %]"
    class="noborder">&lt; Anterior </a>
    [% END %]
    [% IF pager.next_page %]
    <a href="[% c.req.uri_with( page => pager.next_page ) %]"
    class="noborder">Próxima &gt; </a>
    <a href="[% c.req.uri_with( page => pager.last_page ) %]"
    class="noborder">Última &raquo; </a>
    [% END %]
    </div>


    <div class="span-16 height35 paddingTop5 paddingBottom5">
    Páginas.
    [%# Página pager.current_page %]

    [%
    start = (pager.current_page - 6) > 0 ?
    (pager.current_page - 6) : 1;
    end = (pager.current_page + 6) < pager.last_page ?
    (pager.current_page + 6) : pager.last_page;
    FOREACH page IN [ start .. end ]
    %]
    [% IF pager.current_page == page %]
    <a class="current"> [% page %] </a>
    [% ELSE %]
    <a href="[% c.req.uri_with( page => page ) %]">[% page
    %]</a>
    [% END %]
    [% END %]
    </div>

    </div>
    </center>
    [% END %]
    <!-- PAGINATION VIEW END -->

    --hernan

    On Tue, Dec 14, 2010 at 11:21 PM, Matthew Braid wrote:

    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 *'],
    ....})

    This did the right thing database-wise (DBIC_TRACE revealed exactly
    the right SQL statement), but the resulting rows' accessors (like id,
    name etc) all returned nothing - I ended up with a big table of no
    data.

    So I changed it to:

    $rs->search({column => 'value'}, {columns => ['SQL_CALC_FOUND_ROWS *'],
    ...})

    But this resulted in an invalid SQL statement since 'me.' had been
    prepended to 'SQL_CALC_FOUND_ROWS'.

    Even if I had succeeded in getting the first part working, I have no
    idea how to go about formulating the statement 'SELECT FOUND_ROWS()'
    with DBIx::Class - there's no FROM table, and from what I can tell
    DBIx::Class seems to assume there is always a table.

    Is there a method for doing both of this statements? Falling back to
    using the raw database handle $schema->storage->dbh seems to be a bit
    of a shame here....

    Thanks,
    MDB

    _______________________________________________
    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
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101215/246cc8c0/attachment-0001.htm
  • Matthew Braid at Dec 15, 2010 at 2:30 am
    Hi,

    While this is a nifty shortcut for calculating/displaying pagination
    information, it does still result in the use of a count query on top
    of a select query. I'm trying to avoid that entirely (it would be nice
    to still have the same interface though, but I can just use Data::Page
    directly in a pinch).

    MDB
    On Wed, Dec 15, 2010 at 12:01 PM, Hernan Lopes wrote:
    i think i missed the rows and page

    my $results = $c->model('DB::Person')->search({}, {
    ?????? rows =>
    10,
    ??????? page => $c->req->params->{page}||1,
    });
    On Tue, Dec 14, 2010 at 11:37 PM, Hernan Lopes wrote:

    This is how i have done pagination with ease in catalyst:

    1. On the controller
    my $results = $c->model('DB::Person')->search({});
    $c->stash(
    results => $results,
    pager => $result->pager,
    );

    2. create a? pagination.tt and include on your views:
    <!-- PAGINATION VIEW BEGIN -->
    [% IF pager
    %]
    <center>
    ??? <div class="pagination-space">
    ????? <div class="span-7 height35 paddingTop5 paddingBottom5 quiet">
    ??????? Total <strong class="loud">[% pager.last_page %]</strong> p?ginas,
    <strong class="loud">[% pager.total_entries %]</strong> itens.
    ????? </div>
    ????? <div class="span-9 height35 paddingTop5 paddingBottom5 last">
    ?????? [% IF pager.previous_page %]
    ?????????? <a href="[% c.req.uri_with( page => pager.first_page ) %]"
    class="noborder">&laquo; Primeira</a>
    ?????????? <a href="[% c.req.uri_with( page => pager.previous_page ) %]"
    class="noborder">&lt; Anterior </a>
    ?????? [% END %]
    ??????? |
    ?????? [% IF pager.next_page %]
    ?????????? <a href="[% c.req.uri_with( page => pager.next_page ) %]"
    class="noborder">Pr?xima &gt; </a>
    ?????????? <a href="[% c.req.uri_with( page => pager.last_page ) %]"
    class="noborder">?ltima &raquo; </a>
    ?????? [% END %]
    ????? </div>


    ????? <div class="span-16 height35 paddingTop5 paddingBottom5">
    ????? P?ginas.
    [%#??????? P?gina pager.current_page %]

    ?????? [%
    ?????????? start = (pager.current_page - 6) > 0?????????????? ?
    (pager.current_page - 6) : 1;
    ?????????? end?? = (pager.current_page + 6) < pager.last_page ?
    (pager.current_page + 6) : pager.last_page;
    ?????????? FOREACH page IN [ start .. end? ]
    ?????? %]
    ?????????? [% IF pager.current_page == page %]
    ?????????????? <a class="current"> [% page %] </a>
    ?????????? [% ELSE %]
    ?????????????? <a href="[% c.req.uri_with( page => page ) %]">[% page
    %]</a>
    ?????????? [% END %]
    ?????? [% END %]
    ????? </div>

    ?? </div>
    </center>
    [% END %]
    <!-- PAGINATION VIEW END -->

    --hernan

    On Tue, Dec 14, 2010 at 11:21 PM, Matthew Braid <dbixclass@mdb.id.au>
    wrote:
    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 *'],
    ....})

    This did the right thing database-wise (DBIC_TRACE revealed exactly
    the right SQL statement), but the resulting rows' accessors (like id,
    name etc) all returned nothing - I ended up with a big table of no
    data.

    So I changed it to:

    ? $rs->search({column => 'value'}, {columns => ['SQL_CALC_FOUND_ROWS *'],
    ...})

    But this resulted in an invalid SQL statement since 'me.' had been
    prepended to 'SQL_CALC_FOUND_ROWS'.

    Even if I had succeeded in getting the first part working, I have no
    idea how to go about formulating the statement 'SELECT FOUND_ROWS()'
    with DBIx::Class - there's no FROM table, and from what I can tell
    DBIx::Class seems to assume there is always a table.

    Is there a method for doing both of this statements? Falling back to
    using the raw database handle $schema->storage->dbh seems to be a bit
    of a shame here....

    Thanks,
    MDB

    _______________________________________________
    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

    _______________________________________________
    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
  • Emmanuel OTTON at Dec 15, 2010 at 9:50 am

    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
  • Matthew Braid at Dec 15, 2010 at 10:29 am
    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 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/dbix-class@lists.scsys.co.uk
  • Emmanuel OTTON at Dec 15, 2010 at 3:11 pm

    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
  • Octavian Rasnita at Dec 15, 2010 at 3:39 pm
    From: "Emmanuel OTTON" <otton@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
  • Matthew Braid at Dec 15, 2010 at 11:53 pm
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedDec 15, '10 at 1:21a
activeDec 15, '10 at 11:53p
posts9
users4
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2022 Grokbase