I’m using laravel & mysql, my database table has over 1M records and continue growing fast. I always need to filter or count by date range in
updated_at column. I’m wondering that should I create indexes for
updated_at. Do the indexes make the query faster? And how much the insert will be slower if I create the two indexes?
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.
Indexes will degrade insert/delete performance since indexes have to be updated. In case of update it depends on whether you update indexed columns. If not, performance should not be affected.