Index question (MySQL)

[tata668]

If I create an Index in Mysql using 3 Fields (let's say A, B and C) will my data also be indexed for A and B? Or do I have to create a new Index A and B?

Thanks

[coopster]

MySQL should use the existing index as it's key is part of the compound key you first setup. If you are ever unsure or want to double check, all you have to do is use the EXPLAIN [dev.mysql.com] statement.

[coopster]

Here it is in the manual pages ...

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

[dev.mysql.com...]

[tata668]

Thanks a lot coopster!
That's perfect.

[coopster]

You are very welcome, tata668. There is another thread running right now called Overhead question (MySQL) [webmasterworld.com] that might interest you as well. If you're indexing, you'll be OPTIMIZING ;)

[tata668]

Yes, I saw this thread... If I can contribute to it I will but I'm a newbie with performance/indexes tuning using MySQL!

If you don't mind I would have another question. Maybe I should start a new thread for it? Anyway...

I'm currently coding an application that will allow to search on many fields. Let's say I have some "members" and I want to allow people to search on many fields to retrieve "members" corresponding to their searches.
Thing is, again, a lot of fields are possible:
age
sex
city
is a new member?
hair color
height
...

Searches will use some or all of those fields.

For better performances, should I indexe all fields and all combinaisons of fields?

From what I've understood, the more indexes you have:
1) the more memory it takes
2) the more time it takes for INSERTs and UPDATEs

Is that correct?

My application allows "members" to modify their informations but I don't think this will appends so often. Searches (select) will be done way more often then inserts and updates. So I think maybe to use a lot of indexes could be a good idea.

What do you think?
Is using all possible indexes really overkill?

(by the way, sorry again for my not so good english)

[coopster]

The best advice I could offer would be to write your query first, then use the EXPLAIN statement. MySQL will let you know.

[arran]

Don't include columns such as hair colour, sex and 'new member?' in your indexes. These columns do not have enough distinct values to benefit from indexing. Indexes are designed to quickly drill into columns which have many distinct values.

You are correct regarding indexes slowing down inserts, deletes and update - the overhead is caused by having to keep the index(es) updated. However, you are unlikely to notice this performance hit unless your transactional processing rate is high (multiple transactions/second).

[tata668]

coopster:

I'll try that, thanks.

arran:
I'm not sure I understand. Even if "sex" only has two possible values, If I do:

SELECT member_ID FROM member
WHERE member_sex = '1'

And there is no index on "member_sex", MySQL will have to run over all rows in order to find the ones containing the wanted value, no? Poor performances..

[tata668]

coopster, I'll add something:

I'm not sure an EXPLAIN would help because I can't test only one query: there are many possible queries!

member_sex = '0' AND member_hair='8' AND member_new = '0'
-------
member_hair='8' AND member_new = '0' AND member_height='167'
-------
member_weight='180' AND member_hair='8'
----
(...)

There are many fields and queries can be built using any number of those fields together... That's a lot of posible queries!

I can't test them all using EXPLAIN... And I'm pretty sure all the possible combinaisons would "suggest" me different indexes. That's why I'm thinking about indexing all fields! :-)

Thanks for your help...

[arran]

If I do:

SELECT member_ID FROM member
WHERE member_sex = '1'

And there is no index on "member_sex", MySQL will have to run over all rows in order to find the ones containing the wanted value, no? Poor performances..

In the case you describe, MySQL would not use an index, even if one existed. Generally speaking, indexes are ignored if a query retrieves more than 30-40% of the rows in the table. In these cases, doing a full table scan is faster (fewer seeks required).

[tata668]

In the case you describe, MySQL would not use an index, even if one existed. Generally speaking, indexes are ignored if a query retrieves more than 30-40% of the rows in the table. In these cases, doing a full table scan is faster (fewer seeks required).

How does MySQL know a query retrives x% of the rows before executing it?
Also: my fields seldom have only 2 possible values. In general there are +/- 10 possible values per field!

[arran]

How does MySQL know a query retrives x% of the rows before executing it?

For each table in your database, mysql stores a set of statistics including a measure of value distribution within columns (as well as #rows, #pages etc.). When constructing the query plan, the optimiser analyses these statistics before deciding which indexes (if any) to use.

[tata668]

I see you know your business arran! Thanks for those informations.

But then, what would you suggest to index knowing that a lot of different combinaisons of fields can be used for a search?

Should I index all fields that have more than 2 possible values?
Should I index them all separately or together?
Should I get my own stats about which queries are the most frequent and build my indexes using this information?

I'm a little bit lost here!

I understand what indexes are for and approximately how they work but I'm not able to decide which ones would be good in my case because the queries are never the same! They don't necessarily use the same fields each time..