UBB.Developers Forums Community Development Programming MySQL and Server Configuration Trying to optimize a table

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:


<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.