Re: Primary Keys, Multiple Index and Searching
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]
Re: Primary Keys, Multiple Index and Searching
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 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]
Re: Primary Keys, Multiple Index and Searching
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.
Jonathan Wright <[EMAIL PROTECTED]> wrote:
> Hiya,
>
> I've been trying to find out about this for a few days, but can't seam
> to find much information about it on the web.
>
> At the moment I've got a few tables, one of this looks like:
>
>
> CREATE TABLE `news` (
> `section` TINYINT UNSIGNED ZEROFILL NOT NULL,
> `article` SMALLINT(4) NOT NULL AUTO_INCREMENT,
> `status` ENUM('show', 'awaiting', 'hide') NOT NULL DEFAULT 'awaiting',
>
>
> PRIMARY KEY (`section`, `article`),
> INDEX news_search (`section`, `status`),
> )
>
>
> There is another table with defines the sections of the site, and each
> section can have it's own articles. Hence the Primary Key. However, most
> of the searches are going to be with the status aswell, as I want to
> display articles marked with status='show', i.e.
>
>
> SELECT * FROM news WHERE section='x' AND status='show';
>
>
> At the moment, the indexes for section are being duplicated, which I
> suppose is a waste of space, and slows the updates (although that's not
> a problem, as updates aren't anywhere near as common as searches).
>
> However, would it be better to knock `section` out of news_search and
> have just `status`? Does MySQL (currently 4.0.14, although it'll be
> running on 3.23.53 eventually) allow the searching of multiple indexes,
> or is it better to specify multiple indexes with similar columns to
> maximize performance?
>
> Thanks,
>
--
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]
Primary Keys, Multiple Index and Searching
Hiya,
I've been trying to find out about this for a few days, but can't seam
to find much information about it on the web.
At the moment I've got a few tables, one of this looks like:
CREATE TABLE `news` (
`section` TINYINT UNSIGNED ZEROFILL NOT NULL,
`article` SMALLINT(4) NOT NULL AUTO_INCREMENT,
`status` ENUM('show', 'awaiting', 'hide') NOT NULL DEFAULT 'awaiting',
PRIMARY KEY (`section`, `article`),
INDEX news_search (`section`, `status`),
)
There is another table with defines the sections of the site, and each
section can have it's own articles. Hence the Primary Key. However, most
of the searches are going to be with the status aswell, as I want to
display articles marked with status='show', i.e.
SELECT * FROM news WHERE section='x' AND status='show';
At the moment, the indexes for section are being duplicated, which I
suppose is a waste of space, and slows the updates (although that's not
a problem, as updates aren't anywhere near as common as searches).
However, would it be better to knock `section` out of news_search and
have just `status`? Does MySQL (currently 4.0.14, although it'll be
running on 3.23.53 eventually) allow the searching of multiple indexes,
or is it better to specify multiple indexes with similar columns to
maximize performance?
Thanks,
--
jonathan wright
// mail at djnauk.co.uk // running on gentoo linux
// life has no meaning unless we can enjoy what we've been given
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
