Hello.
> So in this case MySQL will choose to use the largest index that suits
MySQL will choose index which returns less rows.
> Given the high cardinality of `manufacturer`
Cardinality - the number of unique values in the index. So manufacturer
index usually has low cardinality and key on goods which they produce
large cardinality.
> 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
MySQL will only use a single index, and probaly it'll be the left part of the
primary key. You should read:
http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
Jonathan Wright <mail@stripped> wrote:
> 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. :)
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
