Query results in a strange explain output

Question

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this.

'1', 'SIMPLE', 'mount_adapters', 'index', NULL, 'mount_product_ID_2', '10', NULL, '15841', 'Using index; Using join buffer'

So this is what slowing the query down. The table consists of 1 primary key, 2 ints and a varchar(2) but I have 4 indexes on the table.

It does not seem to be using the index (mount_product_ID_2) even though it says it is. What's confusing is that it says that there are no possible keys to use but then tries to use the index.

Can anyone explain this?

Code Update

Table: mount_adapters
Create Table: CREATE TABLE `mount_adapters` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `mount_product_ID` int(11) NOT NULL,
  `adapter_product_ID` int(11) NOT NULL,
  `relationship_type` varchar(2) DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `mount_product_ID` (`mount_product_ID`),
  KEY `adapter_product_ID` (`adapter_product_ID`),
  KEY `mount_product_ID_2` (`mount_product_ID`,`adapter_product_ID`),
  KEY `adapter_product_ID_2` (`adapter_product_ID`,`mount_product_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16014 DEFAULT CHARSET=utf8

Query

SELECT
  SQL_CALC_FOUND_ROWS
  DISTINCT (
    IF (
      dependent_mounts.`ID`,
      dependent_mounts.`ID`,
      independent_mounts.`ID`
    )
  ) AS 'product_ID'
FROM (
  `product_av_models`,
  `mount_adapters`
)

LEFT JOIN `av_models` bp ON (
  bp.`ID` = `product_av_models`.`av_model_ID`
)

LEFT JOIN `products` independent_mounts ON (
  independent_mounts.`ID` = `product_av_models`.`product_ID` AND
  independent_mounts.`type_ID` != 11
)

LEFT JOIN `product_countries` independent_mounts_product_countries ON (
  independent_mounts_product_countries.`product_ID` = independent_mounts.`ID`
)
LEFT JOIN `countries` independent_mounts_countries ON (
  independent_mounts_countries.`ID` = independent_mounts_product_countries.`country_ID`
)

LEFT JOIN `products` adapters ON (
  adapters.`ID` = `product_av_models`.`product_ID` AND
  adapters.`type_ID` = 11
)

LEFT JOIN `product_countries` adapters_product_countries ON (
  adapters_product_countries.`product_ID` = adapters.`ID`
)
LEFT JOIN `countries` adapters_countries ON (
  adapters_countries.`ID` = adapters_product_countries.`country_ID`
  AND adapters_countries.`code` = 'US'
)

LEFT JOIN `products` dependent_mounts ON (
  dependent_mounts.`ID` = `mount_adapters`.`mount_product_ID` AND
  `mount_adapters`.`adapter_product_ID` = adapters.`ID`
)

LEFT JOIN `product_countries` dependent_mounts_product_countries ON (
  dependent_mounts_product_countries.`product_ID` = dependent_mounts.`ID`
)
LEFT JOIN `countries` dependent_mounts_countries ON (
  dependent_mounts_countries.`ID` = dependent_mounts_product_countries.`country_ID`
  AND dependent_mounts_countries.`code` = 'US'
)

LEFT JOIN `products_spec_data` dependent_mounts_spec_size_min ON (
  dependent_mounts_spec_size_min.`products_spec_name_id` = 12 AND
  dependent_mounts_spec_size_min.`products_id` = dependent_mounts.`ID`
)
LEFT JOIN `products_spec_data` dependent_mounts_spec_size_max ON (
  dependent_mounts_spec_size_max.`products_spec_name_id` = 13 AND
  dependent_mounts_spec_size_max.`products_id` = dependent_mounts.`ID`
)
LEFT JOIN `products_spec_data` dependent_mounts_spec_weight ON (
  dependent_mounts_spec_weight.`products_spec_name_id` = 25 AND
  dependent_mounts_spec_weight.`products_id` = dependent_mounts.`ID`
)

WHERE `product_av_models`.`av_model_ID` = 12536

AND (
    independent_mounts.`ID` IS NULL
    OR (
            independent_mounts.`group` = 'F'
        AND independent_mounts.`active` = 'T'
        AND independent_mounts.`display_mountfinder` = 'T'
        AND independent_mounts_countries.`code` = 'US'
    )
)

AND (
    adapters.`ID` IS NULL
    OR (
            adapters.`group` = 'F'
        AND adapters.`active` = 'T'
        AND adapters_countries.`code` = 'US'
    )
)

AND (
    -- IS EXIST here maybe
    dependent_mounts.`ID` IS NULL
    OR (
            dependent_mounts.`group` = 'F'
        AND dependent_mounts.`active` = 'T'
        AND dependent_mounts.`display_mountfinder` = 'T'
        AND dependent_mounts_countries.`code` = 'US'
        AND dependent_mounts_spec_size_min.`value` > 0
        AND dependent_mounts_spec_size_min.`value` <= bp.`screen_size`
        AND dependent_mounts_spec_size_max.`value` > 0
        AND dependent_mounts_spec_size_max.`value` >= bp.`screen_size`
        AND dependent_mounts_spec_weight.`value` > 0
        AND dependent_mounts_spec_weight.`value` >= bp.`screen_weight`
    )
)
ORDER BY
  independent_mounts.`name` ASC,
  dependent_mounts.`name` ASC,
  adapters.`name` ASC

Answer 1

Since you didn't provide a full explain it's hard to tell what exactly is slowing your query down. However I can try to explain what the EXPLAIN output you provided means.

According to MySQL EXPLAIN Join types the index join type means that the query scans the whole index "for each combination of rows from the previous tables". In other words it is doing a full index scan, which might be not good.

Using Index in Extra column of EXPLAIN, documented in MySQL EXPLAIN Join types, means that the table data (i.e. the MYD file in case of MyISAM tables) is not read at all, because the columns contained in the index include all data needed by the query.

The NULL in the key column does mean that the index is not used for looking up rows.

This means that the query is really walking the whole index tree, though I doubt that reading 15k rows from index might be the real problem with your query.