2

Is it possible that adding a redundant index improves the performance of a query? I encountered a case like that several days ago and since then I've been scratching my head: how?

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index.

One SELECT, performing poorly, looks like:

select ...
from X
  left join magic M1 on M1.a = X.a and M1.b = X.b and M1.c = 100
  left join magic M2 on M2.a = X.a and M2.b = X.b and M2.c = 200
  left join other O on O.a = M1.a
where
  O.a in (123, 234, 345, ..., 789)

(The query is a part of Magento2's core).

A developer added a new index in the magic table on just (a) (which is a redundant one) and then, magic, the SELECT got substantial perfomance boost.

However, the query plans from EXPLAIN remain the same before and after a new index has been added. Both use the index (a, b, c) only. (Sorry - I was given misleading information).

This seems kind of weird to me. I can't understand: why and how? Theoretically, this new index does not change anything. But in practice - it does.

I've found this: https://blog.pythian.com/duplicate-indexes-in-mysql/, but the article doesn't explain why things work that way.

Every other places repeat and agree that "redundant index can, and should be, deleted".

So anyone has had a case similar to mine and knows how it works?

edit: (now, I have an access to the DB)

Original query (built programmatically by Magneto2 Framework):

SELECT `u`.*
FROM ((SELECT DISTINCT `cpe`.`entity_id`,
                       `dd`.`attribute_id`,
                       `s`.`store_id`,
                       COALESCE(ds.value, dd.value) AS `value`,
                       `cpe`.`entity_id`            AS `source_id`
       FROM `store` AS `s`
                LEFT JOIN `catalog_product_entity_int` AS `dd` ON dd.store_id = 0
                LEFT JOIN `catalog_product_entity_int` AS `ds`
                          ON ds.store_id = s.store_id AND ds.attribute_id = dd.attribute_id AND
                             ds.entity_id = dd.entity_id
                LEFT JOIN `catalog_product_entity_int` AS `d2d`
                          ON d2d.store_id = 0 AND d2d.entity_id = dd.entity_id AND d2d.attribute_id = 97
                LEFT JOIN `catalog_product_entity_int` AS `d2s`
                          ON d2s.store_id = s.store_id AND d2s.attribute_id = d2d.attribute_id AND
                             d2s.entity_id = d2d.entity_id
                LEFT JOIN `catalog_product_entity` AS `cpe` ON cpe.entity_id = dd.entity_id
                LEFT JOIN `catalog_product_entity_int` AS `pis`
                          ON pis.entity_id = cpe.entity_id AND pis.attribute_id = dd.attribute_id AND
                             pis.store_id = s.store_id
       WHERE (s.store_id != 0)
         AND ((ds.value IS NOT NULL OR dd.value IS NOT NULL))
         AND (COALESCE(d2s.value, d2d.value) = 1)
         AND (dd.attribute_id IN (83, 93, 99, 205, 209, 211, 212))
         AND (NOT (pis.value IS NULL AND pis.value_id IS NOT NULL))
         AND (IFNULL(pis.value, COALESCE(ds.value, dd.value)) IS NOT NULL)
         AND (cpe.entity_id IN
              (653591, 653603, 653690, 653697, 653701, 653708, 653716, 653728, 653737, 653748, 653752, 653764, 653774,
               653775, 689894, 689895, 689896, 689897, 689898, 689899, 689900, 689901, 689902, 689903, 689904, 689905,
               689906)))
      UNION
      (SELECT `cpe`.`entity_id`, `wd`.`attribute_id`, `wd`.`store_id`, `wd`.`value`, `cpe`.`entity_id`
       FROM `catalog_product_entity_int` AS `wd`
                LEFT JOIN `catalog_product_entity` AS `cpe` ON cpe.entity_id = wd.entity_id
                LEFT JOIN `catalog_product_entity_int` AS `d2d`
                          ON d2d.store_id = 0 AND d2d.entity_id = wd.entity_id AND d2d.attribute_id = 97
                LEFT JOIN `catalog_product_entity_int` AS `d2s`
                          ON d2s.store_id != 0 AND d2s.attribute_id = d2d.attribute_id AND d2s.entity_id = d2d.entity_id
       WHERE (COALESCE(d2s.value, d2d.value) = 1)
         AND (wd.attribute_id IN (83, 93, 99, 205, 209, 211, 212))
         AND (wd.value IS NOT NULL)
         AND (wd.store_id != 0)
         AND (cpe.entity_id IN
              (653591, 653603, 653690, 653697, 653701, 653708, 653716, 653728, 653737, 653748, 653752, 653764, 653774,
               653775, 689894, 689895, 689896, 689897, 689898, 689899, 689900, 689901, 689902, 689903, 689904, 689905,
               689906)))) AS `u`
;

EXPLAIN (no extra index):

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "rows": 1485,
      "filtered": 100,
      "materialized": {
        "query_block": {
          "union_result": {
            "table_name": "<union2,3>",
            "access_type": "ALL",
            "query_specifications": [
              {
                "query_block": {
                  "select_id": 2,
                  "temporary_table": {
                    "table": {
                      "table_name": "s",
                      "access_type": "index",
                      "possible_keys": ["PRIMARY"],
                      "key": "STORE_WEBSITE_ID",
                      "key_length": "2",
                      "used_key_parts": ["website_id"],
                      "rows": 4,
                      "filtered": 100,
                      "attached_condition": "s.store_id <> 0",
                      "using_index": true
                    },
                    "block-nl-join": {
                      "table": {
                        "table_name": "cpe",
                        "access_type": "range",
                        "possible_keys": ["PRIMARY"],
                        "key": "PRIMARY",
                        "key_length": "4",
                        "used_key_parts": ["entity_id"],
                        "rows": 27,
                        "filtered": 100,
                        "attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
                        "using_index": true
                      },
                      "buffer_type": "flat",
                      "buffer_size": "64",
                      "join_type": "BNL"
                    },
                    "table": {
                      "table_name": "dd",
                      "access_type": "ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "4",
                      "used_key_parts": ["entity_id"],
                      "ref": ["magento.cpe.entity_id"],
                      "rows": 11,
                      "filtered": 38.742,
                      "index_condition": "dd.store_id = 0 and dd.attribute_id in (83,93,99,205,209,211,212)"
                    },
                    "table": {
                      "table_name": "ds",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": [
                        "magento.cpe.entity_id",
                        "magento.dd.attribute_id",
                        "magento.s.store_id"
                      ],
                      "rows": 1,
                      "filtered": 100,
                      "attached_condition": "trigcond(ds.`value` is not null or dd.`value` is not null)"
                    },
                    "table": {
                      "table_name": "d2d",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": ["magento.cpe.entity_id", "const", "const"],
                      "rows": 1,
                      "filtered": 9.2263,
                      "distinct": true
                    },
                    "table": {
                      "table_name": "d2s",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": [
                        "magento.d2d.entity_id",
                        "magento.d2d.attribute_id",
                        "magento.s.store_id"
                      ],
                      "rows": 1,
                      "filtered": 100,
                      "attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))",
                      "distinct": true
                    },
                    "table": {
                      "table_name": "pis",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": [
                        "magento.cpe.entity_id",
                        "magento.dd.attribute_id",
                        "magento.s.store_id"
                      ],
                      "rows": 1,
                      "filtered": 100,
                      "attached_condition": "trigcond((pis.`value` is not null or pis.value_id is null) and ifnull(pis.`value`,coalesce(ds.`value`,dd.`value`)) is not null)",
                      "distinct": true
                    }
                  }
                }
              },
              {
                "query_block": {
                  "select_id": 3,
                  "operation": "UNION",
                  "table": {
                    "table_name": "cpe",
                    "access_type": "range",
                    "possible_keys": ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": ["entity_id"],
                    "rows": 27,
                    "filtered": 100,
                    "attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
                    "using_index": true
                  },
                  "table": {
                    "table_name": "wd",
                    "access_type": "ref",
                    "possible_keys": [
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                    ],
                    "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                    "key_length": "4",
                    "used_key_parts": ["entity_id"],
                    "ref": ["magento.cpe.entity_id"],
                    "rows": 11,
                    "filtered": 7.4615,
                    "index_condition": "wd.attribute_id in (83,93,99,205,209,211,212) and wd.store_id <> 0",
                    "attached_condition": "wd.`value` is not null"
                  },
                  "table": {
                    "table_name": "d2d",
                    "access_type": "eq_ref",
                    "possible_keys": [
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                    ],
                    "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                    "key_length": "8",
                    "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                    "ref": ["magento.cpe.entity_id", "const", "const"],
                    "rows": 1,
                    "filtered": 9.2263
                  },
                  "table": {
                    "table_name": "d2s",
                    "access_type": "ref",
                    "possible_keys": [
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE"
                    ],
                    "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                    "key_length": "6",
                    "used_key_parts": ["entity_id", "attribute_id"],
                    "ref": ["magento.d2d.entity_id", "magento.d2d.attribute_id"],
                    "rows": 1,
                    "filtered": 50,
                    "attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(d2s.store_id <> 0 and trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))"
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}

EXPLAIN (with the extra index):

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "rows": 1485,
      "filtered": 100,
      "materialized": {
        "query_block": {
          "union_result": {
            "table_name": "<union2,3>",
            "access_type": "ALL",
            "query_specifications": [
              {
                "query_block": {
                  "select_id": 2,
                  "temporary_table": {
                    "table": {
                      "table_name": "s",
                      "access_type": "index",
                      "possible_keys": ["PRIMARY"],
                      "key": "STORE_WEBSITE_ID",
                      "key_length": "2",
                      "used_key_parts": ["website_id"],
                      "rows": 4,
                      "filtered": 100,
                      "attached_condition": "s.store_id <> 0",
                      "using_index": true
                    },
                    "block-nl-join": {
                      "table": {
                        "table_name": "cpe",
                        "access_type": "range",
                        "possible_keys": ["PRIMARY"],
                        "key": "PRIMARY",
                        "key_length": "4",
                        "used_key_parts": ["entity_id"],
                        "rows": 27,
                        "filtered": 100,
                        "attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
                        "using_index": true
                      },
                      "buffer_type": "flat",
                      "buffer_size": "64",
                      "join_type": "BNL"
                    },
                    "table": {
                      "table_name": "dd",
                      "access_type": "ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID",
                      "key_length": "4",
                      "used_key_parts": ["entity_id"],
                      "ref": ["magento.cpe.entity_id"],
                      "rows": 11,
                      "filtered": 36.51,
                      "attached_condition": "dd.store_id = 0 and dd.attribute_id in (83,93,99,205,209,211,212)"
                    },
                    "table": {
                      "table_name": "ds",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": [
                        "magento.cpe.entity_id",
                        "magento.dd.attribute_id",
                        "magento.s.store_id"
                      ],
                      "rows": 1,
                      "filtered": 100,
                      "attached_condition": "trigcond(ds.`value` is not null or dd.`value` is not null)"
                    },
                    "table": {
                      "table_name": "d2d",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": ["magento.cpe.entity_id", "const", "const"],
                      "rows": 1,
                      "filtered": 8.6947,
                      "distinct": true
                    },
                    "table": {
                      "table_name": "d2s",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": [
                        "magento.d2d.entity_id",
                        "magento.d2d.attribute_id",
                        "magento.s.store_id"
                      ],
                      "rows": 1,
                      "filtered": 100,
                      "attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))",
                      "distinct": true
                    },
                    "table": {
                      "table_name": "pis",
                      "access_type": "eq_ref",
                      "possible_keys": [
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                        "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                        "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                      ],
                      "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "key_length": "8",
                      "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                      "ref": [
                        "magento.cpe.entity_id",
                        "magento.dd.attribute_id",
                        "magento.s.store_id"
                      ],
                      "rows": 1,
                      "filtered": 100,
                      "attached_condition": "trigcond((pis.`value` is not null or pis.value_id is null) and ifnull(pis.`value`,coalesce(ds.`value`,dd.`value`)) is not null)",
                      "distinct": true
                    }
                  }
                }
              },
              {
                "query_block": {
                  "select_id": 3,
                  "operation": "UNION",
                  "table": {
                    "table_name": "cpe",
                    "access_type": "range",
                    "possible_keys": ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": ["entity_id"],
                    "rows": 27,
                    "filtered": 100,
                    "attached_condition": "cpe.entity_id in (653591,653603,653690,653697,653701,653708,653716,653728,653737,653748,653752,653764,653774,653775,689894,689895,689896,689897,689898,689899,689900,689901,689902,689903,689904,689905,689906)",
                    "using_index": true
                  },
                  "table": {
                    "table_name": "wd",
                    "access_type": "ref",
                    "possible_keys": [
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                    ],
                    "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID",
                    "key_length": "4",
                    "used_key_parts": ["entity_id"],
                    "ref": ["magento.cpe.entity_id"],
                    "rows": 11,
                    "filtered": 7.0315,
                    "attached_condition": "wd.attribute_id in (83,93,99,205,209,211,212) and wd.`value` is not null and wd.store_id <> 0"
                  },
                  "table": {
                    "table_name": "d2d",
                    "access_type": "eq_ref",
                    "possible_keys": [
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                    ],
                    "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                    "key_length": "8",
                    "used_key_parts": ["entity_id", "attribute_id", "store_id"],
                    "ref": ["magento.cpe.entity_id", "const", "const"],
                    "rows": 1,
                    "filtered": 8.6947
                  },
                  "table": {
                    "table_name": "d2s",
                    "access_type": "ref",
                    "possible_keys": [
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_STORE_ID",
                      "CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE",
                      "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID"
                    ],
                    "key": "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID",
                    "key_length": "6",
                    "used_key_parts": ["entity_id", "attribute_id"],
                    "ref": ["magento.d2d.entity_id", "magento.d2d.attribute_id"],
                    "rows": 1,
                    "filtered": 48.517,
                    "attached_condition": "trigcond(coalesce(d2s.`value`,d2d.`value`) = 1) and trigcond(d2s.store_id <> 0 and trigcond(d2d.entity_id is not null and d2d.attribute_id is not null))"
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}

CREATE TABLEs:

CREATE TABLE `catalog_product_entity` (
  `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute Set ID',
  `type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
  `sku` varchar(64) DEFAULT NULL COMMENT 'SKU',
  `has_options` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Has Options',
  `required_options` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Required Options',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Time',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Update Time',
  PRIMARY KEY (`entity_id`),
  KEY `CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
  KEY `CATALOG_PRODUCT_ENTITY_SKU` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=625336 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table'

CREATE TABLE `catalog_product_entity_int` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Attribute ID',
  `store_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Store ID',
  `entity_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Entity ID',
  `value` int(11) DEFAULT NULL COMMENT 'Value',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
  KEY `CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
  KEY `CATALOG_PRODUCT_ENTITY_INT_STORE_ID` (`store_id`),
  KEY `CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE` (`attribute_id`,`store_id`,`value`),
  CONSTRAINT `CATALOG_PRODUCT_ENTITY_INT_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_PRD_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
  CONSTRAINT `CAT_PRD_ENTT_INT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16823544 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table'
15
  • 3
    For questions like this, you should provide query plan information (the output of EXPLAIN for myself/mariadb) as it could include important details to explain what is going on. Sep 24 at 10:14
  • 1
    @DavidSpillett I'll provide the plan when I'm home. Sep 24 at 10:27
  • 1
    The index is not redundant. Can't add a proper answer from the phone, will add one later. Sep 24 at 11:08
  • 1
    Also what David said above. We need the EPLAIN output plus the SHOW CREATE TABLE output of all 3 tables, magic, X and O. Sep 24 at 11:11
  • 1
    side note, left join other O can be replaced with just join other O since the WHERE clause will filter everything coming from the "left" part Sep 24 at 11:30

2 Answers 2

0

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index.
...
A developer added a new index in the magic table on just (a) (which is a redundant one) and then, magic, the SELECT got substantial performance boost.

The index on (a) is not redundant.

Why the index (a) on an existing covering index (a,b,c) is not redundant? As far as I know the multicolumn index covers the left one. And it is mentioned on MariaDB Redundancy? That is, what if you have both of these: INDEX(a), INDEX(a,b)? Answer: Redundanccy costs something on INSERTs; it is rarely useful for SELECTs. Is this case one of those rarely cases when Redundancy index is useful on Selects?

The reason that is not redundant is related to these two facts:

The MariaDB 10.4 InnoDB table magic has an index on (a, b, c), the columns are INT, the PK is not part of this index.

The detail that is relevant here is that all InnoDB tables are clustered, i.e. rows are stored in an order defined by the clustering index. The clustering index is always the PK if there is one, otherwise the first UNIQUE index with non-nullable columns - and if the table has none of those, a hidden 6-byte index is created for this purpose.

Also, this clustering index is attached in every other (secondary) index.

So, going to your case - and as you assert the PK is not part of this index - let's say that the PK is based on some column: (magic_id). Your two indexes (a, b, c) and (a) are essentially and other the hood and for all purpose really (a, b, c, magic_id) and (a, magic_id).

Now you can see that if a query really needs just the a column values, the indexes are redundant, any of the two will find the values of a and in the same order.


But consider a query like:

SELECT a FROM magic ORDER BY a, magic_id ;

We can find the a and magic_id values from either index but only the second index on (a) will get the a values in the wanted order. So the second index is better for this query.

This example is likely not relevant to your case but we'll need the CREATE TABLE statements and the EXPLAIN plans to be sure. The example is certainly one case that MySQL/MariaDB developers had in mind when they added that "it is rarely useful for SELECTs" in the documentation.


There is however another small (or big!) catch, which is not due to InnoDB clustering. If the size of columns b or c are large, the size of the two indexes will be much different. In all cases - that I have encountered - that a query needs just the a column values, the optimizer will pick the index on (a) rather than the index on (a, b, c) because it knows that it will need to read much less blocks from disk/memory.

And this is likely another case that MySQL/MariaDB developers had in mind when they added that "it is rarely useful for SELECTs" in the documentation.


For your scenario, we can't be sure without the asked details (CREATE TABLE statements including indexes plus EXPLAIN plans) but I think it likely falls into the second case (small index size). The query has this join between M1 and O:

from ...
  left join magic M1 on ...
  ...
  left join other O on O.a = M1.a

and it's possible that some plan will need just the values of magic.a for the join with O and thus be faster with the smaller index.

4
  • This is all very interesting, but rely on the new index to be used instead: “However, the query plans from EXPLAIN remain the same before and after a new index has been added. Both use the index (a, b, c) only.” Sep 24 at 16:33
  • @AndrewSayer oh, I missed that part, thnx for commenting. It could be a case of caching then, when the query runs faster after the slower 1st time. I'll wait for the question to bed edited with plan. Sep 24 at 16:50
  • @AndrewSayer I've updated the post. The information of "the same EXPLAINs" was wrong. I was told they were the same by the person who run EXPLAINs and didn't check myself :/ Sep 26 at 12:49
  • 1
    I haven't checked all the details of the two plans but I believe this is a case os smaller size index, as per the 2nd part of my answer. The new index is used for the join between dd and cpe (ON cpe.entity_id = dd.entity_id) which needs only that column and not the whole three. Sep 26 at 14:46
0

Assuming each table has PRIMARY KEY(id) (which is unclear from the phrasing in the question), you effectively added

INDEX(a, id)

Meanwhile,

left join other O on O.a = M1.a

turns into

INNER join other O on O.a = M1.a

because of two things: the explicit WHERE O.a IN ... and the fact that the Optimizer can transfer that to M1.a.

Next, since (apparently) only O and M1 are in the WHERE clause, the Optimizer will start with M1 (and turn it into INNER). But now INDEX(a, id) is optimal because it needs to fetch things from M1 before going on to the rest or the tables. M2, on the other hand, will like the composite (a,b,c).

To provide more details and, I hope, confirm what I just said, please provide

EXPLAIN FORMAT=JSON SELECT ...

And please provide the CREATE TABLEs and the full SELECT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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