Problems optimize query

[Rafal Kedziorski]

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