Hello,
I have searched for some information on single- and multi-colum indexes, but
often it is mentioned that the behaviour is implementation dependend. So I
post my questions on this list. If it's the wrong place, please redirect me
to the right one.
I am currently working with MySQL 3.22.32 on Redhat 6.2.
Is there a place or book where a newbie can gather information on indexes?
I. Assume I have a table Z with columns a, b, c with three separate indexes
on a, b and c.
1. If I perform a query like
SELECT * FROM Z WHERE b = <some_value>
everyting is fine, because an index exists for the specified column. RIGHT?
2. If I perform a query like
SELECT * FROM Z WHERE a = <some_value>, b = <some_value>, c = <some_value>
I assume only the index for column a is taken, because there is no index
that relates column b to column a and furthermore no index relating column c
to column b. RIGHT?
II. Assume I have a table Z with columns a, b, c with a multi-column index
on a, b, c.
1. If I perform a query like
SELECT * FROM Z WHERE a = <some_value>
I assume the multi-column index is taken in consideration. RIGHT?
2. If I perform a query like
SELECT * FROM Z WHERE a = <some_value>, b = <some_value>, c = <some_value>
I assume that the multi-column index is taken and I will find the resulting
rows rather quickly. RIGHT?
3. If I perform a query like
SELECT * FROM Z WHERE a = <some_value>, c = <some_value>
I assume that the multi-column index cannot be used because - again - there
is no direct relation between column a and c. I would have to add a second
multi-column index on a, c. RIGHT?
4. If I perform a query like
SELECT * FROM Z where b = <some_value>, c = <some_value>
I assume that - again - the multi-column index cannot be used and I would
have to add a second multi-column index on b, c. RIGHT?
5. If I choose a different order of the columns in the where clause say b,
a, c, only the single-column index for column b would be used. RIGHT?
III. Assume I have two tables Y, Z with each of the tables having two column
a and column b.
1. If I perform a query like
SELECT * FROM Y, Z WHERE Y.a = Z.a
would a single-column index on both tables on column a speed up the query?
2. If I perform a query like
SELECT * FROM Y, Z WHERE Y.a = Z.a AND Y.b = <some_value>
would it help to have a multi-column index on table Y (a, b)??
Thanks for your time.
Tobias.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
