| |
Optimizing MySQL - Part 1: Indexing
By Qushawn Clark
Expert Author
Article Date: 2011-10-25
With all of its stability, complexity, and support options, MySQL can be slow sometimes.
Those working in the field of NoSQL can try to fix that problem, but since they are seemingly taking their sweet time with that, most of us are stuck with what we have. Don't get me wrong, MySQL is a fantastic tool for storing your data, but sometimes its just not fast enough to get the job done. There are, however, some things that you can do to remedy this problem, and the method I will talk about today is indexing.
MySQL indexes are created per column, and speed up the lookup of information based on that specific column. Let's say, for example, that you have a table set up with the columns name, address, and phone number. You could create an index of the phone number column so that phone number specific queries will run faster. It will not speed up the lookup of information in the other columns, but phone number queries should be noticeably faster. Here is a more concrete version of this example.
CREATE TABLE people (
name VARCHAR(60),
address VARCHAR(250),
phone_number INT(10), INDEX(phone_number)
)
As you can see, all you have to do is add the index to the CREATE TABLE or ALTER TABLE query and voila! Now, it should also be noted that this will not always work the way you intend it to, so pay attention to what you are doing. Because MySQL has to create the index separately from the table and update it as the actually table changes, it is possible that if you are doing a lot of queries other than SELECT, you will actually experience a significant drop in performance. There is also the possibility that the addition will do absolutely nothing for performance, which usually indicates that there is something wrong, and we may discuss some reasons why next time. Until then, happy coding!
About the Author:
Qushawn is a staff writer for the iEntry Network.
|
|
