I've got a couple of general questions regarding database Index. Solutions to the question are looked forward to:

  1. How you can calculate total memory size occupied by a catalog. And it is how big index directly proportional to the Cardinality ?

  2. Do we have to index primary key, or perhaps is it listed in its very own. And just how does it effect the memory and processing speed of choose,place,update queries if primary secret is indexed two times (first instantly while creating table, then by hand with various title) ?

  3. Are we able to arrange or control the seq_in_index while using the combined indexes. If so, what is the easiest method to do that regarding the cardinality of person indexes ?

Thanks ahead of time!!

A classic question, and I am surprised there is no answer. I'll have a stab.

  1. Total size of all of the indexes for any table is visible with "SHOW TABLE STATUS". I am afraid that I'm not sure ways to get the real size a person index, but it may be as easy as how big the posts used along with a pointer towards the row. How big the index would rely on the amount of rows and also the width from the index. Since each row must come in the index, the cardinality should not matter.
  2. The main key will get a catalog instantly. Remember it's a unique index and NULLs aren't permitted (as in comparison with "normal" indexes). Unless of course mysql has been wise beneath the covers, developing a second index can create another index. Each additional index occupies space (disk/memory) and time (place/update/remove).
  3. Yes, you are able to control the succession inside a composite index. I make indexes to (a minimum of) match the WHERE clauses (including joins) and, if at all possible, range from the posts being SELECTED from that table so everything could be collected in the index and also the underlying row does not have to be read. For cardinality, eliminate as numerous rows as possible as rapidly as possible.

Hope that can help.