mysql> select @@version,@@version_comment\G *************************** 1. row *************************** @@version: 5.6.23-log @@version_comment: MySQL Community Server (GPL) 1 row in set (0.00 sec) mysql> show variables like 'opti%'\G *************************** 1. row *************************** Variable_name: optimizer_prune_level Value: 0 *************************** 2. row *************************** Variable_name: optimizer_search_depth Value: 62 *************************** 3. row *************************** Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on *************************** 4. row *************************** Variable_name: optimizer_trace Value: enabled=off,one_line=off *************************** 5. row *************************** Variable_name: optimizer_trace_features Value: greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on *************************** 6. row *************************** Variable_name: optimizer_trace_limit Value: 1 *************************** 7. row *************************** Variable_name: optimizer_trace_max_mem_size Value: 16384 *************************** 8. row *************************** Variable_name: optimizer_trace_offset Value: -1 8 rows in set (0.00 sec) CREATE TABLE `table1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `created_on` datetime NOT NULL, `created_by` varchar(255) NOT NULL, `updated_on` datetime DEFAULT NULL, `updated_by` varchar(255) DEFAULT NULL, `lock_version` int(11) DEFAULT NULL, `_uuid_` varchar(36) NOT NULL, `tenant_id` bigint(20) DEFAULT NULL, `description` varchar(1000) DEFAULT NULL, `actual_close_date` date DEFAULT NULL, `owner_id` bigint(20) DEFAULT NULL, `contact_id` bigint(20) DEFAULT NULL, `status_type_id` bigint(20) DEFAULT NULL, `table2_id` bigint(20) DEFAULT NULL, `name` varchar(255) NOT NULL, `status` int(11) DEFAULT NULL, `search_custom_fields` varchar(255) DEFAULT NULL, `last_contacted_on` datetime DEFAULT NULL, `deleted` bit(1) DEFAULT b'0', PRIMARY KEY (`id`), UNIQUE KEY `_uuid_` (`_uuid_`), KEY `eer3` (`contact_id`), KEY `sf34` (`table2_id`), KEY `gfgsd3` (`status_type_id`), KEY `gds4` (`owner_id`), KEY `tenant` (`tenant_id`), KEY `search_table1` (`tenant_id`,`name`), KEY `last_contacted_on_table1` (`tenant_id`,`last_contacted_on`), KEY `tenant_deleted_name` (`tenant_id`,`deleted`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `table2` ( `type` varchar(31) NOT NULL, `id` bigint(20) NOT NULL AUTO_INCREMENT, `created_on` datetime NOT NULL, `created_by` varchar(255) NOT NULL, `updated_on` datetime DEFAULT NULL, `updated_by` varchar(255) DEFAULT NULL, `lock_version` int(11) DEFAULT NULL, `_uuid_` varchar(36) NOT NULL, `tenant_id` bigint(20) DEFAULT NULL, `search_geographical_addr` varchar(255) DEFAULT NULL, `search_electronic_addr` varchar(255) DEFAULT NULL, `search_phone_number` varchar(255) DEFAULT NULL, `search_formatted_name` varchar(255) DEFAULT NULL, `household_name` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `first_name` varchar(255) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, `middle_names` varchar(255) DEFAULT NULL, `date_of_birth` datetime DEFAULT NULL, `gender` int(11) DEFAULT NULL, `job_title` varchar(255) DEFAULT NULL, `owner_user_id` bigint(20) DEFAULT NULL, `title_id` bigint(20) DEFAULT NULL, `parent_organisation_id` bigint(20) DEFAULT NULL, `search_field` varchar(255) DEFAULT NULL, `search_custom_fields` varchar(255) DEFAULT NULL, `deleted` bit(1) DEFAULT NULL, `about` text, `import_id` bigint(20) DEFAULT NULL, `last_contacted_on` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `_uuid_` (`_uuid_`), KEY `fsdfsdfs` (`parent_organisation_id`), KEY `eeefsacvsds` (`owner_user_id`), KEY `vvcvxs` (`title_id`), KEY `tenant` (`tenant_id`,`deleted`), KEY `search_org` (`tenant_id`,`deleted`,`name`), KEY `search_partial_match` (`tenant_id`,`deleted`,`search_field`), KEY `search_name` (`tenant_id`,`deleted`,`search_formatted_name`), KEY `search_name_by_type` (`tenant_id`,`deleted`,`type`,`search_formatted_name`), KEY `last_contacted_on_table2` (`tenant_id`,`last_contacted_on`), KEY `search_updated_on` (`tenant_id`,`deleted`,`updated_on`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `table3` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(35) NOT NULL, `hashed_password` varchar(255) DEFAULT NULL, `locale` varchar(255) DEFAULT NULL, `timeZone` varchar(255) DEFAULT NULL, `drivepath` varchar(255) DEFAULT NULL, `last_login` datetime DEFAULT NULL, `previous_login` datetime DEFAULT NULL, `invite_code` varchar(36) DEFAULT NULL, `access_location_id` bigint(20) DEFAULT NULL, `physical_location_id` bigint(20) DEFAULT NULL, `status_type_id` bigint(20) DEFAULT NULL, `status` int(11) DEFAULT NULL, `ip_address` varchar(255) DEFAULT NULL, `open_id` varchar(255) DEFAULT NULL, `api_key` varchar(255) DEFAULT NULL, `feed_key` varchar(255) DEFAULT NULL, `email_preference` int(11) DEFAULT NULL, `email_domain` varchar(255) DEFAULT NULL, `click_to_call_preference` int(11) DEFAULT NULL, `created_on` datetime NOT NULL, `created_by` varchar(255) NOT NULL, `updated_on` datetime DEFAULT NULL, `updated_by` varchar(255) DEFAULT NULL, `lock_version` int(11) DEFAULT NULL, `_uuid_` varchar(36) NOT NULL, `tenant_id` bigint(20) DEFAULT NULL, `deleted` bit(1) DEFAULT NULL, `table2_id` bigint(20) NOT NULL, `roles` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `_uuid_` (`_uuid_`), UNIQUE KEY `invite_code` (`invite_code`), KEY `ddasdasdae` (`id`), KEY `gg43wwrw` (`physical_location_id`), KEY `dgdty5` (`access_location_id`), KEY `hdfh76` (`status_type_id`), KEY `table2_id` (`table2_id`), KEY `tenant` (`tenant_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `table4` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `updated_by` varchar(255) DEFAULT NULL, `_uuid_` varchar(36) NOT NULL, `lock_version` int(11) DEFAULT NULL, `created_on` datetime NOT NULL, `created_by` varchar(255) NOT NULL, `updated_on` datetime DEFAULT NULL, `value` varchar(255) DEFAULT NULL, `table1_id` bigint(20) NOT NULL, `definition_id` bigint(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `_uuid_` (`_uuid_`), KEY `ddaa22` (`definition_id`), KEY `jfjf788` (`table1_id`), KEY `search` (`definition_id`,`value`,`table1_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> EXPLAIN SELECT tbl10_.id as col_0_0_, tbl10_.lock_version as col_1_0_, tbl10_.name as col_2_0_, substring(tbl10_.description, 1, 201) as col_3_0_, tbl10_.actual_close_date as col_4_0_, tbl10_.status as col_5_0_, table21_.id as col_6_0_, table21_.first_name as col_7_0_, table21_.last_name as col_8_0_, table21_.name as col_9_0_, person3_.id as col_10_0_, person3_.first_name as col_11_0_, person3_.last_name as col_12_0_ FROM table1 tbl10_ INNER JOIN table2 table21_ on tbl10_.table2_id=table21_.id LEFT OUTER JOIN table3 user2_ on tbl10_.owner_id=user2_.id LEFT OUTER JOIN table2 person3_ on user2_.table2_id=person3_.id WHERE tbl10_.tenant_id = 41315 and tbl10_.deleted=0 and tbl10_.status<>1 and user2_.username<>'dasada' and (tbl10_.id in (select tbl1cu5_.table1_id FROM table4 tbl1cu5_ where 1=1 and tbl1cu5_.value<>'dasdasda GEO' and tbl1cu5_.definition_id=148752)) and (tbl10_.id in (select tbl1cu6_.table1_id FROM table4 tbl1cu6_ where 1=1 and tbl1cu6_.value<>'rwerwer PC' and tbl1cu6_.definition_id=148752)) and (tbl10_.id in (select tbl1cu7_.table1_id FROM table4 tbl1cu7_ where 1=1 and tbl1cu7_.value<>'fsd' and tbl1cu7_.definition_id=148752)) and (tbl10_.id in (select tbl1cu8_.table1_id FROM table4 tbl1cu8_ where 1=1 and tbl1cu8_.value<>'gdfgd. gdfgfd' and tbl1cu8_.definition_id=148752)) and (tbl10_.id in (select tbl1cu9_.table1_id FROM table4 tbl1cu9_ where 1=1 and tbl1cu9_.value<>'jhgjg per jghjg' and tbl1cu9_.definition_id=148752)) and (tbl10_.id in (select tbl1cu10_.table1_id FROM table4 tbl1cu10_ where 1=1 and tbl1cu10_.value<>'fsd. gfgd' and tbl1cu10_.definition_id=148752)) and (tbl10_.id in (select tbl1cu11_.table1_id FROM table4 tbl1cu11_ where 1=1 and tbl1cu11_.value<>'rwerw. rw' and tbl1cu11_.definition_id=148752)) and (tbl10_.id in (select tbl1cu12_.table1_id FROM table4 tbl1cu12_ where 1=1 and tbl1cu12_.value<>'fsd. sdfs' and tbl1cu12_.definition_id=148752)) and (tbl10_.id in (select tbl1cu13_.table1_id FROM table4 tbl1cu13_ where 1=1 and tbl1cu13_.value<>'gdfg gdfgd.' and tbl1cu13_.definition_id=148752)) and (tbl10_.id in (select tbl1cu14_.table1_id FROM table4 tbl1cu14_ where 1=1 and tbl1cu14_.value<>'gdfgfd post gdfgdg' and tbl1cu14_.definition_id=148752)) and (tbl10_.id in (select tbl1cu15_.table1_id FROM table4 tbl1cu15_ where 1=1 and tbl1cu15_.value<>'jghjg. hgjg jghjg' and tbl1cu15_.definition_id=148752)) and (tbl10_.id in (select tbl1cu16_.table1_id FROM table4 tbl1cu16_ where 1=1 and tbl1cu16_.value<>'rw-rwe rwer' and tbl1cu16_.definition_id=148752)) and (tbl10_.id in (select tbl1cu17_.table1_id FROM table4 tbl1cu17_ where 1=1 and tbl1cu17_.value<>'fsdfs fsd!' and tbl1cu17_.definition_id=148752)) and (tbl10_.id in (select tbl1cu18_.table1_id FROM table4 tbl1cu18_ where 1=1 and tbl1cu18_.value<>'gdfgd' and tbl1cu18_.definition_id=148752)) and (tbl10_.id in (select tbl1cu19_.table1_id FROM table4 tbl1cu19_ where 1=1 and tbl1cu19_.value<>'PEC' and tbl1cu19_.definition_id=148752)) and (tbl10_.id in (select tbl1cu20_.table1_id FROM table4 tbl1cu20_ where 1=1 and tbl1cu20_.value<>'rwerw' and tbl1cu20_.definition_id=148752)) and (tbl10_.id in (select tbl1cu21_.table1_id FROM table4 tbl1cu21_ where 1=1 and tbl1cu21_.value<>'jgh. fsd fsd' and tbl1cu21_.definition_id=148752)) and (tbl10_.id in (select tbl1cu22_.table1_id FROM table4 tbl1cu22_ where 1=1 and tbl1cu22_.value<>'fsdfsfs' and tbl1cu22_.definition_id=148752)) and (tbl10_.id in (select tbl1cu23_.table1_id FROM table4 tbl1cu23_ where 1=1 and tbl1cu23_.value<>'gdfg gdfgd' and tbl1cu23_.definition_id=148752)) and (tbl10_.id in (select tbl1cu24_.table1_id FROM table4 tbl1cu24_ where 1=1 and tbl1cu24_.value<>'fsd' and tbl1cu24_.definition_id=148752)) and (tbl10_.id in (select tbl1cu25_.table1_id FROM table4 tbl1cu25_ where 1=1 and tbl1cu25_.value<>'fsdf ggs' and tbl1cu25_.definition_id=148752)) and (tbl10_.id in (select tbl1cu26_.table1_id FROM table4 tbl1cu26_ where 1=1 and tbl1cu26_.value<>'fsdfsdfs' and tbl1cu26_.definition_id=148752)) and (tbl10_.id in (select tbl1cu27_.table1_id FROM table4 tbl1cu27_ where 1=1 and tbl1cu27_.value<>'jghjgh. fsdfsf' and tbl1cu27_.definition_id=148752)) and (tbl10_.id in (select tbl1cu28_.table1_id FROM table4 tbl1cu28_ where 1=1 and tbl1cu28_.value<>'fsdsf' and tbl1cu28_.definition_id=148752)) and (tbl10_.id in (select tbl1cu29_.table1_id FROM table4 tbl1cu29_ where 1=1 and tbl1cu29_.value<>'fsdfs fsdsf' and tbl1cu29_.definition_id=148752)) and (tbl10_.id in (select tbl1cu30_.table1_id FROM table4 tbl1cu30_ where 1=1 and tbl1cu30_.value<>'rw fsdf' and tbl1cu30_.definition_id=148752)) and (tbl10_.id in (select tbl1cu31_.table1_id FROM table4 tbl1cu31_ where 1=1 and tbl1cu31_.value<>'ttervdcvs' and tbl1cu31_.definition_id=148752)) and (tbl10_.id in (select tbl1cu32_.table1_id FROM table4 tbl1cu32_ where 1=1 and tbl1cu32_.value<>'dsfs eee' and tbl1cu32_.definition_id=148752)) and (tbl10_.id in (select tbl1cu33_.table1_id FROM table4 tbl1cu33_ where 1=1 and tbl1cu33_.value<>'Test fsdf' and tbl1cu33_.definition_id=148752)) and (tbl10_.id in (select tbl1cu34_.table1_id FROM table4 tbl1cu34_ where 1=1 and tbl1cu34_.value<>'fsdsf fsdfs GEO' and tbl1cu34_.definition_id=148752)) and (tbl10_.id in (select tbl1cu35_.table1_id FROM table4 tbl1cu35_ where 1=1 and tbl1cu35_.value<>'fs. rwe' and tbl1cu35_.definition_id=148752)) and (tbl10_.id in (select tbl1cu36_.table1_id FROM table4 tbl1cu36_ where 1=1 and tbl1cu36_.value<>'Tel. fsdfsd' and tbl1cu36_.definition_id=148752)) LIMIT 50; ^CCtrl-C -- sending "KILL QUERY 20" to server ... Ctrl-C -- query aborted. ERROR 1317 (70100): Query execution was interrupted -- explain doesn't finish after many hours mysql> set optimizer_switch='semijoin=off'; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN SELECT tbl10_.id as col_0_0_, tbl10_.lock_version as col_1_0_, tbl10_.name as col_2_0_, substring(tbl10_.description, 1, 201) as col_3_0_, tbl10_.actual_close_date as col_4_0_, tbl10_.status as col_5_0_, table21_.id as col_6_0_, table21_.first_name as col_7_0_, table21_.last_name as col_8_0_, table21_.name as col_9_0_, person3_.id as col_10_0_, person3_.first_name as col_11_0_, person3_.last_name as col_12_0_ FROM table1 tbl10_ INNER JOIN table2 table21_ on tbl10_.table2_id=table21_.id LEFT OUTER JOIN table3 user2_ on tbl10_.owner_id=user2_.id LEFT OUTER JOIN table2 person3_ on user2_.table2_id=person3_.id WHERE tbl10_.tenant_id = 41315 and tbl10_.deleted=0 and tbl10_.status<>1 and user2_.username<>'dasada' and (tbl10_.id in (select tbl1cu5_.table1_id FROM table4 tbl1cu5_ where 1=1 and tbl1cu5_.value<>'dasdasda GEO' and tbl1cu5_.definition_id=148752)) and (tbl10_.id in (select tbl1cu6_.table1_id FROM table4 tbl1cu6_ where 1=1 and tbl1cu6_.value<>'rwerwer PC' and tbl1cu6_.definition_id=148752)) and (tbl10_.id in (select tbl1cu7_.table1_id FROM table4 tbl1cu7_ where 1=1 and tbl1cu7_.value<>'fsd' and tbl1cu7_.definition_id=148752)) and (tbl10_.id in (select tbl1cu8_.table1_id FROM table4 tbl1cu8_ where 1=1 and tbl1cu8_.value<>'gdfgd. gdfgfd' and tbl1cu8_.definition_id=148752)) and (tbl10_.id in (select tbl1cu9_.table1_id FROM table4 tbl1cu9_ where 1=1 and tbl1cu9_.value<>'jhgjg per jghjg' and tbl1cu9_.definition_id=148752)) and (tbl10_.id in (select tbl1cu10_.table1_id FROM table4 tbl1cu10_ where 1=1 and tbl1cu10_.value<>'fsd. gfgd' and tbl1cu10_.definition_id=148752)) and (tbl10_.id in (select tbl1cu11_.table1_id FROM table4 tbl1cu11_ where 1=1 and tbl1cu11_.value<>'rwerw. rw' and tbl1cu11_.definition_id=148752)) and (tbl10_.id in (select tbl1cu12_.table1_id FROM table4 tbl1cu12_ where 1=1 and tbl1cu12_.value<>'fsd. sdfs' and tbl1cu12_.definition_id=148752)) and (tbl10_.id in (select tbl1cu13_.table1_id FROM table4 tbl1cu13_ where 1=1 and tbl1cu13_.value<>'gdfg gdfgd.' and tbl1cu13_.definition_id=148752)) and (tbl10_.id in (select tbl1cu14_.table1_id FROM table4 tbl1cu14_ where 1=1 and tbl1cu14_.value<>'gdfgfd post gdfgdg' and tbl1cu14_.definition_id=148752)) and (tbl10_.id in (select tbl1cu15_.table1_id FROM table4 tbl1cu15_ where 1=1 and tbl1cu15_.value<>'jghjg. hgjg jghjg' and tbl1cu15_.definition_id=148752)) and (tbl10_.id in (select tbl1cu16_.table1_id FROM table4 tbl1cu16_ where 1=1 and tbl1cu16_.value<>'rw-rwe rwer' and tbl1cu16_.definition_id=148752)) and (tbl10_.id in (select tbl1cu17_.table1_id FROM table4 tbl1cu17_ where 1=1 and tbl1cu17_.value<>'fsdfs fsd!' and tbl1cu17_.definition_id=148752)) and (tbl10_.id in (select tbl1cu18_.table1_id FROM table4 tbl1cu18_ where 1=1 and tbl1cu18_.value<>'gdfgd' and tbl1cu18_.definition_id=148752)) and (tbl10_.id in (select tbl1cu19_.table1_id FROM table4 tbl1cu19_ where 1=1 and tbl1cu19_.value<>'PEC' and tbl1cu19_.definition_id=148752)) and (tbl10_.id in (select tbl1cu20_.table1_id FROM table4 tbl1cu20_ where 1=1 and tbl1cu20_.value<>'rwerw' and tbl1cu20_.definition_id=148752)) and (tbl10_.id in (select tbl1cu21_.table1_id FROM table4 tbl1cu21_ where 1=1 and tbl1cu21_.value<>'jgh. fsd fsd' and tbl1cu21_.definition_id=148752)) and (tbl10_.id in (select tbl1cu22_.table1_id FROM table4 tbl1cu22_ where 1=1 and tbl1cu22_.value<>'fsdfsfs' and tbl1cu22_.definition_id=148752)) and (tbl10_.id in (select tbl1cu23_.table1_id FROM table4 tbl1cu23_ where 1=1 and tbl1cu23_.value<>'gdfg gdfgd' and tbl1cu23_.definition_id=148752)) and (tbl10_.id in (select tbl1cu24_.table1_id FROM table4 tbl1cu24_ where 1=1 and tbl1cu24_.value<>'fsd' and tbl1cu24_.definition_id=148752)) and (tbl10_.id in (select tbl1cu25_.table1_id FROM table4 tbl1cu25_ where 1=1 and tbl1cu25_.value<>'fsdf ggs' and tbl1cu25_.definition_id=148752)) and (tbl10_.id in (select tbl1cu26_.table1_id FROM table4 tbl1cu26_ where 1=1 and tbl1cu26_.value<>'fsdfsdfs' and tbl1cu26_.definition_id=148752)) and (tbl10_.id in (select tbl1cu27_.table1_id FROM table4 tbl1cu27_ where 1=1 and tbl1cu27_.value<>'jghjgh. fsdfsf' and tbl1cu27_.definition_id=148752)) and (tbl10_.id in (select tbl1cu28_.table1_id FROM table4 tbl1cu28_ where 1=1 and tbl1cu28_.value<>'fsdsf' and tbl1cu28_.definition_id=148752)) and (tbl10_.id in (select tbl1cu29_.table1_id FROM table4 tbl1cu29_ where 1=1 and tbl1cu29_.value<>'fsdfs fsdsf' and tbl1cu29_.definition_id=148752)) and (tbl10_.id in (select tbl1cu30_.table1_id FROM table4 tbl1cu30_ where 1=1 and tbl1cu30_.value<>'rw fsdf' and tbl1cu30_.definition_id=148752)) and (tbl10_.id in (select tbl1cu31_.table1_id FROM table4 tbl1cu31_ where 1=1 and tbl1cu31_.value<>'ttervdcvs' and tbl1cu31_.definition_id=148752)) and (tbl10_.id in (select tbl1cu32_.table1_id FROM table4 tbl1cu32_ where 1=1 and tbl1cu32_.value<>'dsfs eee' and tbl1cu32_.definition_id=148752)) and (tbl10_.id in (select tbl1cu33_.table1_id FROM table4 tbl1cu33_ where 1=1 and tbl1cu33_.value<>'Test fsdf' and tbl1cu33_.definition_id=148752)) and (tbl10_.id in (select tbl1cu34_.table1_id FROM table4 tbl1cu34_ where 1=1 and tbl1cu34_.value<>'fsdsf fsdfs GEO' and tbl1cu34_.definition_id=148752)) and (tbl10_.id in (select tbl1cu35_.table1_id FROM table4 tbl1cu35_ where 1=1 and tbl1cu35_.value<>'fs. rwe' and tbl1cu35_.definition_id=148752)) and (tbl10_.id in (select tbl1cu36_.table1_id FROM table4 tbl1cu36_ where 1=1 and tbl1cu36_.value<>'Tel. fsdfsd' and tbl1cu36_.definition_id=148752)) LIMIT 50; +----+--------------------+-----------+--------+-----------------------------------------------------------------------------+---------+---------+------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------+--------+-----------------------------------------------------------------------------+---------+---------+------------------------+------+-------------+ | 1 | PRIMARY | tbl10_ | ref | sf34,gds4,tenant,search_table1,last_contacted_on_table1,tenant_deleted_name | tenant | 9 | const | 1 | Using where | | 1 | PRIMARY | table21_ | eq_ref | PRIMARY | PRIMARY | 8 | prune.tbl10_.table2_id | 1 | NULL | | 1 | PRIMARY | user2_ | eq_ref | PRIMARY,ddasdasdae | PRIMARY | 8 | prune.tbl10_.owner_id | 1 | Using where | | 1 | PRIMARY | person3_ | eq_ref | PRIMARY | PRIMARY | 8 | prune.user2_.table2_id | 1 | NULL | | 33 | DEPENDENT SUBQUERY | tbl1cu36_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 32 | DEPENDENT SUBQUERY | tbl1cu35_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 31 | DEPENDENT SUBQUERY | tbl1cu34_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 30 | DEPENDENT SUBQUERY | tbl1cu33_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 29 | DEPENDENT SUBQUERY | tbl1cu32_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 28 | DEPENDENT SUBQUERY | tbl1cu31_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 27 | DEPENDENT SUBQUERY | tbl1cu30_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 26 | DEPENDENT SUBQUERY | tbl1cu29_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 25 | DEPENDENT SUBQUERY | tbl1cu28_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 24 | DEPENDENT SUBQUERY | tbl1cu27_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 23 | DEPENDENT SUBQUERY | tbl1cu26_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 22 | DEPENDENT SUBQUERY | tbl1cu25_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 21 | DEPENDENT SUBQUERY | tbl1cu24_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 20 | DEPENDENT SUBQUERY | tbl1cu23_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 19 | DEPENDENT SUBQUERY | tbl1cu22_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 18 | DEPENDENT SUBQUERY | tbl1cu21_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 17 | DEPENDENT SUBQUERY | tbl1cu20_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 16 | DEPENDENT SUBQUERY | tbl1cu19_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 15 | DEPENDENT SUBQUERY | tbl1cu18_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 14 | DEPENDENT SUBQUERY | tbl1cu17_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 13 | DEPENDENT SUBQUERY | tbl1cu16_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 12 | DEPENDENT SUBQUERY | tbl1cu15_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 11 | DEPENDENT SUBQUERY | tbl1cu14_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 10 | DEPENDENT SUBQUERY | tbl1cu13_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 9 | DEPENDENT SUBQUERY | tbl1cu12_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 8 | DEPENDENT SUBQUERY | tbl1cu11_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 7 | DEPENDENT SUBQUERY | tbl1cu10_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 6 | DEPENDENT SUBQUERY | tbl1cu9_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 5 | DEPENDENT SUBQUERY | tbl1cu8_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 4 | DEPENDENT SUBQUERY | tbl1cu7_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 3 | DEPENDENT SUBQUERY | tbl1cu6_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | tbl1cu5_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | +----+--------------------+-----------+--------+-----------------------------------------------------------------------------+---------+---------+------------------------+------+-------------+ 36 rows in set (0.01 sec) The same test on 5.5.42: mysql55 > show variables like 'opti%'; *************************** 1. row *************************** Variable_name: optimizer_prune_level Value: 0 *************************** 2. row *************************** Variable_name: optimizer_search_depth Value: 62 *************************** 3. row *************************** Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on 3 rows in set (0.00 sec) mysql55 > EXPLAIN SELECT tbl10_.id as col_0_0_, tbl10_.lock_version as col_1_0_, tbl10_.name as col_2_0_, substring(tbl10_.description, 1, 201) as col_3_0_, tbl10_.actual_close_date as col_4_0_, tbl10_.status as col_5_0_, table21_.id as col_6_0_, table21_.first_name as col_7_0_, table21_.last_name as col_8_0_, table21_.name as col_9_0_, person3_.id as col_10_0_, person3_.first_name as col_11_0_, person3_.last_name as col_12_0_ FROM table1 tbl10_ INNER JOIN table2 table21_ on tbl10_.table2_id=table21_.id LEFT OUTER JOIN table3 user2_ on tbl10_.owner_id=user2_.id LEFT OUTER JOIN table2 person3_ on user2_.table2_id=person3_.id WHERE tbl10_.tenant_id = 41315 and tbl10_.deleted=0 and tbl10_.status<>1 and user2_.username<>'dasada' and (tbl10_.id in (select tbl1cu5_.table1_id FROM table4 tbl1cu5_ where 1=1 and tbl1cu5_.value<>'dasdasda GEO' and tbl1cu5_.definition_id=148752)) and (tbl10_.id in (select tbl1cu6_.table1_id FROM table4 tbl1cu6_ where 1=1 and tbl1cu6_.value<>'rwerwer PC' and tbl1cu6_.definition_id=148752)) and (tbl10_.id in (select tbl1cu7_.table1_id FROM table4 tbl1cu7_ where 1=1 and tbl1cu7_.value<>'fsd' and tbl1cu7_.definition_id=148752)) and (tbl10_.id in (select tbl1cu8_.table1_id FROM table4 tbl1cu8_ where 1=1 and tbl1cu8_.value<>'gdfgd. gdfgfd' and tbl1cu8_.definition_id=148752)) and (tbl10_.id in (select tbl1cu9_.table1_id FROM table4 tbl1cu9_ where 1=1 and tbl1cu9_.value<>'jhgjg per jghjg' and tbl1cu9_.definition_id=148752)) and (tbl10_.id in (select tbl1cu10_.table1_id FROM table4 tbl1cu10_ where 1=1 and tbl1cu10_.value<>'fsd. gfgd' and tbl1cu10_.definition_id=148752)) and (tbl10_.id in (select tbl1cu11_.table1_id FROM table4 tbl1cu11_ where 1=1 and tbl1cu11_.value<>'rwerw. rw' and tbl1cu11_.definition_id=148752)) and (tbl10_.id in (select tbl1cu12_.table1_id FROM table4 tbl1cu12_ where 1=1 and tbl1cu12_.value<>'fsd. sdfs' and tbl1cu12_.definition_id=148752)) and (tbl10_.id in (select tbl1cu13_.table1_id FROM table4 tbl1cu13_ where 1=1 and tbl1cu13_.value<>'gdfg gdfgd.' and tbl1cu13_.definition_id=148752)) and (tbl10_.id in (select tbl1cu14_.table1_id FROM table4 tbl1cu14_ where 1=1 and tbl1cu14_.value<>'gdfgfd post gdfgdg' and tbl1cu14_.definition_id=148752)) and (tbl10_.id in (select tbl1cu15_.table1_id FROM table4 tbl1cu15_ where 1=1 and tbl1cu15_.value<>'jghjg. hgjg jghjg' and tbl1cu15_.definition_id=148752)) and (tbl10_.id in (select tbl1cu16_.table1_id FROM table4 tbl1cu16_ where 1=1 and tbl1cu16_.value<>'rw-rwe rwer' and tbl1cu16_.definition_id=148752)) and (tbl10_.id in (select tbl1cu17_.table1_id FROM table4 tbl1cu17_ where 1=1 and tbl1cu17_.value<>'fsdfs fsd!' and tbl1cu17_.definition_id=148752)) and (tbl10_.id in (select tbl1cu18_.table1_id FROM table4 tbl1cu18_ where 1=1 and tbl1cu18_.value<>'gdfgd' and tbl1cu18_.definition_id=148752)) and (tbl10_.id in (select tbl1cu19_.table1_id FROM table4 tbl1cu19_ where 1=1 and tbl1cu19_.value<>'PEC' and tbl1cu19_.definition_id=148752)) and (tbl10_.id in (select tbl1cu20_.table1_id FROM table4 tbl1cu20_ where 1=1 and tbl1cu20_.value<>'rwerw' and tbl1cu20_.definition_id=148752)) and (tbl10_.id in (select tbl1cu21_.table1_id FROM table4 tbl1cu21_ where 1=1 and tbl1cu21_.value<>'jgh. fsd fsd' and tbl1cu21_.definition_id=148752)) and (tbl10_.id in (select tbl1cu22_.table1_id FROM table4 tbl1cu22_ where 1=1 and tbl1cu22_.value<>'fsdfsfs' and tbl1cu22_.definition_id=148752)) and (tbl10_.id in (select tbl1cu23_.table1_id FROM table4 tbl1cu23_ where 1=1 and tbl1cu23_.value<>'gdfg gdfgd' and tbl1cu23_.definition_id=148752)) and (tbl10_.id in (select tbl1cu24_.table1_id FROM table4 tbl1cu24_ where 1=1 and tbl1cu24_.value<>'fsd' and tbl1cu24_.definition_id=148752)) and (tbl10_.id in (select tbl1cu25_.table1_id FROM table4 tbl1cu25_ where 1=1 and tbl1cu25_.value<>'fsdf ggs' and tbl1cu25_.definition_id=148752)) and (tbl10_.id in (select tbl1cu26_.table1_id FROM table4 tbl1cu26_ where 1=1 and tbl1cu26_.value<>'fsdfsdfs' and tbl1cu26_.definition_id=148752)) and (tbl10_.id in (select tbl1cu27_.table1_id FROM table4 tbl1cu27_ where 1=1 and tbl1cu27_.value<>'jghjgh. fsdfsf' and tbl1cu27_.definition_id=148752)) and (tbl10_.id in (select tbl1cu28_.table1_id FROM table4 tbl1cu28_ where 1=1 and tbl1cu28_.value<>'fsdsf' and tbl1cu28_.definition_id=148752)) and (tbl10_.id in (select tbl1cu29_.table1_id FROM table4 tbl1cu29_ where 1=1 and tbl1cu29_.value<>'fsdfs fsdsf' and tbl1cu29_.definition_id=148752)) and (tbl10_.id in (select tbl1cu30_.table1_id FROM table4 tbl1cu30_ where 1=1 and tbl1cu30_.value<>'rw fsdf' and tbl1cu30_.definition_id=148752)) and (tbl10_.id in (select tbl1cu31_.table1_id FROM table4 tbl1cu31_ where 1=1 and tbl1cu31_.value<>'ttervdcvs' and tbl1cu31_.definition_id=148752)) and (tbl10_.id in (select tbl1cu32_.table1_id FROM table4 tbl1cu32_ where 1=1 and tbl1cu32_.value<>'dsfs eee' and tbl1cu32_.definition_id=148752)) and (tbl10_.id in (select tbl1cu33_.table1_id FROM table4 tbl1cu33_ where 1=1 and tbl1cu33_.value<>'Test fsdf' and tbl1cu33_.definition_id=148752)) and (tbl10_.id in (select tbl1cu34_.table1_id FROM table4 tbl1cu34_ where 1=1 and tbl1cu34_.value<>'fsdsf fsdfs GEO' and tbl1cu34_.definition_id=148752)) and (tbl10_.id in (select tbl1cu35_.table1_id FROM table4 tbl1cu35_ where 1=1 and tbl1cu35_.value<>'fs. rwe' and tbl1cu35_.definition_id=148752)) and (tbl10_.id in (select tbl1cu36_.table1_id FROM table4 tbl1cu36_ where 1=1 and tbl1cu36_.value<>'Tel. fsdfsd' and tbl1cu36_.definition_id=148752)) LIMIT 50; +----+--------------------+-----------+--------+-----------------------------------------------------------------------------+---------+---------+------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------+--------+-----------------------------------------------------------------------------+---------+---------+------------------------+------+-------------+ | 1 | PRIMARY | tbl10_ | ref | sf34,gds4,tenant,search_table1,last_contacted_on_table1,tenant_deleted_name | tenant | 9 | const | 1 | Using where | | 1 | PRIMARY | table21_ | eq_ref | PRIMARY | PRIMARY | 8 | prune.tbl10_.table2_id | 1 | | | 1 | PRIMARY | user2_ | eq_ref | PRIMARY,ddasdasdae | PRIMARY | 8 | prune.tbl10_.owner_id | 1 | Using where | | 1 | PRIMARY | person3_ | eq_ref | PRIMARY | PRIMARY | 8 | prune.user2_.table2_id | 1 | | | 33 | DEPENDENT SUBQUERY | tbl1cu36_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 32 | DEPENDENT SUBQUERY | tbl1cu35_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 31 | DEPENDENT SUBQUERY | tbl1cu34_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 30 | DEPENDENT SUBQUERY | tbl1cu33_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 29 | DEPENDENT SUBQUERY | tbl1cu32_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 28 | DEPENDENT SUBQUERY | tbl1cu31_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 27 | DEPENDENT SUBQUERY | tbl1cu30_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 26 | DEPENDENT SUBQUERY | tbl1cu29_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 25 | DEPENDENT SUBQUERY | tbl1cu28_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 24 | DEPENDENT SUBQUERY | tbl1cu27_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 23 | DEPENDENT SUBQUERY | tbl1cu26_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 22 | DEPENDENT SUBQUERY | tbl1cu25_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 21 | DEPENDENT SUBQUERY | tbl1cu24_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 20 | DEPENDENT SUBQUERY | tbl1cu23_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 19 | DEPENDENT SUBQUERY | tbl1cu22_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 18 | DEPENDENT SUBQUERY | tbl1cu21_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 17 | DEPENDENT SUBQUERY | tbl1cu20_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 16 | DEPENDENT SUBQUERY | tbl1cu19_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 15 | DEPENDENT SUBQUERY | tbl1cu18_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 14 | DEPENDENT SUBQUERY | tbl1cu17_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 13 | DEPENDENT SUBQUERY | tbl1cu16_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 12 | DEPENDENT SUBQUERY | tbl1cu15_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 11 | DEPENDENT SUBQUERY | tbl1cu14_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 10 | DEPENDENT SUBQUERY | tbl1cu13_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 9 | DEPENDENT SUBQUERY | tbl1cu12_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 8 | DEPENDENT SUBQUERY | tbl1cu11_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 7 | DEPENDENT SUBQUERY | tbl1cu10_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 6 | DEPENDENT SUBQUERY | tbl1cu9_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 5 | DEPENDENT SUBQUERY | tbl1cu8_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 4 | DEPENDENT SUBQUERY | tbl1cu7_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 3 | DEPENDENT SUBQUERY | tbl1cu6_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | tbl1cu5_ | ref | ddaa22,jfjf788,search | ddaa22 | 8 | const | 1 | Using where | +----+--------------------+-----------+--------+-----------------------------------------------------------------------------+---------+---------+------------------------+------+-------------+ 36 rows in set (0.00 sec)