Query taking too much time

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 (catalo

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:

Try the SQLFiddle here.

StackExchange Database Administrators Q#30820, answer score: 5