John,
Things to consider are that only one index can be used in a query, and it's
what's in your "where" clause that's important. Therefore, your search
("where bid = ...") will only use an index that has "bid" as the first
column in it. Therefore your multicolumn index wouldn't be used, as "id" is
the first column in the index, but "id" isn't in the where clause of your
query.
Sometimes you need to make lots of multicolumn indexes on a table to
optimise all of your queries. Sometimes this makes the indexes much larger
than the data itself.
Hope this helps,
Andy
> -----Original Message-----
> From: Brent Baisley [mailto:[EMAIL PROTECTED]
> Sent: 03 November 2006 16:04
> To: John.H; mysql
> Subject: Re: How many colums should a index contain?
>
> 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
