Re: Corruptions like crazy

[John Bowles]

Thanks Andrew.
I was thinking about it last night. I think you are correct. Some parts of the database may at times need rollbacks and crashes sure don't help speed things up so InnoDB is good. The part that does the matching record processing involves two many-to-many link files and only one of them has the loop-within-loop and only at two levels. I came from a background of Assembler and Cobol originally where there were no indexes. As a result we always sorted in optimal order before processing. I know exactly the order this should be done in these matching record loops. I am assuming at this point that new insertions are putting data at the end of the table and the index is a temporary way to get the data processed in order and the optimize puts the data in a better order (as we use to do in the past) for the processing. If this assumption is correct then the question at this point is "How to I get the data in the order of the index I have set up for these high intensity loops which can process several hundred records to get my common song counts". In other words, can I optimize to a specific index rather than the primary key or do I need to change this index to the primary key.
Lots to read and learn. I have your post with the optimize parameter which is a great help. I also by accident stumbled on this there they resolved a speed issue. I haven’t studied it yet but I assume they are using the optimize parameter in the context of a bigger process.
http://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/
Google: “Manage Optimize MySql” is typically how I would find my learning material. At times “tutorial” or “Beginners guide” is added to the search if the material is 100% new to me.