[Phpmyadmin-devel] Innodb and row exact count

[Marc Delisle]

Le 2013-07-27 07:25, Madhura Jayaratne a écrit :
>
>
>
> On Sat, Jul 27, 2013 at 4:36 PM, Marc Delisle <marc at infomarc.info
> <mailto:marc at infomarc.info>> wrote:
>
>     Le 2013-07-27 07:04, Marc Delisle a écrit :
>      > Le 2013-07-27 07:02, Madhura Jayaratne a écrit :
>      >>
>      >>
>      >>
>      >> On Thu, Jul 25, 2013 at 1:39 AM, Marc Delisle
>     <marc at infomarc.info <mailto:marc at infomarc.info>
>      >> <mailto:marc at infomarc.info <mailto:marc at infomarc.info>>> wrote:
>      >>
>      >>      Le 2013-06-12 17:04, Marc Delisle a écrit :
>      >>       > Le 2013-06-12 03:23, Michal Čihař a écrit :
>      >>       >> Hi
>      >>       >>
>      >>       >> Dne Tue, 11 Jun 2013 13:20:44 -0400
>      >>       >> Marc Delisle <marc at infomarc.info
>     <mailto:marc at infomarc.info> <mailto:marc at infomarc.info
>     <mailto:marc at infomarc.info>>>
>      >>      napsal(a):
>      >>       >>
>      >>       >>> I don't find a winning solution so I'm seeking for
>     advice. In
>      >>      this bug
>      >>       >>> report [0], we see that it's annoying to have
>     incorrect page
>      >>      navigation
>      >>       >>> (page selector and arrows) for InnoDB.
>      >>       >>>
>      >>       >>> However, when I apply my proposed patch (which forces
>     an exact
>      >>      count in
>      >>       >>> sql.php), I see delays for larger InnoDB tables.
>      >>       >>>
>      >>       >>> For example, on MySQL 5.5.31, the first time I browse
>     a table
>      >>      having one
>      >>       >>> million rows, I wait for 9 seconds. I tried with 0.5
>     million
>      >>      and it was
>      >>       >>> about 5 seconds. A few minutes afterwards, even after
>     closing the
>      >>       >>> browser, my big table starts to display in two seconds.
>      >>       >>>
>      >>       >>> I tend to find these delays acceptable and I propose
>     to apply
>      >>      my patch.
>      >>       >>
>      >>       >> I agree this is acceptable and agree with the patch for
>     4.0.4.
>      >>       >>
>      >>       >> For 4.1 I'd go even further - drop MaxExactCount and
>     use only
>      >>      not exact
>      >>       >> counts on overview pages and count properly when browsing.
>      >>       >
>      >>       > Thanks, I'll have a look at this suggestion for 4.1.
>      >>       >
>      >>       >
>      >>
>      >>      Hmmm, I think we have a problem with exact counting, see
>      >> https://sourceforge.net/p/phpmyadmin/bugs/4027/
>      >>
>      >>      It's true that 750 M rows is a lot of rows...
>      >>
>      >> Would it make things too slow to get the rough count first and
>     then get
>      >> the exact count if the rough counts is less than some defined
>     threshold?
>
>     (The title of bug #4027 is "$cfg['MaxExactCount'] is ignored when
>     BROWSING is back")
>      >
>      > That's exactly the goal of the MaxExactCount configuration directive,
>      > and I think it could be applied here as well.
>      >
>      > Would not be too slow, as the rough count is very quick.
>      >
>
> My bad.
>
> So I think it's best to use $cfg['MaxExactCount'] in browsing as well.
> However IMO, its default value being 0 would break its purpose.

Not necessarily. You see, the guy reporting bug #4027 was aware of this 
directive and can set it to an appropriate value.

In version 3.0.0 we had 20000 as a default value, not sure why we set it 
to 0 later.

-- 
Marc Delisle
http://infomarc.info