Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

On my journey exploring MySQL and its advanced features, especially regarding the performance optimization I bumped in some extremely important feature for SQL performance improvement mos of us knows just as "Index"

I went on with some experimentation and found out, that I can improve my database reads, and advanced write functions from 20 to 100 times just by applying additional index, to one of the most used columns.

Natural comes a question, if such a performance improvement is viable, why not to set up indexing for every database column, and have 2 till 100 times faster database operations?

Obviously there is a reason why this does not happen by default.

So, how to know what to Index and when? What are the limitations of Indexing, and how it can affect my overall system performance if I go over the head with too much Indexing. Is Indexing affecting my database read and write performance when used too much?

I am using InnoDB as my primary database engine, my system runs perl + apache + mysql CPUs: Intel(R) Xeon(R) CPU L5520 @ 2.27GHz RAM: 8192 MB

share|improve this question
2  
Entire volumes have been written on the subject of database design, which is unrelated to system administration. You would do well to read such books, or any of the numerous tutorials on the Internet covering the subject. –  John Gardeniers Apr 4 '11 at 11:09
add comment

3 Answers

up vote 1 down vote accepted

Natural comes a question, if such a performance improvement is viable, why not to set up indexing for every database column, and have 2 till 100 times faster database operations?

First, indexing doesn't help in all situations. If the queries are poorly written, an index will do nothing to help. Indexes can take quite a lot of space, often times many times larger than the data itself, so adding an index for every column will be extremely wasteful.

So, how to know what to Index and when? What are the limitations of Indexing, and how it can affect my overall system performance if I go over the head with too much Indexing.

Your queries determine what data needs to be indexed. The EXPLAIN command will get you quite far in knowing what needs to be indexed and how effectively your indexes are helping your queries perform.

Indexes won't help bad queries. For example, queries such as select * from table where col like "%stuff%" or select * from table where col rlike "ing$" won't be helped much by additional indexing. You're better off in these cases tuning your queries than indexing.

Tune your queries first. Use EXPLAIN and watch the logs for slow queries. Once you've confirmed the queries cannot be tuned any more, then begin to add indexes.

share|improve this answer
add comment

Indexes takes space, and if you update your data, the indexes need to be rebuilt, which takes time to do.

Your indexes speeds up searching in the tables. Not all operations!

To know what indexes you need, you need to understand your application, and also a little about how a RDBMS works.

Example: if you have a table with people in it, and your application never searches for people with a specific shoe size, you most likely don't need an index on shoe size.

In MySQL, there is a function for logging slow queries, and queries not using indexes. These might be helpful for you to help planning your indexing.

share|improve this answer
add comment

In addition to all of the advice above, which is excellent, I'd like to note that any table that sees a lot of write activity will be problematic with indexing because the indexing process will be continually running high to keep up with the changes.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.