Menu
I have an events tables in my db, which includes among others start_date and end_date columns.
I frequently run queries like
where start_date > 'some starting date' and end_date < 'some end date'
Will I benefit from adding an index to the start_date and end_date columns? I figure out that this is not a = comparison, but maybe anyway.
279
Yes, index will help you, when using ORDER BY. Because INDEX is a sorted data structure, so the request will be executed faster.
Indexing makes columns faster to query by creating pointers to where data is stored within a database. Imagine you want to find a piece of information that is within a large database. To get this information out of the database the computer will look through every row until it finds it.
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. Indexes can also speed up a DELETE and UPDATE statements if the WHERE condition can make use of the index.
Advantages of MySQL Indexes 1- Indexes make search queries much faster. 2- Indexes like primary key index and unique index help to avoid duplicate row data. 3- Full-text indexes in MySQL, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.
The MySQL optimizer will use the indexes where it thinks it is appropriate to do so:
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.
...
Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)
Source: Comparison of B-Tree and Hash Indexes
You might find these interesting:
How MySQL Uses Indexes
And this answer and this answer to Why does MySQL not use an index for a greater than comparison?.
54
Yes, database will use those indexes and it should increase performance.
Note: it cannot use the two disctinct indexes simultaneously for good performance you need a multi-column index.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
