I think you want to create separate indexes. Indexes are basically a sorted list. So a single index on all those fields would sort
the data first by id, then bid, then title,...
If id is unique, then there is absolutely no reason to add other fields to the index. Think of a compound index as a field that
combines all the fields specified connected in the order specified. So in your example, searching on title wouldn't use the index
because the index is first on id+bid then title.
Create indexes so the database can quickly narrow down the number of records it needs to search on. If you do a "SHOW INDEX ON
tablename", you'll see a column called "cardinality". This is the uniqueness of the data in the index. Higher numbers indicate more
uniqueness. A cardinality of 2 is bad, since that indicates there are only 2 unique values. Using that index means it would still
have to search half the database, might as well search the whole thing.
Create separate indexes on the fields you mostly search on.
----- Original Message -----
From: "John.H" <[EMAIL PROTECTED]>
To: "mysql" <mysql@lists.mysql.com>
Sent: Thursday, November 02, 2006 3:25 AM
Subject: How many colums should a index contain?
I have two tables and I must do :
select `id`,`bid`,`title`,`link`,`bname` from table1 where `bid` in
( ...this is a subquery in table2 )
should I create a index (`id`,`bid`,`title`,`link`,`bname`) so that my query
will take less time
or should a index contain so many colums?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
