I have a few questions about indexes. I understand (1) what an index
is, and (2) why indexes are useful, but I don't have even a rough idea
about HOW they work. The internet resources I've been able to find don't
answer the questions I'm asking. I also tried
cat /var/lib/mysql/srms07/staff.MYI to see if I could glean some
information directly from an index file, but the MYI file wasn't
Q1. What good does it do to store the primary key or a unique key if
you're normally SELECTing columns that don't use that primary or unique
Q2. Does a SELECT statement look at an index before it looks at a
Q3. Are JOINs where the real timesaving occurs and SELECTs just a
peripheral issue muddying the water?
Q4. What about non-unique indexes? Is the structure of a non-unique
index file similar to the index in the back of a book, the phrase you're
searching for plus a list of row numbers (page numbers for a book) where
that phrase is found?
Q5. Is an item in an index tied to a memory address (like a pointer in
C++) where the indexed data appears inside the larger memory area staked
out by the table?
Q6. As for memory, when you choose a database inside the mysql client,
are all the tables within that database read into memory from the hard
drive, or just the indexes?
Thanks in advance for taking the time to read this, and even more
thanks if you take the time to respond to my questions with either an
explanatory URL or your words explaining the matter.