> whats the difference between:
> alter table tablename add index (name);
> alter table tablename add index (age);
> alter table tablename add index (birthdate);
> alter table tablename add index (city);
These are four INDIVIDUAL keys.. helpful if you want to search by name,
age, birthday, _OR_ city
> compared to:
> alter table tablename add index (name,age,birthdate,city);
This is a compound key. Helpful usually if you want to span a UNIQUE
entity across multiple columns. Ie, if you wanted to have a unique key
(say a primary key) without having a distinct unique column (like a
counter). The key would be creating essentially is nameagebirthdatecity.
Since you can query on the prefix of a key, a query which would query:
(name) OR
(name && age) OR,
(name && age && birthdate) OR,
(name && age && birthdate && city)
would be optimized.
HOWEVER, a query on age, birthday, or city alone (or any combination which
doesn't follow the prefix order of the key) would NOT.
> What I want is performance/speed in doing the following:
> select * from tablename where name = 'bill';
> select * from tablename where age > 30;
> select * from tablename where city = 'new york';
You would thus want the prior, individual keys.
Take care,
seth
---
Seth Northrop
Manager of Information Technology
Reflectivity, Inc.
3910 Freedom Circle, Suite 103
Santa Clara, CA 95054
voice: 408-970-8881 x147
fax: 408-970-8840
http://www.reflectivity.com/
---------------------------------------------------------------------
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
