index question

[schilly]

Can someone give me examples on when to use a multi column index vs two single column indexes?

 

thx.

[fenway]

If you're just using constants for both fields, or are querying on one and sorting on the other, this can be much faster for MyISAM tables.

[schilly]

ok so for queries where you are using both fields in the where clause, it would be faster to have one index on both fields compared to one indexes on each field?

 

All of our tables are MyISAM.

[ajlisowski]

From my experience if you have one index on each field, as opposed to having one on both, it will not use the index.

 

I recently had an issue where I was not indexing on both fields (and instead had two indexes on each individual field) and my query was taking literally 90 seconds. I added the index on both fields and it cut it down to like 800ms.

[Mchl]

This increases performace also for InnoDB. And another thing to consider is order of columns in index defintion.

 

WHERE column1 = ? AND column2 = ? will use index created on (column1,column2) but not on (column2, column1).

[fenway]

Well, 5.1 has index_merge, so you can sometimes get around the "one index per table" issue.

 

Also:

 

This increases performace also for InnoDB. And another thing to consider is order of columns in index defintion.

 

WHERE column1 = ? AND column2 = ? will use index created on (column1,column2) but not on (column2, column1).

That's not actually true -- the optimizer will handle this quite easily.  The order only matter when there are not equality constraints; in that case, the constant needs to go first.  That is:

 

WHERE column1 = ? AND column2 >= ?  will use the index

 

BUT

 

WHERE column1 >= ? AND column2 = ?  won't.

[schilly]

Thanks everyone for the feedback.

 

What's the one index per table issue?

[fenway]

What's the one index per table issue?

MySQL will pick the "best" one to use, and it's not always the most desirable.  MySQL 5 has index_merge, which will sometime generate a pseudo-index that can use multiple, but again, not always reliable.

[schilly]

so if i have a table with multiple single column indexes and i do something like:

 

select * from table where column1 = value1 and column2 = value2 etc etc.

 

and there is an index for column 1 and column 2, MySQL will only use the index on one of them and not both?

[fenway]

That's correct -- EXPLAIN will tell you which one it picked, or if it decided to merge them.

[schilly]

Thanks Fenway. looks like i need to revamp a ton of indexes. I didn't know it could only use one =(

[schilly]

Ok so is that one index per table? how would that work a multiple table query?

[fenway]

Ok so is that one index per table? how would that work a multiple table query?

Still one each.

[schilly]

ok cool. thx.