By Qushawn Clark
Expert Author
Article Date: 2011-11-17
As your databases get larger and larger and you continually make changes to them, they will get messy and fragmented, just like any data on your computer.
This happens exceptionally quickly in MySQL, which is why they included the OPTIMZE TABLES command to help out. What this command does is de-fragment database tables, specifically those with variable length data (varchar, text, etc.), that are frequently updated or deleted from.
mysql> optimize table employees;
+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| company.employees | optimize | status | OK |
+--------------------+----------+----------+----------+
1 row in set (18.74 sec)
As you can see from above, this is probably going to take a little bit longer than your average query, so be patient when you start the de-fragmenting, but more than likely, you will be surprised by the results. It should significantly increase the speed of your queries, and save you a nice chunk of memory as well , which is always a good thing. Here is an example selection before optimization:
mysql> select count(*) from employees where city ='los angeles';
+----------+
| count(*) |
+----------+
| 2931 |
+----------+
1 row in set (4.53 sec)
This is pretty
slow to get a count for those employee records, and would be a real pain if say, a webpage needed that data to load. So, you run the OPTIMIZE TABLES and wait for it to finish, then run the previous query again and voila!
mysql> select count(*) from backlog where status ='running';
+----------+
| count(*) |
+----------+
| 2931 |
+----------+
1 row in set (0.33 sec)
Now, that looks much better than it did before and certainly wont hold anybody up from getting the information they need. This command should be run periodically to keep things running smoothly. This technique, along with indexing (which I talked about before) can really turn a lagging table into a well-oiled machine. If you need any more explanation go here to look at the entry in the MySQL dev manual.
