When trying to optimize the performance of your MySQL database, the first thing you try is often the most futile:
Table Optimization.
We put this first because it’s often the first “fix” people arrive at when trying to fix performance problems, but we think it’s almost never the solution, so we want to get that out of the way first. Generally speaking, most Mysql tables are created in a very optimum way. 5-10 years ago that wasn’t necessarily the case, however these days most problems are caused at the application-level or table-desgin-level (aka. Improper use of tables or improper table structure). It might be worthwhile to skip this article, but it’s a good idea to keep Table Optimization in mind, just don’t assume that’s going to be some magical fix.
“Table Optimization” is a very general term, and there are many ways to go about it. It can be everything from utilizing table indexes to changing the structure of your tables and your application logic to work more efficiently. If basic table optimization doesn’t work, chances are you’re going to need some performance enhancing code – the real good stuff. In other words, get prepared to spend some time looking at your database and application and thinkin’ up ways to fix it. I will be writing an article on “Summary Tables” with some more advanced suggestions on optimizing tables, but here I’m just going to cover MySQL’s built-in features for optimization.
Spending some time, being patient, and understanding your data and application is often the path to improving your performance. Each situation unique, and if it’s not a quick fix, strap in and get ready.
The Rule
Always remember The Rule.
Indexes / Indices
Many people start by adding an Index to their table. Adding indexes (proper: indices, but we don’t care) is a good idea, but once you are experienced at MySQL programming, chances are you will setup the Indexes properly anyway, without issue. It’s not rocket science, and is very easy to get right the first time. If you are dealing with a table with millions of rows, and/or multi-column/cross-column indexes, then you’re going to need a good plan that goes beyond the scope of this article.
Only keep indexes on the columns you need to reference a lot. If you use too many indexes, your Index Size will actually be larger than your original table. Run a “SHOW TABLE STATUS” to check the size of your indexes.
OPTIMIZE TABLE
Running an actual “OPTIMIZE TABLE” command through MySQL is another option. Unfortunately this is also usually futile. This may free up a bit of disk space, but otherwise probably won’t do too much for you. It’s not going to figure out how to automatically work better and faster.
MySQL is very good at keeping tables relatively optimized throughout their lifetime, so the fact that OPTIMIZE TABLE is usually unnecessary and/or useless is a good thing. In the process of many thousands of records getting added and deleted, and data being moved around on the physical hard disk, some little bits of disk space are scattered around and left empty. By optimizing a table you are re-arranging your data to free up these sectors.
FULLTEXT Columns and Keyword Matching
If you are doing a lot of keyword searches against text columns in your database, and you are looking up performance optimization,
Tags: index, magic, magic fix, mysql, optimize, performance, php, the rule
