List:General Discussion« Previous MessageNext Message »
From:Matthias Pigulla Date:May 13 1999 12:43am
Subject:"How MySQL uses indexes"
View as plain text  
Hi folks,

once again I'm having a question concerning indexes and their use in
JOINing SELECTs. Last time Christian explained me that with MySQL an
"index that doesn't span all AND levels in the WHERE clause is not used
to optimize the query".

Now, I'm having a relation in which there is one attribute used for
looking up rows, and two attributes referencing two other table's
primary keys. Feeling very smart, I defined the key as (lookupattrib,
refattrib, refattrib), believing that it would be possible to do the
whole join with this key. Well, this failed, although I screwed around
for several hours.

Now, I'd like to ask one of you to elaborate a bit more precise on the
key/join issue. I'm almost sure that also a key spanning all AND levels
in the SELECT clause can only be used for retrieval, not JOINing, right?

I feel that one can only use keys for joining if all key parts left from
the one used for the join are constant?

Can such leftmore parts remain from earlier joins? Assume kn is the nth
protion of a key in "SELECT ... FROM t1,t2,t3 WHERE t1.k1=(value) AND
t2.k1=t1.k2...". Would I be able to use t2.k2 for a further JOIN, ie is
t2.k2 logically identical to t1.k2?

What would a solution for my JOIN look like? I cannot imagine that it's
a design flaw if you have a row containing two references at once :-/...

   w e b f a c t o r y | matthias pigulla  mp@stripped
"How MySQL uses indexes"Matthias Pigulla13 May
  • "How MySQL uses indexes"Michael Widenius13 May