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)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
