CODEWITHSUNDEEP
mysqlindexing
shealtiel
shealtiel

Reputation: 8388

Do indexes speed up greater than > comparison in MySQL?

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.

Upvotes: 28

Views: 20093

Answers (3)

Rick James
Rick James

Reputation: 142453

I recommend having these two composite indexes:

INDEX(start_date, end_date),
INDEX(end_date, start_date)

However, they may not be as beneficial as you would like. This is because there is no good way to use an index on more than one "range".

If the ranges do not overlap, then there is a somewhat complex technique to make the certain queries quite fast: http://mysql.rjweb.org/doc.php/ipranges

Misc

(A semi-related comment on which things are more optimizable.)

These are in order of how optimizable they are

  • Equal: =, IS NULL, LIKE 'abc' (no wildcards), IN (only-one-item)
  • IN: IN (multiple-items) (see also the equivalent OR)
  • Range: '>' (etc), BETWEEN..AND.., LIKE 'abc%' (wildcard, but not at start), IS NOT NULL
  • Un-optimizable: LIKE '%xyz' (leading wildcard), unsargable (see Wikipedia), NOT anything REGEXP

Upvotes: 1

Mike
Mike

Reputation: 21659

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

Upvotes: 26

Karoly Horvath
Karoly Horvath

Reputation: 96286

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.

Upvotes: 1

Related Questions