mysql index size and duplicacy questions

I have a few general questions about database Index. Answers to any question are awaited:

1. How to calculate total memory 开发者_StackOverflow中文版size occupied by an index. And is the size of index directly proportional to its Cardinality ?

2. Do we need to index primary key, or is it indexed by its own. And how will it effect the memory and processing speed of select,insert,update queries if primary key is indexed twice (first automatically while creating table, then manually with different name) ?

3. Can we arrange or control the seq_in_index while using combined indexes. If yes, whats the best way to do this with respect to the cardinality of individual indexes ?

Thanks a lot in advance!!

---

An old question, and I'm surprised there's been no answer. I'll take a stab.

1. Total size of all the indexes for a table can be seen with "SHOW TABLE STATUS". I'm afraid that I don't know how to get the true size of an individual index, but it might be as simple as the size of the columns used and a pointer to the row. The size of the index would depend on the number of rows and the width of the index. Since each row must appear in the index, the cardinality shouldn't matter.
2. The primary key gets an index automatically. Remember that it's a unique index and NULLs are not allowed (as compared with "normal" indexes). Unless mysql is being smart underneath the covers, creating a second index will create a second index. Each additional index takes up space (disk/memory) and time (insert/update/delete).
3. Yes, you can control the sequence in a composite index. I make indexes to (at least) match the WHERE clauses (including joins) and, if possible, include the columns being SELECTED from that table so everything can be gathered from the index and the underlying row doesn't need to be read. As for cardinality, get rid of as many rows as you can as quickly as you can.

Hope that helps.