1. From the documentation i read about the following.An index can contain multiple columns. The ones relevent to the primary or foreign key must be the first IN THAT PARTICULAR INDEX. For instance, let's say that INVOICE is indexed on invoiceno and LINEITEM is indexed on the combination of invoiceno (first) and lineno (second). invoiceno could be set up as a foreign key in LINEITEM, corresponding to the invoiceno column in INVOICE, because invoiceno is the first column in the index.
===============================================
Both tables have to be InnoDB type and there must be
an index where the foreign key and the referenced key
are listed as the FIRST columns.
===============================================
I understand that both tables have to be innodb part.
When the doc specifies both foreign key and referenced
key must be listed as the FIRST columns in the index.
I am confused.
The reason is that within an index you can do an efficient lookup as long as you know the leftmost part of the key. A good analogy is the fiction section of a library, where books are sorted by author's surname followed by given name; this corresponds to a two-column index. If you know the surname, you can still find the author quickly. If you know only the given name, you cannot.
Bruce Feist
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
