How to Optimize MySQL databases/Queries

Mark · #1 (**permalink**) 28-08-09, 03:30 PM

We all know that the server load increase oftenly due to the long MySQL queries. Is there any tool to optimize the MySQL databases. If anyone has any tips please feel free to post here.

Nancy J · #2 (**permalink**) 29-08-09, 06:29 AM

simple one is using the mysqlcheck command to OPTIMIZE TABLE in a convenient way for the user

mysqlcheck -o db_name

Query Optimization matters a lot, ie the way in which queries are designed to retrieve the data from the database is highly important so that the MySQL performance is not hampered.

**Harry** · #3 (**permalink**) 29-08-09, 06:57 AM

Nancy and many thanks for your advice

Well, I am not much into database administration but still i will try to help you. Google is my best Friend

If you are using cPanel then you can optimize databases using PhpMyAdmin tool which comes a part of cPanel.
Login phpMyAdmin >> select database name >> Tick the tables you wish to optimize >> choose Optimize table.
This will execute the OPTIMIZE TABLE SQL query on the selected tables and they will be updated and optimized.
Hope it helps you !!!

walterburke · #4 (**permalink**) 19-10-09, 11:11 AM

Hi !

Thank you for the information.
I am learning a lot here and i feel it is a great place to share your knowledge.
I am glad to be here. Thanks once again.

Regards
Walter Burke

**Administrator** · #5 (**permalink**) 20-10-09, 10:03 PM

I have always used myisamchk and it has done great job everytime. Here is how you should use myisamchk :-

myisamchk -o *.MYI

Make sure you run this command from shell and inside the Database directory.