mysql indexes

[gameover]

Hi,
I have a few q's about mysql indexes that i can't find the answers to on the net, wondering if anyone could help out -

• What is the index size used for?
• If i have a field of "text" type (or blob), and have an index and set the size to say 10, does it only have an index for the first 10 characters (or bytes)? does the same apply for other types of fields
• If i had two columns, and made a query "select * from tbl where field1='x' and field2 = 'y'", is it best to have an 1 index on two rows, or 2 indexes on one row each?
• Say i had a table about people, and did a query of "select * from people where age = '40' and sex = 'm'" - would it be best to do sex = 'm' (assuming that there are more males than people aged 40 - ie, narrow it down as much as possible as quick as possible, or does mysql not care about order of WHEREs

thanks a lot

 

[roid]

Not sure on some but i'll help with what i've found in the past :

If i have a field of "text" type (or blob), and have an index and set the size to say 10, does it only have an index for the first 10 characters (or bytes)? does the same apply for other types of fields

If you set the index to only 10 characters then it will only contain 10 characters. As far as I'm aware this only applies to text based fields.

If i had two columns, and made a query "select * from tbl where field1='x' and field2 = 'y'", is it best to have an 1 index on two rows, or 2 indexes on one row each?

One index containing both fields would be best.

 

[Beansprout]

All the detail you could ever want is here. There are a few interesting subtleties but it's all quite reasonable common sense

The paragraph you need to read starts with "If a multiple-column index exists on col1 and col2" - the above poster is correct and in answer to WHERE field ordering, yes - it does matter

 

[gameover]

All the detail you could ever want is here. There are a few interesting subtleties but it's all quite reasonable common sense

The paragraph you need to read starts with "If a multiple-column index exists on col1 and col2" - the above poster is correct and in answer to WHERE field ordering, yes - it does matter

Tried looking on there and on google before posting. Things like the index size, just can't find anything about it... (only full text indexes)