What happens when I create an index on a column?

Question

I asked multiple question about indexes already. Something like this:

Would following queries benefit from this index? mytable(col1, col2, col3)

. . . WHERE col1 = ? AND col3 = ?
. . . WHERE col2 = ?
. . . WHERE col1 IN (?, ?)
. . . WHERE col3 = ? AND col1 NOT IN (?, ?)
. . . WHERE col1 = ? OR col2 = ?
. . . WHERE col2 = ? AND col1 = ?
. . . WHERE col1 = ? AND col2 > ?
. . . WHERE col1 = ? AND col3 > ?

-- each question was containing one of these queries ;-)

Every time I got an answer for that specific query which was mentioned in that question, and still I cannot judge such a index would be useful for such a query or not. (or how making it more optimal)

So I decided to ask this question and want to know about the backstage. What happens when I create an index on a column? An index is made of what? An multiple column index is containing what rows (because order is important)? How it works which causes a query much faster?

Actually I need some information about indexes to make me able how can I determine a proper index (multiple columns or single column) for a query.

Note: I have some experiences to work with EXPLAIN. And yes I know using EXPLAIN is really useful in these cases. Now I just need some further information.

Answer 1

An index puts the value, or part of the value in RAM so its faster to access. An index with more than one column aggregates the contents.

So an index with (col1, col2, col3) will be useful for all queries that contain col1 lookups, because col1 is the left-most.

It will be even more beneficial for lookups of col1 and col2, because after getting all the matches for col1, then it can use the col2 part too.

Finally, the col3 part will only ever be used if col1 and col2 have been used already, so its unlikely that it will be useful. But it may be.