Here are the pros and cons of indexes
Indexing is one of the most useful features of MySQL. MySQL allows several types of indexes, such as a primary key index, a unique index, a normal index, also known as a "non-unique index, a regular index, an unrestricted index" and a full-text index. Of course, indexes improve the speed of SELECT queries, but they have and considerable disadvantages: The benefits of MySQL indexes
Generally speaking, indexing MySQL into a 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. * Text 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
When an index is created in a column (s), MySQL also creates a separate file that is sorted and contains only the fields that you are interested in sorting.
First, indexes occupy disk space. Usually the use of space is not significant, but due to the creation of an index for each column in each possible combination, the index file will grow much faster than the data file. In the case where the table has a large table size, the index file can reach the maximum file size of the operating system.
Secondly, indexes slow down the speed of writing queries such as INSERT, UPDATE, and DELETE. Since MySQL must internally support "pointers" to inserted rows in the actual data file, therefore, in the case of the above write requests, there is a execution price, because every time a record is changed, the indices must be updated. However, you can write your queries in a way that does not lead to very noticeable performance degradation.