Hi, I'm a new user and have some related questions about optimizing my tables. I have some large tables of 100M records.
1. I have created one composite index for my largest table (with three columns). I would like to use Table Diagnostics to help me understand how it is working; but there is no information in the help file about the “Table Diagnostics” menu function. Is there a place that I could go to get more background on the various table diagnostics functions? Is table diagnostics the same as myisamchk.
2. To increase search speed, I am considering sorting my table in the same order as my index using order by the three columns. Is that something that the table diagnostics function (optimize?) function can do? Would you suggest doing the “order by” table sort first, then building the index in the same column sequence, or the reverse, or does it make any difference? I am looking to increase speed in a table that is changed only once every 90 days, so I'm not worried about the impact on the update function. If I do the 'order by' function after I have created my index, do I need to rebuild the index? if so, how?
3. I understand that adding indexes takes space on the server disk; how can I find out how large my index is, in terms of space?
4. I also have MONyog; is there a way that I can use it to help optimize the search process for my tables? Will MONyog help me understand the speed/design/optimization of my tables/databases?
thanks for your help with this. I'm learning how to use your tools; they have been very helpful.
best,
Brian
