myisamchk vs OPTIMIZE TABLE
Hi all! Running 4.0.17. I hope this isn't a stupid question, but it appears that OPTIMIZE TABLE is NOT equivalent to: myisamchk --quick --check-only-changed --sort-index --analyze Maybe I'm missing something, but OPTIMIZE TABLE rebuilds both the data file and the index file (I see a TMD and TMM file being built). myisamchk only appears to rebuild the TMM file. Why is this? How do you get the equivalent OPTIMIZE TABLE in myisamchk? Thanks Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: myisamchk vs OPTIMIZE TABLE
In reference to my earlier message, I think I've figured out that the equivalent command for OPTIMIZE TABLE is: myisamchk -r --sort-index --analyze That isn't documented anywhere... and in fact, the French language version says something conflicting (I don't speak French but a Google search brought me there). Here's my problem: I've got a bunch of tables with hundreds of millions of rows in them. Every night, I delete about couple million rows and then run millions of searches on these tables. What should I worry about more? A sorted index or a data file with no deleted rows in it? I don't mind running either OPTIMIZE TABLE (which apparently rebuilds everything and sorts it) or just the myisamchk to sort the index. Does anyone know which one might get me more mileage? Thanks! Mark > -----Original Message- > From: Mark Hazen [mailto:[EMAIL PROTECTED] > Sent: Sunday, February 08, 2004 11:46 AM > To: [EMAIL PROTECTED] > Subject: myisamchk vs OPTIMIZE TABLE > > > Hi all! > > Running 4.0.17. > > I hope this isn't a stupid question, but it appears that OPTIMIZE TABLE is > NOT equivalent to: > myisamchk --quick --check-only-changed --sort-index --analyze > > Maybe I'm missing something, but OPTIMIZE TABLE rebuilds both the > data file > and the index file (I see a TMD and TMM file being built). myisamchk only > appears to rebuild the TMM file. Why is this? How do you get the > equivalent OPTIMIZE TABLE in myisamchk? > > Thanks > Mark > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: myisamchk vs OPTIMIZE TABLE
> What's the nature of your query? > > If it's using an integer index and that's what your searching on, then > having > it physically sorted is a Good Thing. If you're table-scanning your > main table, you're toast anyway. Finding ways of making that faster is > the > way to go, maybe partitioning your data into "small, fixed width, > fields I > care about" and "other stuff" is the way to go? > My tables are just 2 INT columns. I have unique indexes on them going both ways. Thanks Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: myisamchk vs OPTIMIZE TABLE
> You know, this might sound strange, but does the performance drop off at > all if you lose the indices? A table scan of rows 8 bytes wide is going > to be pretty damn quick. Plus there's a lot less maintenance to do > without > indices and no risk of them getting corrupted. A full table scan is never going to be faster than using indexes when you're running several hundred million rows. Does anyone else have any thoughts? Does anyone know the charge you take in having deleted rows? Or an unsorted index? Which is worse? Thanks Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Attn: MySQL AB: we need 3.23.5x NOW !
> Can someone *please* pick up the dropped ball on the > release schedule and run with it ? It's not such > a big deal for ISAM table users but it's a very big > deal for InnoDB tables... > MySQL AB: The least you could do is say: OK, we have this X problem and still working out Y... but we anticipate being finished next week. Then we'll spend about a week testing the build, and it will be live. Instead we get Monty talking about posting 3.23.50 in a week about 2 months ago. Then nothing... dead caller at the other end! Please help!!! Anyone?! Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
