A multi-column index on lname,fname (order matters) would be used for
SELECT ... WHERE lname='Smith' AND fname = 'Joe';
and
SELECT ... WHERE lname='Smith';
but could not be used for
SELECT ... WHERE fname = 'Joe';
That is, the multi-column index works for searches on the column on the left (in the index definition) and for searches on both, but not for searches on just the column on the right.
You should read the portion of the manual which explains in detail how mysql uses indexes:
<http://dev.mysql.com/doc/mysql/en/Indexes.html> <http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html> <http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html>
Michael
Joshua Beall wrote:
Hi All,
I have noticed that in phpMyAdmin, when I create a table and select the columns I want indexed, if I select two or more columns (say "fname" and "lname") to be indexed, then when the table is created, instead of two keys, I have only one key, and it contains both the fname and lname columns.
If I had forgotten to select those fields to be indexed when I created the table, I would click the "index" link (for both columns) in phpMyAdmin's structure layout page for that table, and I would then wind up with two separate keys, one for each column.
As best I can tell, both ways seem to perform the desired task of allowed faster searching for data in those columns, but I am not sure what the difference is? Can anyone enlighten me? Is one way more "correct?" What are the pros and cons?
Thanks for any insights!
Sincerely, -Josh
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
