Geek Answers Handbook

Creating MySQL Indexes

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

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’s not all, I also need to support sorting data by different columns.

My question is that the possible combinations of indexes created to optimize searches are huge, and are these my options?

Thanks!

+4
source share
2 answers

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


Also, on a side note, get used to using the between statement:

 column_a between a_min AND a_max

but not:

 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 do the same.

+4
source

Create an index for all columns used in the query.

(column_a, column_n)

See the range access method for multiple part indices: http://dev.mysql.com/doc/refman/5.0/en/range-optimization.html

0
source

More articles:


All Articles