Once I faced a situation wherein I had no option to delete the database and was limited to use only the MySQL command line. I wasted a lot of time find and trying different ways to delete all tables in the database. So, I thought it will be worth sharing things that ultimately worked for me.

$ mysqldump -hHOSTNAME -uUSERNAME -pPASSWORD --add-drop-table --no-data DB_NAME | grep -e '^DROP \| FOREIGN_KEY_CHECKS' | mysql -hHOSTNAME -uUSERNAME -pPASSWORD DB_NAME;

Optimizing Table in database is just like the doing Disk Defragmentation in your PC.

Optimize Table should be used if you have deleted a large part of a table or if you have made many changes to a table with variable length rows, such as VARCHAR, TEXT, BLOB or VARBINARY columns. Deleted rows are maintained in the linked list and insert operations reuse the old row positions. You can use “Optimize Table” to reclaim unused space and defragment the data file for optimal performance. If a lot of changes have been made to a table, optimizing the table can sometimes significantly improve performance.

If you try importing a mysql database backup file greater than 2 MB then you may receive an error – “No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.”. No Worries! you just need to make few changes to your php.ini file.

Magento maintains several log tables for tracking purpose like customer access, products viewed, products compared etc. These tables grow in size day by day so if you have a large numbers of visitors on your website the size of these log tables may become large enough within a week slowing down your database. So you should perform database log cleaning on a regular basis – daily/weekly/monthly depending upon your website traffic.

There are three ways to clean out these tables:

1. Log Cleaning in the Magento Admin

2. Shell Utility log.php in the ../shell directory, and

3. Manually via phpMyAdmin or mysql client