CODEWITHSUNDEEP
mysqldatabaserdbmsindexingdatabase-optimization
sebasuy
sebasuy

Reputation: 494

MySQL Indexes creation

I have a table with 10 columns and I need to support combined range filters for most of them.

Let's say for example:

WHERE column_a >= a_min AND column_a <= a_max 
AND column_b >= b_min AND column_b <= b_max
...

But that is not all, I need also to support sorting data by different columns.

My question is, considering that the possible indexes combinations to create in order to optimize the searches is huge, which are my possible options?

Thanks!

Upvotes: 4

Views: 90

Answers (2)

Bohemian
Bohemian

Reputation: 425168

Create indexes for each of the columns individually. Let mysql figure out how to use them.


Also, on a side note, get in the habit of using the between operator:

column_a between a_min AND a_max

rather than:

column_a >= a_min AND column_a <= a_max -- ugly and the semantic is not as obvious

It's easier to read and type and does exactly the same thing.

Upvotes: 4

bren
bren

Reputation: 106

create an index for all the columns used in the query.

(column_a, column_n)

See The Range Access Method for Multiple-Part Indexes here: http://dev.mysql.com/doc/refman/5.0/en/range-optimization.html

Upvotes: 0

Related Questions