I'm trying to optimize tables on a new server.
I have root access via phpMyAdmin, but when I try to optimize the tables, I get this:
The handler for the table doesn't support check/repair
The mySQL version seems to be a bit outdated: 3.23.46
Is this a permissions thing I'm missing?
Do you have shell access? You can go in directly and try it.
However I do beleive that does look like the user does not have all the permissions we normally see.
Same thing:
<br />mysql> OPTIMIZE TABLE `w3t_Users; <br />+-----------------+----------+----------+--------------------------------------------------------+ <br />| Table | Op | Msg_type | Msg_text | <br />+-----------------+----------+----------+--------------------------------------------------------+ <br />| forum.w3t_Users | optimize | error | The handler for the table doesn't support check/repair | <br />+-----------------+----------+----------+--------------------------------------------------------+ <br />
<shrug>
Thought it would make my upgrade go faster - it's coming from 5.5.1 - but I might just have to wing it.
Have you tried myisamchk that should work when the normal ones dont
I'm not familiar with their use.
do you just type
myisamchk tablename at the mysql prompt?
You can. There are some switches you can use
Like -a is for analize
Okay you can type
myisamchk tbl_name to check the tables
Then only on the tables that need repair you can type this
myisamchk -r -q tbl_name
Thanks.
What about optimizing?
I like to do that with upgrades/server moves. Tends to reduce the table size.
That command should also optimize the table however there are some more switches one can use. I already stated the -a for analyze
This is right off the mysql site
4.5.6.10 Table Optimization
To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records, run myisamchk in recovery mode:
shell> myisamchk -r tbl_name
You can optimize a table in the same way using the SQL OPTIMIZE TABLE statement. OPTIMIZE TABLE does a repair of the table and a key analysis, and also sorts the index tree to give faster key lookups. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE. See section 4.6.1 OPTIMIZE TABLE Syntax.
myisamchk also has a number of other options you can use to improve the performance of a table:
* -S, --sort-index
* -R index_num, --sort-records=index_num
* -a, --analyze
Just a follow up to what solved this. Didn't catch it before, and not sure why, but the tables that wouldn't optimize where ISAM instead of MyISAM.
Once I changed the tables to that type - all was fine.