分类： Mysql/postgreSQL
2011-04-22 17:27:15
Select queries in my database and also search area of my site seems working. I assume indexing is required to add into my tables. I am new to MySQL and on learning ways. So I need to know about the MySQL indexing. Can the indexing can improve my select queries performance. Also, is any disadvantage of MySQL indexing? Please explain!
Indexing is one of the more useful features of MySQL. MySQL allows several types of indexes like , , also known as ("non-unique index", ordinary index, index without constraints") and . Of course, the indexes improve SELECT queries speed tremendously. but, they do have some considerable disadvantages as well.
Advantages of MySQL IndexesGenerally speaking, MySQL indexing into database gives you three advantages:
When 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 sorting on.
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 INSERT, UPDATE and DELETE. 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 performance degradation.
