Gleb Paharenko wrote:
> Hello.
>
> If you have a separate indexes on section and status columns, MySQL
> will use only one of them. And as the column status has very small number
> of possible values the cardinality of separate index on it will be too low
> and optimizer won't use this index. I think, if slow updates won't be a problem,
> use multiple-column indexes as now.
So in this case MySQL will choose to use the largest index that suits
the optimization of the query, and there isn't a problem with having
multiple indexes referencing similar column patterns?
However, say for example that I had a similar structure, but for a shop
(ok, not a fantastic example!):
CREATE TABLE products (
`store` INT,
`item_barcode` CHAR(x),
`manufacturer` VARCHAR(y),
PRIMARY KEY(`store`, `item_barcode`)
);
And say I wanted to search for all products in a particular store
related to the one currently being viewed. In this case, I'd have a
SELECT with searches on `store` and `manufacturer`.
Given the high cardinality of `manufacturer`, if an index was created on
just `manufacturer` as well, would MySQL use a sort of union on the
Primary Key index and `manufacturer` index to find all relevant rows? Or
would it still be better to create a multiple column index across
`store` and `manufacturer` together with the Primary Key?
Sorry to bother you again, it's just something I've not come across (and
something not really been covered in what I've read/studied), and I'm
interested to know how the query is optimized given a multiple column
searches and possible multiple indexes. :)
--
Jonathan Wright <mail at djnauk dot co dot uk>
Life has no meaning unless we can enjoy what we've been given
--
Running on Gentoo Linux
(2.6.10-gentoo-r7-djnauk-b03 i686 AMD Athlon(tm) XP 2100+ GNU/Linux)
