3

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)
1
  • 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? Dec 21, 2012 at 7:40

2 Answers 2

5

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.

-2

"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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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