List:General Discussion« Previous MessageNext Message »
From:John Kebbel Date:May 27 2007 8:53pm
Subject:Re: General Questions About Indexes
View as plain text  
INDEXES - A Science AND an Art

I've been continuing to look for answers to my own questions. I've found
a few ...

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
        "As you can see, it only makes sense to index those fields you
        use in the WHERE clause."
Q2. Does a SELECT statement look at an index before it looks at a
        "Before we repair the table structure above, let me tell you
        about a most important little secret for anyone serious about
        optimizing their queries: EXPLAIN. EXPLAIN shows (explains!) how
        your queries are being used. By putting it before a SELECT, you
        can see whether indexes are being used properly, and what kind
        of join is being performed..."
Q3. Are JOINs where the real timesaving occurs and SELECTs just a
peripheral issue muddying the water?
        In MySQL, Paul DuBois writes: "Index columns that you search
        for, not columns you select ... [t]he best candidate columns for
        indexing are the columns that appear in your WHERE clause or
        columns named in join clauses." 
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?
        I haven't found the answer to this question, but I did find:
        "Indexes work best for columns with unique values,  and most
        poorly with columns that have many duplicate values" Paul
        DuBois, MySQL 
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?

General Questions About IndexesJohn Kebbel27 May
Re: General Questions About IndexesJohn Kebbel27 May
  • Re: General Questions About IndexesBaron Schwartz28 May