Re: Why doesn't the InnoDB count() match table status?
On 3/27/07, Tim Lucia wrote: > -Original Message- > From: Maciej Dobrzanski > Sent: Tuesday, March 27, 2007 6:46 AM > To: mysql@lists.mysql.com > Subject: Re: Why doesn't the InnoDB count() match table status? > > 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 You can and ought to be able to expect it. After all, SQL is a standard. But the problem is not with the result of the query, but with the speed. And the speed of an implementation is not standardized by the ISO/IEC. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't the InnoDB count() match table status?
> -Original Message- > From: Maciej Dobrzanski [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 27, 2007 6:46 AM > To: mysql@lists.mysql.com > Subject: Re: Why doesn't the InnoDB count() match table status? > > 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 You can and ought to be able to expect it. After all, SQL is a standard. It's a crying shame that so many 'feature' creep in for seemingly the sole purpose of hooking you on that vendor's platform. Of course you'd be naïve to believe it, but nothing is wrong with the expectation. IMO, of course Tim > > Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
In news:[EMAIL PROTECTED], "Daevid Vincent" <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't the InnoDB count() match table status?
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 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't the InnoDB count() match table status?
> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't the InnoDB count() match table status?
> > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
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: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't the InnoDB count() match table status?
> 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/en/show-table-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. :-\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
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/en/show-table-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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
