Why is MySQL not using the indexes I have created?

user_18990544 0
Viewed 45

Any ideas why index is not using on table SD and how to fix it?

I tried to remove the group and sort clauses but still same issue and cant find that is the problem

P.S. dont read this system wont let me post because code is more than description

Query

        SELECT sd.filter_group_id, fgd.name AS group_name, sdc.filter_id AS filter_id, fd.name, 
            COUNT(DISTINCT p2c.product_id) AS total, f.sort_order, sd.sort_order AS sort, 
            (CASE
                WHEN fgd.custom_order = 1 
                THEN COUNT(p2c.product_id) 
            END) AS custom_order
        FROM oc_sd_filter sd
            JOIN oc_product_to_category p2c ON p2c.category_id = sd.category_id 
            JOIN oc_product_filter sdc18 ON sdc18.product_id = p2c.product_id 
            JOIN oc_product_filter sdc21 ON sdc21.product_id = p2c.product_id 
            JOIN oc_product p ON p.product_id = p2c.product_id
            JOIN oc_product_filter sdc ON sdc.product_id = p2c.product_id
            JOIN oc_filter f ON sdc.filter_id = f.filter_id
            JOIN oc_filter_description fd ON sdc.filter_id = fd.filter_id
            JOIN oc_filter_group_description fgd ON fd.filter_group_id = fgd.filter_group_id

        WHERE sd.category_id = '93' 
            AND p.status = '1' 
            AND sd.filter_group_id = fd.filter_group_id 
            AND sd.status = 1
            AND sdc18.filter_id IN (199,200,120,321,611,451,380,542) 
            AND sdc21.filter_id IN (241,242)
        GROUP BY fd.filter_id, fd.filter_group_id
        ORDER BY sd.sort_order ASC, 
            (CASE 
                WHEN fgd.custom_order = 0 
                THEN f.sort_order 
             END) ASC, 
             (CASE 
                WHEN fgd.custom_order = 1 
                THEN COUNT(p2c.product_id) 
              END) DESC

EXPLAIN

    id  select_type    table   type     possible_keys               key        key_len    ref                            rows   Extra   
    1   SIMPLE         sd      ALL      filter,cat,status           NULL       NULL       NULL                            11    Using where; Using temporary; Using filesort
    1   SIMPLE         fgd     ref      PRIMARY,filter_group_id     PRIMARY     4         example_db.sd.filter_group_id   1     
    1   SIMPLE         p2c     ref      PRIMARY,category_id,        category_id 4         example_db.sd.category_id       59    Using index
    1   SIMPLE         p       eq_ref   PRIMARY,status,product_id   PRIMARY     4         example_db.p2c.product_id       1     Using where
    1   SIMPLE         sdc     ref      PRIMARY                     PRIMARY     4         example_db.p2c.product_id       9     Using index
    1   SIMPLE         fd      ref      PRIMARY,filter              PRIMARY     4         example_db.sdc.filter_id        1     Using where
    1   SIMPLE         f       eq_ref   PRIMARY                     PRIMARY     4         example_db.sdc.filter_id        1     
    1   SIMPLE         sdc21   ref      PRIMARY                     PRIMARY     4         example_db.p2c.product_id       9     Using where; Using index
    1   SIMPLE         sdc18   ref      PRIMARY                     PRIMARY     4         example_db.p2c.product_id       9     Using where; Using index

Table

        CREATE TABLE `oc_sd_filter` (
      `id` int(11) NOT NULL,
      `category_id` int(11) NOT NULL,
      `filter_group_id` int(11) NOT NULL,
      `status` int(11) NOT NULL,
      `sort_order` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- Indexes for table `oc_sd_filter`
    --
    ALTER TABLE `oc_sd_filter`
      ADD PRIMARY KEY (`id`),
      ADD KEY `filter` (`filter_group_id`),
      ADD KEY `cat` (`category_id`),
      ADD KEY `status` (`status`),
      ADD KEY `sort_order` (`sort_order`);
indexing mysql
1 Answers

Suggested composite indexes:

sd:  INDEX(category_id, status, filter_group_id,  sort_order)
fgd:  INDEX(filter_group_id,  name, custom_order)
sdc:  INDEX(product_id,  filter_id)
fd:  INDEX(filter_group_id, filter_id,  name)
p2c:  INDEX(category_id,  product_id)
f:  INDEX(filter_id,  sort_order)
oc_product_filter: INDEX(product_id, filter_id)
p:  INDEX(status, product_id)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

If that does not help enough, come back and we can talk about turning the query inside out -- so that the GROUP BY is done before most of the JOINs. But first, how many rows in the resultset? How many rows if you take out the GROUP BY clause?

Example (from Comment):

SELECT  filter_group_id
    FROM  sd
    WHERE  status = 1
    ORDER BY  sort_order

The Optimal index is both composite and "covering"; the order is important:

INDEX(status, sort_order,  filter_group_id)

Any longer index starting with those is essentially "as good". Any shorter index (eg, INDEX(status, sort_order) or starting with that) will be "good", but "not as good".

In particular, the 4-column index I provided above is not useful. It is OK to add both indexes; the Optimizer will decide which index to use for each SELECT.

user_17668310
Related