Summary of semi-connection Test in MySQL and Oracle (1)

The semi-connection in SQL still has some gaps between MySQL and Oracle, and from the point of view of the test, Oracle's handling is more comprehensive.

First of all, let's take a look at how to test in MySQL. For the test of MySQL, we have also referred to a lot of Haixiang brother's blog articles, and we have also practiced it completely according to his test ideas.

First create the following table:

Create table users (

Userid int (11) unsigned not null

User_name varchar (64) default null

Primary key (userid)

) engine=innodb default charset=UTF8

If you want to insert data, you can use stored procedures. For example, insert 20000 pieces of custom data first.

Delimiter $$

Drop procedure if exists proc_auto_insertdata$$

Create procedure proc_auto_insertdata ()

Begin

Declare

Init_data integer default 1

While init_datasource insert_proc.sql

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (5.63 sec)

Then we use the following semi-join query data, which actually executes for about 6 seconds.

Select u.useridreu.userroomname from users u where u.user_name in (select t.user_name from users t where t.useridselect count (u.userid) from users u where u.user_name in (select t.user_name from users t where t.useridexplain extended select count (u.userid) from users u where (u.user_name in (select t.user_name from users t where t.useridset optimizer_switch= "semijoin=off")

Query OK, 0 rows affected (0.00 sec)

Run the original execution time of SQL for nearly 6 seconds again, and the execution time is greatly reduced.

[test] > select count (u.userid) from users u where u.user_name in (select t.user_name from users t where t.useridselect count (u.userid) from users u where (u.user_name in (select t.user_name from users t where t.useridtrue))

Then execute the same statement as in MySQL.

We use trace to look at it, and we only list the case of trace.

SQL > set autot trace exp stat

SQL > select u.userid.u.userroomname from users u where u.user_name in (select t.user_name from users t where t.userid