CODEWITHSUNDEEP
mysqlindexing
parrker9
parrker9

Reputation: 958

MySQL index question

I've been reading about indexes in MySQL recently, and some of the principles are quite straightforward but one concept is still bugging me: basically, if in a hypothetical table with, let's say, 10 columns, we have two single-column indexes (for column01 and column02 respectively), plus a primary key column (some other column), then are they going to be used in a simple SELECT query like this one or not:

SELECT * FROM table WHERE column01 = 'aaa' AND column02 = 'bbb'

Looking at it, my first instinct is telling me that the first index is going to retrieve a set of rows (or primary keys in InnoDB, if I got the idea right) that satisfy the first condition, and the second index will get another set. And the final result set will be just the intersection of these two. In the books that I've been going through I cannot find anything about this particular scenario. Of course, for this particular query one index on both columns seems like the best option, but I am struggling with understanding the real process behind this whole thing if I try to use two indexes that I described above.

Upvotes: 2

Views: 167

Answers (2)

Markus Winand
Markus Winand

Reputation: 8746

It's actually the most common question about indexing at all: is it better to have one index with all columns or one individual index for every column?

http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/index-combine-performance

Upvotes: 1

Mark Loeser
Mark Loeser

Reputation: 18657

Its only going to use a single index. You need to create a composite index of multiple columns if you want it to be able to index off of each column you are testing. You may want to read the manual to find out how MySQL uses each type of index, and how to order your composite indexes correctly to get the best utilization of it.

Upvotes: 1

Related Questions