Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-primary-key-index-in-MySQL, http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-unique-key-or-index-in-MySQL, http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-index-or-key-without-constraints-in-MySQL also known as ("non-unique index", ordinary index, index without constraints") and http://www.mysqlfaqs.net/mysql-faqs/Indexes/What-is-full-text-index-in-MySQL. Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well.
Advantages of MySQL Indexes
Generally speaking, MySQL indexing into database gives you three advantages:
- Query optimization: Indexes make search queries much faster.
- Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
searching: Full-text indexes in MySQL version 3.23.23, users have the
opportunity to optimize searching against even large amounts of text
located in any field indexed as such.
Disadvantages of MySQL indexes
an index is created on the column(s), MySQL also creates a separate
file that is sorted, and contains only the field(s) you're interested in
Firstly, the indexes take up disk space.
Usually the space usage isn’t significant, but because of creating
index on every column in every possible combination, the index file
would grow much more quickly than the data file. In the case when a
table is of large table size, the index file could reach the operating
system’s maximum file size.
Secondly, the indexes slow down the speed of writing queries, such as
Because MySQL has to internally maintain the “pointers” to the inserted
rows in the actual data file, so there is a performance price to pay in
case of above said writing queries because every time a record
is changed, the indexes must be updated. However, you may be able to
write your queries in such a way that do not cause the very noticeable
refer the links also