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?
>Solution :
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.