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