Eliminating Full Table Scan

[oddz]

I have the following query:

SELECT
			      d.id display_id
 
			      ,d.human_name
			      ,d.system_name
 
			      ,c.id field_id
			      ,c.path field_path
			      ,c.sortable field_sortable
			      ,c.editable field_editable
			      ,c.removed field_removed
 
			      ,co.id field_option_id
			      ,co.option_name field_option_name  
			      ,CASE
			          WHEN co.value_field_id IS NOT NULL
			          THEN co.value_field_id
 
			          WHEN co.value_argument_id IS NOT NULL
			          THEN co.value_argument_id
 
			          ELSE
			          co.value_static
			      END field_option_value		      
			      ,CASE
			          WHEN co.value_field_id IS NOT NULL
			          THEN 'field'
 
			          WHEN co.value_argument_id IS NOT NULL
			          THEN 'argument'
 
			          ELSE
			          'static'
			      END field_option_type
 
			      ,f.id filter_id
			      ,f.path filter_path
			      ,f.comparision filter_comparision
			      ,f.conditional filter_conditional
			      ,f.wildcard filter_wildcard
			      ,f.regex filter_regex
			      ,f.removed filter_removed
 
			      ,fv.id filter_value_id
			      ,CASE
			          WHEN fv.value_field_id IS NOT NULL
			          THEN fv.value_field_id
 
			          WHEN fv.value_argument_id IS NOT NULL
			          THEN fv.value_argument_id
 
			          ELSE
			          fv.value_static
			      END filter_value_value      
			      ,CASE
			          WHEN fv.value_field_id IS NOT NULL
			          THEN 'field'
 
			          WHEN fv.value_argument_id IS NOT NULL
			          THEN 'argument'
 
			          ELSE
			          'static'
			      END field_value_type
			      ,fv.wildcard filter_wildcard
			      ,fv.regex filter_regex
 
			      ,s.id sorting_id
			      ,s.path sorting_path
			      ,s.ordering sorting_order
			      ,s.priority sorting_priority
			      ,s.removed sorting_removed
 
			      ,sp.id sorting_priority_id
			      ,CASE
			          WHEN sp.value_field_id IS NOT NULL
			          THEN sp.value_field_id
 
			          WHEN sp.value_argument_id IS NOT NULL
			          THEN sp.value_argument_id
 
			          ELSE
			          sp.value_static
			      END sorting_priority_value  
			      ,CASE
			          WHEN sp.value_field_id IS NOT NULL
			          THEN 'field'
 
			          WHEN sp.value_argument_id IS NOT NULL
			          THEN 'argument'
 
			          ELSE
			          'static'
			      END sorting_priority_type
			      ,sp.weight sorting_priority_weight
 
			  FROM
			      MCP_VD_DISPLAYS d
			  LEFT OUTER
			  JOIN
			      MCP_VD_FIELDS c
			    ON
			      d.id = c.displays_id
			   AND
			      c.active = 1
			  LEFT OUTER
			  JOIN
			      MCP_VD_FIELD_OPTIONS co
			    ON
			      c.id = co.fields_id
			   AND
			      co.active = 1
			  LEFT OUTER
			  JOIN
			      MCP_VD_FILTERS f
			    ON
			      d.id = f.displays_id
			   AND
			      f.active = 1
			  LEFT OUTER
			  JOIN
			     MCP_VD_FILTER_VALUES fv
			    ON
			     f.id = fv.filters_id
			   AND
			     fv.active = 1
			  LEFT OUTER
			  JOIN
			     MCP_VD_SORTING s 
			    ON
			     d.id = s.displays_id
			   AND
			     s.active = 1
			  LEFT OUTER
			  JOIN
			     MCP_VD_SORTING_PRIORITY sp
			    ON
			     s.id = sp.sorting_id
			   AND
			     sp.active = 1 
			 WHERE
			     d.id = 1

Indexes have been defined on the following tables:

MCP_VD_FIELDS
- INDEX(displays_id,active)

MCP_VD_FIELD_OPTIONS
- INDEX(fields_id, active)

MCP_VD_FILTERS
- INDEX(displays_id,active)

MCP_VD_FILTER_VALUES
- INDEX(filters_id,active)

MCP_VD_SORTING
- INDEX(displays_id,active)

MCP_VD_SORTING_PRIORITY
- INDEX(sorting_id,active)

The primary key for every table is the id column.

Yet, a full table scan is occurring as seen in the attached image.

All tables besides MCP_VD_FILTER_VALUES (fv) seems to be referenced, not sure why that is though because there is an index on the join columns. Any ideas how to remove the full table scan making it a reference like the rest?

* If the tables are needed I can post those, though I think posting the indexes should be enough.

Thanks

[oddz]

Apparently it has something to do with the fields that are being selected. Still need to investigate further. However, removing all column selections besides for the display id yields the desired explain result.

SELECT
                  d.id display_id
 
              FROM
                  MCP_VD_DISPLAYS d
              LEFT OUTER
              JOIN
                  MCP_VD_FIELDS c
                ON
                  d.id = c.displays_id
               AND
                  c.active = 1
              LEFT OUTER
              JOIN
                  MCP_VD_FIELD_OPTIONS co
                ON
                  c.id = co.fields_id
               AND
                  co.active = 1
              LEFT OUTER
              JOIN
                  MCP_VD_FILTERS f
                ON
                  d.id = f.displays_id
               AND
                  f.active = 1
              LEFT OUTER
              JOIN
                 MCP_VD_FILTER_VALUES fv
                ON
                 f.id = fv.filters_id
               AND
                 fv.active = 1
              LEFT OUTER
              JOIN
                 MCP_VD_SORTING s
                ON
                 d.id = s.displays_id
               AND
                 s.active = 1
              LEFT OUTER
              JOIN
                 MCP_VD_SORTING_PRIORITY sp
                ON
                 s.id = sp.sorting_id
               AND
                 sp.active = 1
             WHERE
                 d.id = 1

[oddz]

Perhaps I'm not searching via the correct terminology but this one has me stumped. It seems that selecting any columns within fv results in a full table scan. When I remove all selections for that table from the query reference is used instead. Which is unexpected considering the other tables behave as expected using the reference rather than full table scan.

This is the create table for MCP_VD_FILTER_VALUES (fv)

CREATE TABLE `mcp_vd_filter_values` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `creators_id` BIGINT(20) UNSIGNED NOT NULL,
  `filters_id` BIGINT(20) UNSIGNED NOT NULL,
  `value_static` text,
  `value_argument_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `value_field_id` BIGINT(20) UNSIGNED DEFAULT NULL,
  `wildcard` enum('%s%','%s','s%') DEFAULT NULL,
  `regex` VARCHAR(255) DEFAULT NULL,
  `updated_on_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_on_timestamp` TIMESTAMP NULL DEFAULT NULL,
  `active` tinyint(3) UNSIGNED DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `filters_id` (`filters_id`,`active`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

An index is clearly defined on filters_id and active so I don't see why its not being utilized.