Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My query is taking too much time to execute. Please help me here.

mysql> explain 
select pcm.catalog_id 
from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c  
where 
    pcm.product_id = 2520000  
    and ccm.catalog_id = pcm.catalog_id 
    and ccm.parent_catalog_id = 1000025 
    and levels <> 0 
    and c.catalog_id = pcm.catalog_id 
    and c.precedence is not null 
order by c.precedence;
+----+-------------+-------+------+--------------------------------------------+----------+---------+------------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys                              | key      | key_len | ref                    | rows | Extra                       |
+----+-------------+-------+------+--------------------------------------------+----------+---------+------------------------+------+-----------------------------+
|  1 | SIMPLE      | c     | ALL  | idx_1031,idx_17109                         | NULL     | NULL    | NULL                   | 2769 | Using where; Using filesort |
|  1 | SIMPLE      | pcm   | ref  | idx_2195,idx_1069                          | idx_2195 | 10      | cms.c.catalog_id,const |    1 | Using where; Using index    |
|  1 | SIMPLE      | ccm   | ref  | idx_2902,idx_651,fkey_cat_catal_catal_4433 | idx_2902 | 10      | cms.c.catalog_id,const |    1 | Using where                 |
+----+-------------+-------+------+--------------------------------------------+----------+---------+------------------------+------+-----------------------------+
3 rows in set (0.01 sec)


mysql> show create table cat_produ_catal_map_defer\G
*************************** 1. row ***************************
       Table: cat_produ_catal_map_defer
Create Table: CREATE TABLE `cat_produ_catal_map_defer` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `catalog_id` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_2195` (`catalog_id`,`product_id`),
  KEY `idx_1069` (`product_id`),
  CONSTRAINT `fkey_cat_produ_catal_3725` FOREIGN KEY (`catalog_id`) REFERENCES `cat_catal_defer` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fkey_cat_produ_catal_3763` FOREIGN KEY (`product_id`) REFERENCES `cat_produ_defer` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show create table cat_catal_catal_map_defer\G
*************************** 1. row ***************************
       Table: cat_catal_catal_map_defer
Create Table: CREATE TABLE `cat_catal_catal_map_defer` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `catalog_id` int(11) DEFAULT NULL,
  `parent_catalog_id` int(11) DEFAULT NULL,
  `levels` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_2902` (`catalog_id`,`parent_catalog_id`),
  KEY `idx_651` (`levels`),
  KEY `fkey_cat_catal_catal_4433` (`parent_catalog_id`),
  CONSTRAINT `fkey_cat_catal_catal_3688` FOREIGN KEY (`catalog_id`) REFERENCES `cat_catal_defer` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fkey_cat_catal_catal_4433` FOREIGN KEY (`parent_catalog_id`) REFERENCES `cat_catal_defer` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> show create table cat_catal_defer\G
*************************** 1. row ***************************
       Table: cat_catal_defer
Create Table: CREATE TABLE `cat_catal_defer` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `catalog_id` int(11) DEFAULT NULL,
  `prod_result_changes_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `inheritance_mode` int(11) DEFAULT NULL,
  `name` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `datafile_regexp` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `product_change_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `add_category_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `delete_category_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `root_category_id` int(11) DEFAULT NULL,
  `deleted` int(11) DEFAULT NULL,
  `precedence` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_1031` (`catalog_id`),
  KEY `idx_1709` (`root_category_id`),
  KEY `idx_17109` (`precedence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
share|improve this question
    
This is not 100% related to the question but what's the purpose of having nullable unique keys? Especially because you have foreign keys that reference these nullable columns? –  ypercube Dec 21 '12 at 7:40

2 Answers 2

The core issue appears to be that the optimizer does not (or cannot) use the index idx_17109 to seek for the c.precedence IS NOT NULL predicate. The following modification allows the seek, but still requires a hint to avoid the sort:

SELECT
    pcm.catalog_id 
FROM cat_catal_defer AS c USE INDEX (idx_17109)
JOIN cat_produ_catal_map_defer AS pcm ON
    pcm.catalog_id = c.catalog_id
JOIN cat_catal_catal_map_defer AS ccm ON
    ccm.catalog_id = pcm.catalog_id
WHERE
    c.precedence > ''
    AND pcm.product_id = 2520000
    AND ccm.parent_catalog_id = 1000025
    AND ccm.levels <> 0
ORDER BY
    c.precedence;

Explain:

Explain table

Try the SQLFiddle here.

share|improve this answer

"Using filesort" sounds bad.

Your request take its time on the sorting step, do you really need to sort your result set ?

Try to remove the ORDER BY, to validate that the problem comes from here.

Max.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.