0

I have a query that joins table SPACES with 3 rows. But MySql is not using indexes.

EXPLAIN SELECT
environmen0_.description        AS col_0_0_,
environmen0_.fk_user_profile_id AS col_1_0_,
environmen0_.id                 AS col_2_0_,
environmen0_.ukey               AS col_3_0_,
environmen0_.environment_name   AS col_4_0_,
lifecycle5_.state               AS col_6_0_,
environmen0_.updated_date       AS col_7_0_,
environmen0_.updated_by         AS col_8_0_,
usersecuri1_.id                 AS id1_238_,
usersecuri1_.primary_entity     AS primary_2_238_,
space4_.alias_name               AS type_alias


FROM ENVIRONMENTS environmen0_ 
  STRAIGHT_JOIN SPACES space4_ 
              ON environmen0_.fk_space_id = space4_.id
  STRAIGHT_JOIN USER_SECURITY_PROFILES usersecuri1_
              ON environmen0_.fk_user_profile_id = usersecuri1_.id
  STRAIGHT_JOIN SECURITY_ROLE_PERMISSIONS permission2_
              ON usersecuri1_.id = permission2_.fk_profile_id

WHERE
      permission2_.permission_read = 1
 AND (permission2_.fk_security_role_id IN (2, 1))
 AND 1 = 1 AND 1 = 1 AND 1 = 1
 AND 1 = 1
 AND (environmen0_.operation_scope = 'design'
   OR environmen0_.operation_scope = 'design')
 AND 1 = 1 AND 1 = 1
GROUP BY environmen0_.id
ORDER BY environmen0_.id ASC
LIMIT 25

How come that MySql refuses to use index? Join type is ALL, that problem leads that JOIN type for ENVIRONMENTS table will be ALL. If I remove from select this line

space4_.alias_name AS type_alias

Explain output explain output

The output from CREATE TABLE

CREATE TABLE `environments` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `alias_name` varchar(255) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `description` longtext,
  `ukey` varchar(255) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `namespace` varchar(255) DEFAULT NULL,
  `updated_date` datetime NOT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `vstamp` int(11) DEFAULT NULL,
  `builder_view` longtext,
  `environment_name` varchar(255) NOT NULL,
  `operation_scope` varchar(255) DEFAULT NULL,
  `fk_provision_profile_id` bigint(20) DEFAULT NULL,
  `fk_user_profile_id` bigint(20) DEFAULT NULL,
  `fk_mainApplication_id` bigint(20) DEFAULT NULL,
  `fk_organization_id` bigint(20) DEFAULT NULL,
  `fk_security_profile_id` bigint(20) NOT NULL,
  `fk_space_id` bigint(20) NOT NULL,
  `fk_state_id` bigint(20) DEFAULT NULL,
  `fk_environment_type` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ad600i5g7rewdxmuc1elmekdh` (`ukey`),
  KEY `FK_pxiie9k1gm297ejhr5y8k56a9` (`fk_provision_profile_id`),
  KEY `FK_35i1f8xibrr2apesdu9ahcpnt` (`fk_user_profile_id`),
  KEY `FK_b959faehk11qrxb06die67ukm` (`fk_mainApplication_id`),
  KEY `FK_554d71l9bovc8u804wghgyo38` (`fk_organization_id`),
  KEY `FK_bomv382tbq3rf4ae54ewkr8rs` (`fk_security_profile_id`),
  KEY `FK_9xype9ap7gcmdm5t077gdgor3` (`fk_space_id`),
  KEY `FK_th5888gw8704s2bfcw0r8hmr2` (`fk_state_id`),
  KEY `FK_aryfg8vfjdrhxrdxd3ykptsfq` (`fk_environment_type`),
  CONSTRAINT `FK_35i1f8xibrr2apesdu9ahcpnt` FOREIGN KEY (`fk_user_profile_id`) REFERENCES `user_security_profiles` (`id`),
  CONSTRAINT `FK_554d71l9bovc8u804wghgyo38` FOREIGN KEY (`fk_organization_id`) REFERENCES `organizations` (`id`),
  CONSTRAINT `FK_9xype9ap7gcmdm5t077gdgor3` FOREIGN KEY (`fk_space_id`) REFERENCES `spaces` (`id`),
  CONSTRAINT `FK_aryfg8vfjdrhxrdxd3ykptsfq` FOREIGN KEY (`fk_environment_type`) REFERENCES `environment_types` (`id`),
  CONSTRAINT `FK_b959faehk11qrxb06die67ukm` FOREIGN KEY (`fk_mainApplication_id`) REFERENCES `services` (`id`),
  CONSTRAINT `FK_bomv382tbq3rf4ae54ewkr8rs` FOREIGN KEY (`fk_security_profile_id`) REFERENCES `security_profiles` (`id`),
  CONSTRAINT `FK_pxiie9k1gm297ejhr5y8k56a9` FOREIGN KEY (`fk_provision_profile_id`) REFERENCES `provision_profiles` (`id`),
  CONSTRAINT `FK_th5888gw8704s2bfcw0r8hmr2` FOREIGN KEY (`fk_state_id`) REFERENCES `lifecycles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3686 DEFAULT CHARSET=utf8

SHOW CREATE TABLE SPACES OUTPUT

CREATE TABLE `spaces` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `alias_name` varchar(255) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `description` longtext,
  `ukey` varchar(255) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `namespace` varchar(255) DEFAULT NULL,
  `updated_date` datetime NOT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `vstamp` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `registered` bit(1) NOT NULL,
  `computeGroup_id` bigint(20) DEFAULT NULL,
  `fk_datacenter_id` bigint(20) DEFAULT NULL,
  `fk_environment_type_id` bigint(20) DEFAULT NULL,
  `fk_firewall_details_id` bigint(20) DEFAULT NULL,
  `fk_user_profile_id` bigint(20) DEFAULT NULL,
  `fk_infrastructure_space_id` bigint(20) DEFAULT NULL,
  `fk_network_details_id` bigint(20) DEFAULT NULL,
  `networkGroup_id` bigint(20) DEFAULT NULL,
  `fk_organization_id` bigint(20) DEFAULT NULL,
  `fk_space_type_id` bigint(20) DEFAULT NULL,
  `fk_state_id` bigint(20) DEFAULT NULL,
  `storageGroup_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_sjlchubxlbvgi5ulks9wp9sde` (`ukey`),
  UNIQUE KEY `spaces_name_uindex` (`name`),
  KEY `FK_9nvkdrx0f46h75d6sa5oy6iwr` (`computeGroup_id`),
  KEY `FK_ejq2ibt89frkg86smcrmhyt5v` (`fk_datacenter_id`),
  KEY `FK_6ajs9lyo7mjmjjj4y3qjsc31g` (`fk_environment_type_id`),
  KEY `FK_7hm1q5w10wt88uxnhhgprxb` (`fk_firewall_details_id`),
  KEY `FK_bato9w3gb8niubughim3co8ep` (`fk_user_profile_id`),
  KEY `FK_ilpdrrgxshsrd3vfx3xtdfegx` (`fk_infrastructure_space_id`),
  KEY `FK_4lstxys6ovkiut13ja3r5rcpd` (`fk_network_details_id`),
  KEY `FK_n1633yw9h1jfv1wpa8hhtmafa` (`networkGroup_id`),
  KEY `FK_l9pbefokdexmg051uxucrue2i` (`fk_organization_id`),
  KEY `FK_gyvfscvcyyvwpxugo8sbcv7mc` (`fk_space_type_id`),
  KEY `FK_jjhvvr4g3v55iylkh54mee86d` (`fk_state_id`),
  KEY `FK_v4q19t54g2ecqslfdyit0gn7` (`storageGroup_id`),
  CONSTRAINT `FK_4lstxys6ovkiut13ja3r5rcpd` FOREIGN KEY (`fk_network_details_id`) REFERENCES `component_details` (`id`),
  CONSTRAINT `FK_6ajs9lyo7mjmjjj4y3qjsc31g` FOREIGN KEY (`fk_environment_type_id`) REFERENCES `environment_types` (`id`),
  CONSTRAINT `FK_7hm1q5w10wt88uxnhhgprxb` FOREIGN KEY (`fk_firewall_details_id`) REFERENCES `component_details` (`id`),
  CONSTRAINT `FK_9nvkdrx0f46h75d6sa5oy6iwr` FOREIGN KEY (`computeGroup_id`) REFERENCES `compute_groups` (`id`),
  CONSTRAINT `FK_bato9w3gb8niubughim3co8ep` FOREIGN KEY (`fk_user_profile_id`) REFERENCES `user_security_profiles` (`id`),
  CONSTRAINT `FK_ejq2ibt89frkg86smcrmhyt5v` FOREIGN KEY (`fk_datacenter_id`) REFERENCES `data_centers` (`id`),
  CONSTRAINT `FK_gyvfscvcyyvwpxugo8sbcv7mc` FOREIGN KEY (`fk_space_type_id`) REFERENCES `space_type` (`id`),
  CONSTRAINT `FK_ilpdrrgxshsrd3vfx3xtdfegx` FOREIGN KEY (`fk_infrastructure_space_id`) REFERENCES `infrastructure_spaces` (`id`),
  CONSTRAINT `FK_jjhvvr4g3v55iylkh54mee86d` FOREIGN KEY (`fk_state_id`) REFERENCES `lifecycles` (`id`),
  CONSTRAINT `FK_l9pbefokdexmg051uxucrue2i` FOREIGN KEY (`fk_organization_id`) REFERENCES `organizations` (`id`),
  CONSTRAINT `FK_n1633yw9h1jfv1wpa8hhtmafa` FOREIGN KEY (`networkGroup_id`) REFERENCES `network_groups` (`id`),
  CONSTRAINT `FK_v4q19t54g2ecqslfdyit0gn7` FOREIGN KEY (`storageGroup_id`) REFERENCES `storage_groups` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

EXPLAIN OUTPUT FORMAT JSON

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5401.30"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "nested_loop": [
          {
            "table": {
              "table_name": "environmen0_",
              "access_type": "ALL",
              "possible_keys": [
                "PRIMARY",
                "UK_ad600i5g7rewdxmuc1elmekdh",
                "FK_pxiie9k1gm297ejhr5y8k56a9",
                "FK_35i1f8xibrr2apesdu9ahcpnt",
                "FK_b959faehk11qrxb06die67ukm",
                "FK_554d71l9bovc8u804wghgyo38",
                "FK_bomv382tbq3rf4ae54ewkr8rs",
                "FK_9xype9ap7gcmdm5t077gdgor3",
                "FK_th5888gw8704s2bfcw0r8hmr2",
                "FK_aryfg8vfjdrhxrdxd3ykptsfq"
              ],
              "rows_examined_per_scan": 3026,
              "rows_produced_per_join": 574,
              "filtered": "19.00",
              "cost_info": {
                "read_cost": "3517.21",
                "eval_cost": "114.99",
                "prefix_cost": "3632.20",
                "data_read_per_join": "3M"
              },
              "used_columns": [
                "id",
                "description",
                "ukey",
                "updated_date",
                "updated_by",
                "environment_name",
                "operation_scope",
                "fk_user_profile_id",
                "fk_space_id"
              ],
              "attached_condition": "(((`p3c`.`environmen0_`.`operation_scope` = 'design') or (`p3c`.`environmen0_`.`operation_scope` = 'design')) and ((`p3c`.`environmen0_`.`fk_user_profile_id` is not null) and (`p3c`.`environmen0_`.`fk_user_profile_id` is not null)))"
            }
          },
          {
            "table": {
              "table_name": "space4_",
              "access_type": "ALL",
              "possible_keys": [
                "PRIMARY"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 574,
              "filtered": "33.33",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "1.27",
                "eval_cost": "114.99",
                "prefix_cost": "3978.44",
                "data_read_per_join": "3M"
              },
              "used_columns": [
                "id",
                "alias_name"
              ],
              "attached_condition": "(`p3c`.`space4_`.`id` = `p3c`.`environmen0_`.`fk_space_id`)"
            }
          },
          {
            "table": {
              "table_name": "usersecuri1_",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "id"
              ],
              "key_length": "8",
              "ref": [
                "p3c.environmen0_.fk_user_profile_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 574,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "574.94",
                "eval_cost": "114.99",
                "prefix_cost": "4668.37",
                "data_read_per_join": "440K"
              },
              "used_columns": [
                "id",
                "primary_entity"
              ]
            }
          },
          {
            "table": {
              "table_name": "permission2_",
              "access_type": "ref",
              "possible_keys": [
                "UK_6xoibydyrd0ygb48fin59dqpi",
                "FK_9aqihyu0wbjupml42sqqs6nj5"
              ],
              "key": "UK_6xoibydyrd0ygb48fin59dqpi",
              "used_key_parts": [
                "fk_profile_id"
              ],
              "key_length": "8",
              "ref": [
                "p3c.environmen0_.fk_user_profile_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 287,
              "filtered": "47.08",
              "index_condition": "(`p3c`.`permission2_`.`fk_security_role_id` in (2,1))",
              "cost_info": {
                "read_cost": "610.78",
                "eval_cost": "57.51",
                "prefix_cost": "5401.30",
                "data_read_per_join": "224K"
              },
              "used_columns": [
                "id",
                "permission_read",
                "fk_profile_id",
                "fk_security_role_id"
              ],
              "attached_condition": "(`p3c`.`permission2_`.`permission_read` = 1)"
            }
          }
        ]
      }
    }
  }
}

Indexes for join table will be applied and ENVIRONMENT TABLE will not obtain all records. Could someone explain the behavior? What should I do in this case?

MODIFIED QUERY, after all discussions

Added indexes for Environment and SECURITY_ROLE_PERMISSIONS table, but performance is not changes

EXPLAIN SELECT
  environmen0_.description        AS col_0_0_,
  environmen0_.fk_user_profile_id AS col_1_0_,
  environmen0_.id                 AS col_2_0_,
  environmen0_.ukey               AS col_3_0_,
  environmen0_.environment_name   AS col_4_0_,
  environmen0_.updated_date       AS col_7_0_,
  environmen0_.updated_by         AS col_8_0_,
  usersecuri1_.id                 AS id1_238_,
  usersecuri1_.primary_entity     AS primary_2_238_,
  space4_.alias_name               AS type_alias


FROM ENVIRONMENTS environmen0_
  JOIN SPACES space4_  ON environmen0_.fk_space_id = space4_.id
  JOIN USER_SECURITY_PROFILES usersecuri1_ ON environmen0_.fk_user_profile_id = usersecuri1_.id
  JOIN SECURITY_ROLE_PERMISSIONS permission2_ ON usersecuri1_.id = permission2_.fk_profile_id
WHERE
      permission2_.permission_read = 1 AND (permission2_.fk_security_role_id IN (2, 1)) AND
      environmen0_.operation_scope = 'design'

GROUP BY environmen0_.id
ORDER BY environmen0_.id ASC
LIMIT 25

The EXPLAIN JSON output for edited query

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7475.71"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "cost_info": {
          "sort_cost": "1685.00"
        },
        "nested_loop": [
          {
            "table": {
              "table_name": "permission2_",
              "access_type": "index",
              "possible_keys": [
                "UK_6xoibydyrd0ygb48fin59dqpi",
                "FK_9aqihyu0wbjupml42sqqs6nj5",
                "security_index"
              ],
              "key": "security_index",
              "used_key_parts": [
                "fk_profile_id",
                "permission_read",
                "fk_security_role_id"
              ],
              "key_length": "18",
              "rows_examined_per_scan": 3579,
              "rows_produced_per_join": 1684,
              "filtered": "47.08",
              "using_index": true,
              "cost_info": {
                "read_cost": "391.80",
                "eval_cost": "337.00",
                "prefix_cost": "728.80",
                "data_read_per_join": "1M"
              },
              "used_columns": [
                "id",
                "permission_read",
                "fk_profile_id",
                "fk_security_role_id"
              ],
              "attached_condition": "((`p3c`.`permission2_`.`permission_read` = 1) and (`p3c`.`permission2_`.`fk_security_role_id` in (2,1)))"
            }
          },
          {
            "table": {
              "table_name": "environmen0_",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "UK_ad600i5g7rewdxmuc1elmekdh",
                "FK_pxiie9k1gm297ejhr5y8k56a9",
                "FK_35i1f8xibrr2apesdu9ahcpnt",
                "FK_b959faehk11qrxb06die67ukm",
                "FK_554d71l9bovc8u804wghgyo38",
                "FK_bomv382tbq3rf4ae54ewkr8rs",
                "FK_9xype9ap7gcmdm5t077gdgor3",
                "FK_th5888gw8704s2bfcw0r8hmr2",
                "FK_aryfg8vfjdrhxrdxd3ykptsfq",
                "environments_operation_scope_id_index"
              ],
              "key": "FK_35i1f8xibrr2apesdu9ahcpnt",
              "used_key_parts": [
                "fk_user_profile_id"
              ],
              "key_length": "9",
              "ref": [
                "p3c.permission2_.fk_profile_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1684,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1685.00",
                "eval_cost": "337.00",
                "prefix_cost": "2750.80",
                "data_read_per_join": "10M"
              },
              "used_columns": [
                "id",
                "description",
                "ukey",
                "updated_date",
                "updated_by",
                "environment_name",
                "operation_scope",
                "fk_user_profile_id",
                "fk_space_id"
              ],
              "attached_condition": "(`p3c`.`environmen0_`.`operation_scope` = 'design')"
            }
          },
          {
            "table": {
              "table_name": "usersecuri1_",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "id"
              ],
              "key_length": "8",
              "ref": [
                "p3c.permission2_.fk_profile_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1684,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1685.00",
                "eval_cost": "337.00",
                "prefix_cost": "4772.80",
                "data_read_per_join": "1M"
              },
              "used_columns": [
                "id",
                "primary_entity"
              ]
            }
          },
          {
            "table": {
              "table_name": "space4_",
              "access_type": "ALL",
              "possible_keys": [
                "PRIMARY"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 1685,
              "filtered": "33.33",
              "using_join_buffer": "Block Nested Loop",
              "cost_info": {
                "read_cost": "6.91",
                "eval_cost": "337.00",
                "prefix_cost": "5790.71",
                "data_read_per_join": "8M"
              },
              "used_columns": [
                "id",
                "alias_name"
              ],
              "attached_condition": "(`p3c`.`space4_`.`id` = `p3c`.`environmen0_`.`fk_space_id`)"
            }
          }
        ]
      }
    }
  }
}

Thanks

  • 1
    Please provide SHOW CREATE TABLE so we can see the indexes. – Rick James Feb 13 '17 at 19:08
1

Get rid of STRAIGHT_JOIN

Get rid of AND 1=1

Don't OR together identical clauses, just say it once:

AND (environmen0_.operation_scope = 'design' OR environmen0_.operation_scope = 'design')

Indexes needed:

environmen0_:  INDEX(operation_scope, id)
permission2_:  INDEX(fk_profile_id, permission_read, fk_security_role_id)

When a table has only 3 rows, it does not matter if it uses an index. As the table grows, or the parameters change, the Optimizer may pick a different index to use.

  • My Environment table has 3000 rows, and table SPACES has 3 rows, but indexes and not used. I've added explain output and out from CREATE TABLE – Vitalii Kravchenko Feb 13 '17 at 21:54
  • environmen0_ can't use any of your indexes; it needs mine. The other table is too tiny (3 rows) to draw any useful conclusions. – Rick James Feb 13 '17 at 22:55
  • If table is tiny it cannot use index for join? If I remove the last select field, MySQL starts using indexes. Why it cannot use fk_spaceid? – Vitalii Kravchenko Feb 13 '17 at 23:03
  • Please provide SHOW CREATE TABLE SPACES and EXPLAIN FORMAT=JSON SELECT ...; – Rick James Feb 13 '17 at 23:37
  • 1
    The database will only use an index if it makes sense to do so. For a table with only three rows, a full scan is almost always quicker. – Andrew Brennan Feb 14 '17 at 10:25
1

In addition to Rick James answer

  1. what is it STRAIGHT_JOIN?:

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

So, before use index for fk_space_id, MySQL will read ENVIRONMENTS first and there You have only 1 filter condition:

AND (environmen0_.operation_scope = 'design'
   OR environmen0_.operation_scope = 'design')

operation_scope - not indexed ... mean == FULL SCAN

  1. 1=1 - remove it all, it a DUMMY condition and do nothing in Your query
  2. If You change STRAIGHT_JOIN to the normal JOIN, MySQL could or couldn't change reading order. If it decide change it - Your expected index start work. Choose or not - depends from many unclean parameters,

based on all of this - create index for (operation_scope, id) - will be best choice

  • I applied you changes, but it gives we the same output from EXPLAIN, access type for environment is ALL, and access type for space is ALL. It Selects 3026 * 3 rows. I'd like to reduce them. tnx – Vitalii Kravchenko Feb 14 '17 at 8:56
  • Hi @VitaliiKravchenko, from plans - it different, may be same speed, but this could depend from data. If interesting - send all tables from query structure and datasets for tests. about 3 rows - all comments above correct, no reason for use index, about other tables need to check why. – a_vlad Feb 14 '17 at 11:12

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.