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
key?
"As you can see, it only makes sense to index those fields you
use in the WHERE clause."
http://www.databasejournal.com/features/mysql/article.php/10897_1382791_2
Q2. Does a SELECT statement look at an index before it looks at a
table?
"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..."
http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1
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?
?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
