1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE `test`
( `id` integer unsigned auto_increment not null primary key,
`val` smallint unsigned not null,
index `idx` (`val`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `test` (`val`) values (15),(17),(33),(42),(45),(48),(52),(56),(62),(68),(72),(84),(88)
--------------
--------------
select * from `test`
--------------
+----+-----+
| id | val |
+----+-----+
| 1 | 15 |
| 2 | 17 |
| 3 | 33 |
| 4 | 42 |
| 5 | 45 |
| 6 | 48 |
| 7 | 52 |
| 8 | 56 |
| 9 | 62 |
| 10 | 68 |
| 11 | 72 |
| 12 | 84 |
| 13 | 88 |
+----+-----+
--------------
DROP TABLE IF EXISTS `flag`
--------------
--------------
CREATE TABLE `flag`
( `id` integer unsigned auto_increment not null primary key,
`val` smallint unsigned not null,
index `idx` (`val`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `flag` (`val`) values (17),(45),(52),(56),(72),(84)
--------------
--------------
select * from `flag`
--------------
+----+-----+
| id | val |
+----+-----+
| 1 | 17 |
| 2 | 45 |
| 3 | 52 |
| 4 | 56 |
| 5 | 72 |
| 6 | 84 |
+----+-----+
--------------
select *
from `test` as t1
where not exists (select 1 from `flag` as t2 where t2.val = t1.val)
--------------
+----+-----+
| id | val |
+----+-----+
| 1 | 15 |
| 3 | 33 |
| 4 | 42 |
| 6 | 48 |
| 9 | 62 |
| 10 | 68 |
| 13 | 88 |
+----+-----+
--------------
explain
select *
from `test` as t1
where not exists (select 1 from `flag` as t2 where t2.val = t1.val)
--------------
+----+--------------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+--------------------------+
| 1 | PRIMARY | t1 | NULL | index | NULL | idx | 2 | NULL | 14 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | ref | idx | idx | 2 | base.t1.val | 1 | 100.00 | Using index |
+----+--------------------+-------+------------+-------+---------------+------+---------+-------------+------+----------+--------------------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager