Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
556 views
in Technique[技术] by (71.8m points)

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...