rm -rf 87164 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/87164 -v bin/mysqld --log_bin --server_id=1 --basedir=$PWD --datadir=$PWD/87164 --core-file --socket=/tmp/mysql.sock --port=3307 --log-error=$PWD/87164/log.err 2>&1 & [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.19: bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.19-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> create database test; use Query OK, 1 row affected (0.78 sec) root@localhost [(none)]> use test Database changed root@localhost [test]> source /tmp/dump.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 45609 rows affected (1.20 sec) Records: 45609 Duplicates: 0 Warnings: 0 Query OK, 43475 rows affected (1.18 sec) Records: 43475 Duplicates: 0 Warnings: 0 Query OK, 42274 rows affected (1.17 sec) Records: 42274 Duplicates: 0 Warnings: 0 Query OK, 42544 rows affected (1.09 sec) Records: 42544 Duplicates: 0 Warnings: 0 Query OK, 41791 rows affected (1.10 sec) Records: 41791 Duplicates: 0 Warnings: 0 Query OK, 41106 rows affected (1.04 sec) Records: 41106 Duplicates: 0 Warnings: 0 Query OK, 41406 rows affected (1.09 sec) Records: 41406 Duplicates: 0 Warnings: 0 Query OK, 5407 rows affected (0.14 sec) Records: 5407 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 15375 rows affected (0.31 sec) Records: 15375 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 6145 rows affected (0.59 sec) Records: 6145 Duplicates: 0 Warnings: 0 Query OK, 2216 rows affected (0.23 sec) Records: 2216 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 22820 rows affected (0.71 sec) Records: 22820 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) root@localhost [test]> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [test]> SET profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [test]> source /tmp/query.sql; *************************** 1. row *************************** pa_id: 40 product_id: 162 *************************** 2. row *************************** pa_id: 40 product_id: 500 *************************** 3. row *************************** pa_id: 40 product_id: 501 *************************** 4. row *************************** pa_id: 40 product_id: 805 *************************** 5. row *************************** pa_id: 40 product_id: 813 *************************** 6. row *************************** pa_id: 40 product_id: 817 *************************** 7. row *************************** pa_id: 40 product_id: 921 *************************** 8. row *************************** pa_id: 40 product_id: 3410 8 rows in set (3 min 13.23 sec) root@localhost [test]> show profiles; +----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 193.22101350 | select `pa_id`, `pc2`.`connect_parent` as `product_id` from `pc2` inner join `pc1` on `pc1`.`pc_id` = `pc2`.`connect_child` inner join `ac` on `ac`.`product_child` = `pc1`.`pc_id` inner join `pa` on `pa`.`pa_id` = `ac`.`attribute_id` and `pa`.`pa_type` = '1' inner join `ac` as `ac0` on `ac | +----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [test]> show profiles for query 1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for query 1' at line 1 root@localhost [test]> show profile for query 1; +----------------------+------------+ | Status | Duration | +----------------------+------------+ | starting | 0.009689 | | checking permissions | 0.000008 | | checking permissions | 0.000003 | | checking permissions | 0.000002 | | checking permissions | 0.000003 | | checking permissions | 0.000007 | | Opening tables | 0.000980 | | init | 0.002611 | | System lock | 0.000016 | | optimizing | 0.000925 | | statistics | 193.195049 | | preparing | 0.000957 | | Creating tmp table | 0.000035 | | Sorting result | 0.000010 | | executing | 0.000003 | | Sending data | 0.010331 | | Creating sort index | 0.000054 | | end | 0.000005 | | query end | 0.000013 | | removing tmp table | 0.000005 | | query end | 0.000003 | | closing tables | 0.000013 | | freeing items | 0.000264 | | cleaning up | 0.000029 | +----------------------+------------+ 24 rows in set, 1 warning (0.00 sec) root@localhost [test]> source /tmp/query_explain.sql; +----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------+------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------+------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | pa | NULL | ref | PRIMARY,type | type | 4 | const | 99 | 100.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | ac | NULL | ref | idx_product_child_attribute_id,attribute,product_child_attribute_id | attribute | 8 | test.pa.pa_id | 14 | 7.53 | Using where | | 1 | SIMPLE | pc1 | NULL | eq_ref | PRIMARY,pc_id_ts_created,pc_enabled,pc_is_clearance,idx_pc_enabled_pc_is_clearance_pc_stock_index | PRIMARY | 4 | test.ac.product_child | 1 | 45.49 | Using where | | 1 | SIMPLE | ac0 | NULL | eq_ref | idx_product_child_attribute_id,attribute,product_child_attribute_id | idx_product_child_attribute_id | 12 | test.ac.product_child,const | 1 | 100.00 | Using index | | 1 | SIMPLE | pc2 | NULL | ref | idx_family_unique,connect_parent,idx_connect_child | idx_family_unique | 4 | test.ac.product_child | 2 | 80.61 | Using where; Using index | +----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------+------+----------+-----------------------------------------------------------+ 5 rows in set, 1 warning (3 min 19.51 sec) root@localhost [test]> show variables like 'optimizer_switch'; +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | optimizer_switch | 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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on | +------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- 5.6 - optimizer_switch root@localhost [test]> set session optimizer_switch='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'; Query OK, 0 rows affected (0.00 sec) root@localhost [test]> source /tmp/query.sql; *************************** 1. row *************************** pa_id: 40 product_id: 162 *************************** 2. row *************************** pa_id: 40 product_id: 500 *************************** 3. row *************************** pa_id: 40 product_id: 501 *************************** 4. row *************************** pa_id: 40 product_id: 805 *************************** 5. row *************************** pa_id: 40 product_id: 813 *************************** 6. row *************************** pa_id: 40 product_id: 817 *************************** 7. row *************************** pa_id: 40 product_id: 921 *************************** 8. row *************************** pa_id: 40 product_id: 3410 8 rows in set (3 min 11.42 sec) root@localhost [test]> show profiles; +----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 193.22101350 | select `pa_id`, `pc2`.`connect_parent` as `product_id` from `pc2` inner join `pc1` on `pc1`.`pc_id` = `pc2`.`connect_child` inner join `ac` on `ac`.`product_child` = `pc1`.`pc_id` inner join `pa` on `pa`.`pa_id` = `ac`.`attribute_id` and `pa`.`pa_type` = '1' inner join `ac` as `ac0` on `ac | | 2 | 0.00008325 | show profiles for query 1 | | 3 | 199.50640250 | explain select `pa_id`, `pc2`.`connect_parent` as `product_id` from `pc2` inner join `pc1` on `pc1`.`pc_id` = `pc2`.`connect_child` inner join `ac` on `ac`.`product_child` = `pc1`.`pc_id` inner join `pa` on `pa`.`pa_id` = `ac`.`attribute_id` and `pa`.`pa_type` = '1' inner join `ac` as `ac0 | | 4 | 0.00194925 | show variables like 'optimizer_switch' | | 5 | 0.00010550 | set session optimize_switch='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= | | 6 | 0.00015325 | set session optimizer_switch='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 | | 7 | 191.42626400 | select `pa_id`, `pc2`.`connect_parent` as `product_id` from `pc2` inner join `pc1` on `pc1`.`pc_id` = `pc2`.`connect_child` inner join `ac` on `ac`.`product_child` = `pc1`.`pc_id` inner join `pa` on `pa`.`pa_id` = `ac`.`attribute_id` and `pa`.`pa_type` = '1' inner join `ac` as `ac0` on `ac | +----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set, 1 warning (0.00 sec) root@localhost [test]> show profile for query 7; +----------------------+------------+ | Status | Duration | +----------------------+------------+ | starting | 0.008576 | | checking permissions | 0.000006 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | checking permissions | 0.000001 | | checking permissions | 0.000006 | | Opening tables | 0.000036 | | init | 0.002762 | | System lock | 0.000018 | | optimizing | 0.000924 | | statistics | 191.402809 | | preparing | 0.000931 | | Creating tmp table | 0.000032 | | Sorting result | 0.000008 | | executing | 0.000002 | | Sending data | 0.009783 | | Creating sort index | 0.000045 | | end | 0.000004 | | query end | 0.000013 | | removing tmp table | 0.000012 | | query end | 0.000003 | | closing tables | 0.000013 | | freeing items | 0.000254 | | cleaning up | 0.000025 | +----------------------+------------+ 24 rows in set, 1 warning (0.00 sec) root@localhost [test]> rm -rf 86971 scripts/mysql_install_db --basedir=$PWD --datadir=$PWD/86971 bin/mysqld --log_bin --server_id=1 --basedir=$PWD --datadir=$PWD/86971 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/86971/log.err 2>&1 & [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.6.37: bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> use test Database changed root@localhost [test]> source /tmp/dump.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 2 warnings (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 45609 rows affected (0.72 sec) Records: 45609 Duplicates: 0 Warnings: 0 Query OK, 43475 rows affected (0.66 sec) Records: 43475 Duplicates: 0 Warnings: 0 Query OK, 42274 rows affected (0.62 sec) Records: 42274 Duplicates: 0 Warnings: 0 Query OK, 42544 rows affected (0.64 sec) Records: 42544 Duplicates: 0 Warnings: 0 Query OK, 41791 rows affected (0.63 sec) Records: 41791 Duplicates: 0 Warnings: 0 Query OK, 41106 rows affected (0.61 sec) Records: 41106 Duplicates: 0 Warnings: 0 Query OK, 41406 rows affected (0.62 sec) Records: 41406 Duplicates: 0 Warnings: 0 Query OK, 5407 rows affected (0.08 sec) Records: 5407 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 2 warnings (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 15375 rows affected (0.19 sec) Records: 15375 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 2 warnings (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 6145 rows affected (0.35 sec) Records: 6145 Duplicates: 0 Warnings: 0 Query OK, 2216 rows affected (0.11 sec) Records: 2216 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 2 warnings (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 22820 rows affected (0.41 sec) Records: 22820 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) root@localhost [test]> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [test]> SET profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [test]> source /tmp/query.sql; *************************** 1. row *************************** pa_id: 40 product_id: 162 *************************** 2. row *************************** pa_id: 40 product_id: 500 *************************** 3. row *************************** pa_id: 40 product_id: 501 *************************** 4. row *************************** pa_id: 40 product_id: 805 *************************** 5. row *************************** pa_id: 40 product_id: 813 *************************** 6. row *************************** pa_id: 40 product_id: 817 *************************** 7. row *************************** pa_id: 40 product_id: 921 *************************** 8. row *************************** pa_id: 40 product_id: 3410 8 rows in set (0.87 sec) root@localhost [test]> show profiles; +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.86843650 | select `pa_id`, `pc2`.`connect_parent` as `product_id` from `pc2` inner join `pc1` on `pc1`.`pc_id` = `pc2`.`connect_child` inner join `ac` on `ac`.`product_child` = `pc1`.`pc_id` inner join `pa` on `pa`.`pa_id` = `ac`.`attribute_id` and `pa`.`pa_type` = '1' inner join `ac` as `ac0` on `ac | +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [test]> show profile for query 1; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.008478 | | checking permissions | 0.000006 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | checking permissions | 0.000001 | | checking permissions | 0.000003 | | Opening tables | 0.000827 | | init | 0.003386 | | System lock | 0.000012 | | optimizing | 0.000944 | | statistics | 0.698125 | | preparing | 0.001170 | | Creating tmp table | 0.000041 | | Sorting result | 0.000004 | | executing | 0.000003 | | Sending data | 0.154983 | | Creating sort index | 0.000045 | | end | 0.000005 | | removing tmp table | 0.000008 | | end | 0.000004 | | query end | 0.000007 | | closing tables | 0.000019 | | freeing items | 0.000340 | | cleaning up | 0.000024 | +----------------------+----------+ 24 rows in set, 1 warning (0.00 sec) root@localhost [test]> source /tmp/query_explain.sql; +----+-------------+-------+--------+---------------------------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | pa | ref | PRIMARY,type | type | 4 | const | 99 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | ac | ref | idx_product_child_attribute_id,attribute,product_child_attribute_id | attribute | 8 | test.pa.pa_id | 1 | Using where | | 1 | SIMPLE | pc2 | ref | idx_family_unique,connect_parent,idx_connect_child | idx_family_unique | 4 | test.ac.product_child | 1 | Using where; Using index | | 1 | SIMPLE | pc1 | eq_ref | PRIMARY,pc_id_ts_created,pc_enabled,pc_is_clearance,idx_pc_enabled_pc_is_clearance_pc_stock_index | PRIMARY | 4 | test.ac.product_child | 1 | Using where | | 1 | SIMPLE | ac0 | eq_ref | idx_product_child_attribute_id,attribute,product_child_attribute_id | idx_product_child_attribute_id | 12 | test.ac.product_child,const | 1 | Using index | +----+-------------+-------+--------+---------------------------------------------------------------------------------------------------+--------------------------------+---------+-----------------------------+------+-----------------------------------------------------------+ 5 rows in set (0.71 sec) root@localhost [test]> show variables like 'optimizer_switch'; +------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | optimizer_switch | 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 | +------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@localhost [test]>