From my understanding, adding an index on a MySQL column speeds up requests using this column in the where clause by creating a brand new table. So select become faster but insert/modify become slower because there are now two tables to insert data into but in my case it's not even relevant.

Am I then right assuming that any new data in that table will be retrieved faster aswell?

CodePudding user response：

UPDATE and DELETE have WHERE clauses too. If you want your change to be applied to a specific row, it improves performance if that row can be found using an index, instead of forcing the UPDATE or DELETE to scan the entire table.

Indexes are kept in sync with all the rows.^* So if you define an index for the table, any subsequent INSERT/UPDATE/DELETE changes entries in the index. Thus new rows also benefit from the performance improvement.

^* Some other brands of SQL database have "partial indexes" that apply only to a subset of the rows of a table, but MySQL doesn't have this feature.

CodePudding user response：

It does not create a second table.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.