List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 13 1999 8:33am
Subject:"How MySQL uses indexes"
View as plain text  

>>>>> "Matthias" == Matthias Pigulla <mp@stripped> writes:

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

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

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

No.  For example if you have a key (a,b,c) in table test,the FULL key
will be used in the following queries:

SELECT * from test where a=1 and b=1 and (c=1 or c=2) and d=1;

SELECT * from test where a=1 and (b=1 and d=2 or b=1 and d=2) and c=1;

SELECT * from test,test2 where test.a=test2.a and test.b=test2.b and test.c=5;

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

This isn't right.

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

I am not 100% sure I followed you here.  What do you mean with
'leftmost parts'.

MySQL does the join the following way:

- Order the table t1,t2,t3 in the 'optimal way'  (lets assume this is t1,t2,t3)

- Read through all matching rows from t1 (with or without indexes).
- Read through all matching rows from t2 where all fields in t1 are
  considered constants.
- Read through all matching rows from t3 where all fields in t1 and t2 are
  considered constants.

When finding 'matching rows', MySQL will use any available index that
can be used to find the rows.

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

If you would have posted an 'EXPLAIN' of your tries + what your tables 
look like, I would have been able to give a more complete answer.

Please remember, it's always better to provide too much information
than to little (as long as the mail keeps reasonable small).


*************** Warning commercial signature follows **********
If you like TCX's concept of a 'mostly free' database and free
advice, you should at least CONSIDER supporting us that we can
afford to keep this service up.
"How MySQL uses indexes"Matthias Pigulla13 May
  • "How MySQL uses indexes"Michael Widenius13 May