Why Mysql is not using indexes on join

Question

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

• Rick James: Please provide SHOW CREATE TABLE so we can see the indexes.

Answer 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.

• Vitalii Kravchenko: 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
• Rick James: environmen0_ can't use any of your indexes; it needs mine. The other table is too tiny (3 rows) to draw any useful conclusions.
• Vitalii Kravchenko: 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?
• Rick James: Please provide SHOW CREATE TABLE SPACES and EXPLAIN FORMAT=JSON SELECT ...;
• Andrew Brennan: 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.

Answer 2

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

2. 1=1 - remove it all, it a DUMMY condition and do nothing in Your query
3. 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

• Vitalii Kravchenko: 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
• a_vlad: 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.