MySQL Forums
Forum List  »  Optimizer & Parser

Problems optimize query
Posted by: Rafal Kedziorski
Date: January 08, 2007 05:14PM

Hi,

I'm using MySQL 5.0.27 and 4-8 parallel queries make my MySQL instance slow. The CPU usage grows to 90-100%.

I have some problems optimize this query:

select
distinct m.media_id, m.media_type_id, mf.media_file_id
from
category_tree c_tree,
media_2_category m2c,
media_activation ma,
media m inner join media_file mf on (mf.media_id = m.media_id)  ,
media_2_partner m2p,
magix_product mp,
media_type_2_magix_product mt2mp
where
m.media_id = ma.media_id
and
c_tree.mandant_id = 2
and
c_tree.partner_id = 1
and
c_tree.category_tree_id = m2c.category_tree_id
and
m2c.media_id = m.media_id
and
m2p.media_id = m.media_id
and
m2p.partner_id = 1
and
ma.partner_id = 1
and
mp.magix_product_id = 155
and
mp.magix_product_id = mt2mp.magix_product_id
and
m.media_type_id = mt2mp.media_type_id
and
(mf.language_id = 4 or mf.language_id is null)
and
mf.media_file_quality_id = 4
and
(c_tree.category_tree_id = 4093 or c_tree.parent_id = 4093 or c_tree.path like '3785/4092/4093/%')
ORDER BY m2p.priority desc ,m.media_id  limit 0, 25

and explain of this query

id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;SIMPLE;mp;const;PRIMARY;PRIMARY;2;const;1;Using index; Using temporary; Using filesort
1;SIMPLE;mt2mp;ref;media_type_2_magix_product__idx,media_type_id,magix_product_id;magix_product_id;2;const;21;
1;SIMPLE;m;ref;PRIMARY,media_type_id;media_type_id;2;omk.mt2mp.media_type_id;324;Using index
1;SIMPLE;mf;ref;media_file_quality_language__idx,media_id,media_file_quality_id,language_id,media_file_quality__idx;media_file_quality__idx;10;omk.m.media_id,const;1;Using where
1;SIMPLE;m2p;eq_ref;media_partner__idx,media_id,partner_id;media_partner__idx;10;omk.mf.media_id,const;1;Using where; Distinct
1;SIMPLE;ma;eq_ref;media_activation__idx,media_id,partner_id;media_activation__idx;10;omk.m.media_id,const;1;Using index; Distinct
1;SIMPLE;m2c;ref;category_tree_id,media_id;media_id;8;omk.mf.media_id;3;Using where; Distinct
1;SIMPLE;c_tree;eq_ref;PRIMARY,category_tree__idx,parent_id,mandant_id,partner_id,category_tree_path__idx;PRIMARY;8;omk.m2c.category_tree_id;1;Using where; Distinct

and this are the tables

CREATE TABLE `category_tree` (
  `category_tree_id` BIGINT         UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id`        BIGINT         UNSIGNED,
  `mandant_id`       SMALLINT       UNSIGNED NOT NULL,
  `partner_id`       SMALLINT       UNSIGNED NOT NULL,
  `class_id`         VARCHAR  ( 32)          NOT NULL,
  `position`         SMALLINT       UNSIGNED NOT NULL,
  `path`             VARCHAR  (255),
  `description`      VARCHAR  (255)          NOT NULL,
  #
  PRIMARY KEY (`category_tree_id`),
  INDEX (`parent_id`),
  INDEX (`mandant_id`),
  INDEX (`partner_id`),
  FOREIGN KEY (`parent_id`)
        REFERENCES `category_tree` (`category_tree_id`),
  FOREIGN KEY (`mandant_id`)
        REFERENCES `mandant` (`mandant_id`),
  FOREIGN KEY (`partner_id`)
        REFERENCES `partner` (`partner_id`)
) TYPE=InnoDB;

CREATE UNIQUE INDEX `category_tree__idx`
                      ON `category_tree` (`mandant_id`, `class_id`);
CREATE INDEX `category_tree_path__idx`
                      ON `category_tree` (`path`);


CREATE TABLE `media_2_category` (
  `media_2_category_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_tree_id`    BIGINT UNSIGNED NOT NULL,
  `media_id`            BIGINT UNSIGNED NOT NULL,
  #
  PRIMARY KEY (`media_2_category_id`),
  INDEX (`category_tree_id`),
  INDEX (`media_id`),
  FOREIGN KEY (`category_tree_id`)
        REFERENCES `category_tree` (`category_tree_id`),
  FOREIGN KEY (`media_id`)
        REFERENCES `media` (`media_id`)
) TYPE=InnoDB;

CREATE UNIQUE INDEX `media_2_category__idx`
                      ON `media_2_category` (`media_id`, `category_tree_id`);


CREATE TABLE `media_activation` (
  `media_activation_id`         BIGINT      UNSIGNED NOT NULL AUTO_INCREMENT,
  `media_id`                    BIGINT      UNSIGNED NOT NULL,
  `partner_id`                  SMALLINT   UNSIGNED NOT NULL,
  #
  PRIMARY KEY (`media_activation_id`),
  INDEX (`media_id`),
  INDEX (`partner_id`),

  FOREIGN KEY (`media_id`)
        REFERENCES `media` (`media_id`),

  FOREIGN KEY (`partner_id`)
        REFERENCES `partner` (`partner_id`)

) TYPE=InnoDB;

CREATE UNIQUE INDEX `media_activation__idx`
                      ON `media_activation` (`media_id`, `partner_id`);


CREATE TABLE `media` (
  `media_id`          BIGINT        UNSIGNED NOT NULL AUTO_INCREMENT,
  `mandant_id`        SMALLINT      UNSIGNED NOT NULL,
  `media_type_id`     SMALLINT      UNSIGNED NOT NULL,
  `class_id`          CHAR     (32)          NOT NULL,
  `creation_date`     DATETIME               NOT NULL,
  `provider`          VARCHAR  (20)          NOT NULL,
  `source`            VARCHAR  (128),
  `media_pool_id`     SMALLINT      UNSIGNED,
  #
  PRIMARY KEY (`media_id`),
  INDEX (`mandant_id`),
  INDEX (`media_type_id`),
  INDEX (`media_pool_id`),
  FOREIGN KEY (`mandant_id`)
        REFERENCES `mandant` (`mandant_id`),
  FOREIGN KEY (`media_type_id`)
        REFERENCES `media_type` (`media_type_id`),
  FOREIGN KEY (`media_pool_id`)
        REFERENCES `media_pool` (`media_pool_id`)
) TYPE=InnoDB;

CREATE UNIQUE INDEX `media__idx`
                      ON `media` (`mandant_id`, `class_id`);

CREATE UNIQUE INDEX `media_class_id__idx`
                      ON `media` (`class_id`);


CREATE TABLE `media_file` (
  `media_file_id`           BIGINT         UNSIGNED  NOT NULL AUTO_INCREMENT,
  `media_id`                BIGINT         UNSIGNED  NOT NULL,
  `class_id`                CHAR (32)               NOT NULL,
  `filename`                VARCHAR  (255),
  `media_file_quality_id`   SMALLINT       UNSIGNED NOT NULL,
  `language_id`             SMALLINT       UNSIGNED,
  `creation_date`           DATETIME                 NOT NULL,
  `binary_last_change_date` DATETIME,
  `filesize`                INTEGER        UNSIGNED,
  `codec_id`                SMALLINT       UNSIGNED NOT NULL,
  `format`                  INTEGER,
  `md5_sum`                 CHAR     (32),
  #
  PRIMARY KEY (`media_file_id`),
  INDEX (`media_id`),
  INDEX (`media_file_quality_id`),
  INDEX (`language_id`),
  INDEX (`codec_id`),
  FOREIGN KEY (`media_id`)
        REFERENCES `media` (`media_id`),
  FOREIGN KEY (`media_file_quality_id`)
        REFERENCES `media_file_quality` (`media_file_quality_id`),
  FOREIGN KEY (`language_id`)
        REFERENCES `language` (`language_id`),
  FOREIGN KEY (`codec_id`)
        REFERENCES `codec` (`codec_id`)
) TYPE=InnoDB;

CREATE UNIQUE INDEX `media_file_quality_language__idx`
                      ON `media_file` (`media_id`, `media_file_quality_id`, `language_id`);
CREATE INDEX `media_file_quality__idx`
                      ON `media_file` (`media_id`, `media_file_quality_id`);
CREATE UNIQUE INDEX `media_file_class_id__idx`
                      ON `media_file` (`class_id`);


CREATE TABLE `media_2_partner` (
  `media_2_partner_id`             BIGINT   UNSIGNED NOT NULL AUTO_INCREMENT,
  `media_id`                       BIGINT   UNSIGNED NOT NULL,
  `partner_id`                     SMALLINT UNSIGNED NOT NULL,
  `priority`                       SMALLINT UNSIGNED NOT NULL,
  #
  PRIMARY KEY (media_2_partner_id),
  INDEX (`media_id`),
  INDEX (`partner_id`),
  FOREIGN KEY (`media_id`)
        REFERENCES `media` (`media_id`),
  FOREIGN KEY (`partner_id`)
        REFERENCES `partner` (`partner_id`)

) TYPE=InnoDB;

CREATE UNIQUE INDEX `media_partner__idx`
                      ON `media_2_partner` (`media_id`, `partner_id`);
CREATE INDEX `media_priority__idx`
                      ON `media_2_partner` (`priority`);


CREATE TABLE `magix_product` (
  `magix_product_id`    SMALLINT      UNSIGNED NOT NULL,
  `product_name`        VARCHAR(50)   NOT NULL,
  #
  PRIMARY KEY (`magix_product_id`)
) TYPE=InnoDB;

CREATE UNIQUE INDEX `magix_product__idx`
                      ON `magix_product` (`product_name`);


CREATE TABLE `media_type_2_magix_product` (
  `media_type_2_magix_product_id`  BIGINT        UNSIGNED NOT NULL AUTO_INCREMENT,
  `media_type_id`                  SMALLINT      UNSIGNED NOT NULL,
  `magix_product_id`               SMALLINT      UNSIGNED NOT NULL,
  #
  PRIMARY KEY (`media_type_2_magix_product_id`),
  INDEX (`media_type_id`),
  INDEX (`magix_product_id`),
  FOREIGN KEY (`magix_product_id`)
        REFERENCES `magix_product` (`magix_product_id`),
  FOREIGN KEY (`media_type_id`)
        REFERENCES `media_type` (`media_type_id`)
) TYPE=InnoDB;

CREATE UNIQUE INDEX `media_type_2_magix_product__idx`
                      ON `media_type_2_magix_product` (`media_type_id`, `magix_product_id`);

Can anybody give me some tips?


Best Regards,
Rafal

Options: ReplyQuote


Subject
Views
Written By
Posted
Problems optimize query
3896
January 08, 2007 05:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.