Aside from the incredibly annoying fact that InnoDB tables don't store a
total COUNT(), my question is... Why are these numbers different? I could
easily parse out the second query which is REDICULOUSLY faster. BTW, why
doesn't mySQL just 'alias' the first query behind the scenes for us and
parse out the count?

mysql> SELECT COUNT(rsm_report_id) FROM Swordfish.rsm_reports;
+----------------------+
| COUNT(rsm_report_id) |
+----------------------+
| 277456 |
+----------------------+
1 row in set (24.84 sec)

mysql> show table status where Name = 'rsm_reports'\G
*************************** 1. row ***************************
Name: rsm_reports
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 296100 <<----- why doesn't this match above?!
Avg_row_length: 3204
Data_length: 948961280
Max_data_length: 0
Index_length: 13664256
Data_free: 0
Auto_increment: 277457 <<----- this can't be counted on
Create_time: 2007-03-26 20:23:14
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 5120 kB
1 row in set (0.27 sec)

In the last episode (Mar 26), Daevid Vincent said:
> Aside from the incredibly annoying fact that InnoDB tables don't store a
> total COUNT(), my question is... Why are these numbers different? I could
> easily parse out the second query which is REDICULOUSLY faster. BTW, why
> doesn't mySQL just 'alias' the first query behind the scenes for us and
> parse out the count?

SHOW TABLE STATUS simply returns a guess based on some index dives on
InnoDB tables, and will actually change every time you run the command
as it randomly picks different parts of the index to examine.

http://dev.mysql.com/doc/refman/5.0/...le-status.html :

Rows

The number of rows. Some storage engines, such as MyISAM, store the
exact count. For other storage engines, such as InnoDB, this value
is an approximation, and may vary from the actual value by as much
as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an
accurate count.

--
Dan Nelson
dnelson@allantgroup.com

> In the last episode (Mar 26), Daevid Vincent said:
> > Aside from the incredibly annoying fact that InnoDB tables
> don't store a
> > total COUNT(), my question is... Why are these numbers
> different? I could
> > easily parse out the second query which is REDICULOUSLY
> faster. BTW, why
> > doesn't mySQL just 'alias' the first query behind the
> scenes for us and
> > parse out the count?
>
> SHOW TABLE STATUS simply returns a guess based on some index dives on
> InnoDB tables, and will actually change every time you run the command
> as it randomly picks different parts of the index to examine.
>
> http://dev.mysql.com/doc/refman/5.0/...le-status.html :
>
> Rows
>
> The number of rows. Some storage engines, such as MyISAM,
> store the
> exact count. For other storage engines, such as InnoDB, this value
> is an approximation, and may vary from the actual value by as much
> as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an
> accurate count.

Thanks Dan.

UGH. This is useless.

WHY doesn't COUNT(*) return fast like MYISM tables do?
This seems like such a glaring oversight when designing INNODB tables.

I don't use transactions. I could give a shit about transactions. I only use
INNODB for Foreign Keys. Can't there be some setting in my.cnf for users
like us?

Is mySQL planning on fixing this BUG. YES -- it is a BUG. A BIG FAT HARRY
ONE.
It's completely stupid that I can't query and get an accurate total of
records in a table in under a second.

A deviation of 40-50% is SIGNIFICANT!
You might as well just use RAND() at that point.

Oh, and BTW, my company DOES pay mySQL for a support contract of several
thousand dollars per year. So spare me the 'free software' speeches...

This is absolutely baffling and astonishing how a company so seemingly smart
could make such an incredible blunder. :-\

In the last episode (Mar 26), Daevid Vincent said:
> > In the last episode (Mar 26), Daevid Vincent said:
> > > Aside from the incredibly annoying fact that InnoDB tables don't
> > > store a total COUNT(), my question is... Why are these numbers
> > > different? I could easily parse out the second query which is
> > > REDICULOUSLY faster. BTW, why doesn't mySQL just 'alias' the
> > > first query behind the scenes for us and parse out the count?
> >
> > SHOW TABLE STATUS simply returns a guess based on some index dives
> > on InnoDB tables, and will actually change every time you run the
> > command as it randomly picks different parts of the index to
> > examine.
>
> Thanks Dan.
>
> UGH. This is useless.
>
> WHY doesn't COUNT(*) return fast like MYISM tables do?
> This seems like such a glaring oversight when designing INNODB tables.

You're about 5 years too late for this converation, but I recall it
having to do with the fact that when you're on a table that supports
transactions, you don't know exactly how many records a particular
session has available to it unless you actually go and count them.
Depending on your settings, you may or may not see rows inserted by
other uncommitted sessions, and they may disappear if the other
sessions roll their transactions back.

> I don't use transactions. I could give a shit about transactions. I
> only use INNODB for Foreign Keys. Can't there be some setting in
> my.cnf for users like us?
>
> Is mySQL planning on fixing this BUG. YES -- it is a BUG. A BIG FAT
> HARRY ONE. It's completely stupid that I can't query and get an
> accurate total of records in a table in under a second.
>
> A deviation of 40-50% is SIGNIFICANT!
> You might as well just use RAND() at that point.
>
> Oh, and BTW, my company DOES pay mySQL for a support contract of
> several thousand dollars per year. So spare me the 'free software'
> speeches...

You should probably be filing bug reports or calling your support
number then, not sending emails to the free-support mailing list.
Maybe ask if they could implement foreign keys for MyISAM instead of
asking for Innodb to do something it may not be able to do.

> This is absolutely baffling and astonishing how a company so
> seemingly smart could make such an incredible blunder. :-\

Let us know if you find another database product that supports instant
count(*)'s on transactioned tables.

--
Dan Nelson
dnelson@allantgroup.com

Daevid Vincent wrote:

> Is mySQL planning on fixing this BUG. YES -- it is a BUG. A BIG FAT HARRY
> ONE.
>

I think you mean 'hairy', not 'harry'. There are no 'harry' bugs, apart
from that British fool who's in line for the throne.

> It's completely stupid that I can't query and get an accurate total of
> records in a table in under a second.
>

If it's so important to you, why not maintain your OWN count. Cache the
figure somewhere, or update a figure when you insert a record.

> A deviation of 40-50% is SIGNIFICANT!
> You might as well just use RAND() at that point.
>

Fine. Use rand() if it makes you happy.

> This is absolutely baffling and astonishing how a company so seemingly smart
> could make such an incredible blunder. :-\
>

Oh the melodrama! Won't someone please think of the children!

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

> > Is mySQL planning on fixing this BUG. YES -- it is a BUG. A
> BIG FAT HARRY
> > ONE.
> >
>
> I think you mean 'hairy', not 'harry'. There are no 'harry'
> bugs, apart

LOL! Doh! Yeah. I was so blinded by rage that I forgot my spelling.

> > It's completely stupid that I can't query and get an
> accurate total of
> > records in a table in under a second.
> >
>
> If it's so important to you, why not maintain your OWN count.
> Cache the
> figure somewhere, or update a figure when you insert a record.

Because we're a huge enterprise product, with 3 databases of > 200 tables
each.

We are migrating from MYISM to INNODB and keeping track of that value isn't
something we thought we'd need to do. Plus it seems like something we
_shouldn't_ have to do. That's WHY we use a database.

> > A deviation of 40-50% is SIGNIFICANT!
> > You might as well just use RAND() at that point.
>
> Fine. Use rand() if it makes you happy.

Sarcasm. Cool.

> > This is absolutely baffling and astonishing how a company
> so seemingly smart
> > could make such an incredible blunder. :-\
> >
>
> Oh the melodrama! Won't someone please think of the children!

....and the homeless
....and Katrina victims
....and Tsunami in India victims

;-p

> You're about 5 years too late for this converation, but I recall it

Really? People have just happily accepted this absurd limitation for _five_
years? Wow.

> having to do with the fact that when you're on a table that supports
> transactions, you don't know exactly how many records a particular
> session has available to it unless you actually go and count them.
> Depending on your settings, you may or may not see rows inserted by
> other uncommitted sessions, and they may disappear if the other
> sessions roll their transactions back.

You know how many are *IN* the table on the disk at that particular moment.
That's all that needs to be shown!?
So if someone isn't using transactions, then that number will be accurate.
This isn't rocket science.

> You should probably be filing bug reports or calling your support

Oh. I will. ;-)

> Let us know if you find another database product that supports instant
> count(*)'s on transactioned tables.

I don't care what other RDMS are or are not doing.
I care what the one I'm paying for is not doing.

On Mon, March 26, 2007 16:21, Daevid Vincent said:
>> You're about 5 years too late for this converation, but I recall it
>
> Really? People have just happily accepted this absurd limitation for
> _five_
> years? Wow.
>
>> having to do with the fact that when you're on a table that supports
>> transactions, you don't know exactly how many records a particular
>> session has available to it unless you actually go and count them.
>> Depending on your settings, you may or may not see rows inserted by
>> other uncommitted sessions, and they may disappear if the other
>> sessions roll their transactions back.
>
> You know how many are *IN* the table on the disk at that particular
> moment.
Why would they be on the disk. Until the transaction is committed and the
caches are flushed the info. is really in memory I thought.
> That's all that needs to be shown!?
> So if someone isn't using transactions, then that number will be accurate.
> This isn't rocket science.
>
>> You should probably be filing bug reports or calling your support
>
> Oh. I will. ;-)
>
>> Let us know if you find another database product that supports instant
>> count(*)'s on transactioned tables.
>
> I don't care what other RDMS are or are not doing.
> I care what the one I'm paying for is not doing.
>
If you want to bypass the uncertainties built into transaction tables and
get a count that is 'accurate', how about locking the tables then issuing
the count request. I realize this sort of defeats the purpose of
transaction tables but ...

Hi Daevid,

Ugh. How about not going berserk on the public mailing list?

We can understand that you're upset that you didn't read the manual
before starting a MyISAM to InnoDB conversion. You didn't do your
research and now you're being hit by a very simple (and not really all
that unexpected) difference between storage engines.

>> You're about 5 years too late for this converation, but I recall it
>
> Really? People have just happily accepted this absurd limitation for _five_
> years? Wow.

Yes. And it's not likely to change for a long time, either.

>> having to do with the fact that when you're on a table that supports
>> transactions, you don't know exactly how many records a particular
>> session has available to it unless you actually go and count them.
>> Depending on your settings, you may or may not see rows inserted by
>> other uncommitted sessions, and they may disappear if the other
>> sessions roll their transactions back.
>
> You know how many are *IN* the table on the disk at that particular moment.
> That's all that needs to be shown!?
> So if someone isn't using transactions, then that number will be accurate.
> This isn't rocket science.

This actually has a lot less to do with transactions, and more to do
with multiversioning. The number of rows can and will be different
within different sessions and there is no trivial way to keep a simple
count up to date efficiently.

And, if you are using a transactional storage engine, there is no such
thing as "not using transactions". Even if you don't use BEGIN/COMMIT
there are still implicit transactions for each statement. That's the
whole point.

Regards,

Jeremy

--
high performance mysql consulting
www.provenscaling.com

In news:BRAKA1DpZd8CRDqasx600000b4e@mx1.lockdownnetwo rks.com,
"Daevid Vincent" <daevid@daevid.com> wrote:

> Because we're a huge enterprise product, with 3 databases of > 200
> tables each.
>
> We are migrating from MYISM to INNODB and keeping track of that value
> isn't something we thought we'd need to do. Plus it seems like
> something we _shouldn't_ have to do. That's WHY we use a database.

Have you done any research regarding the effects of changes you are making?
For example, how those two engines differ one from another, what the
strenghts and weaknesses of either engine? I think not. If you did, you
wouldn't be surprised by the easiest to spot difference in behaviour between
MyISAM and InnoDB (and there are plenty more). RDBMS is not an Office
spreadsheet, you cannot simply change software as from OpenOffice Calc to MS
Excel and expect everything will work the same. The migration can be done
with positive effects, but require knowledge of both products.

Maciek