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 <[EMAIL PROTECTED]> 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
