| Server IP : 185.86.78.101 / Your IP : 216.73.216.213 Web Server : Apache System : Linux 675867-vds-valikoshka1996.gmhost.pp.ua 5.4.0-150-generic #167-Ubuntu SMP Mon May 15 17:35:05 UTC 2023 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : OFF Directory : /www/server/mysql/mysql-test/r/ |
Upload File : |
DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 ( pk MEDIUMINT NOT NULL AUTO_INCREMENT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, PRIMARY KEY pk (pk), KEY idx_a (a), KEY idx_b (b), KEY idx_c (c), KEY idx_d (d) ); ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status OK EXPLAIN UPDATE t1 SET a=2 WHERE b=6 AND c=6 ; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t1 NULL index_merge idx_b,idx_c idx_b,idx_c 4,4 NULL # 100.00 Using intersect(idx_b,idx_c); Using where SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 ; COUNT(*) SUM(a) 15 90 UPDATE t1 SET a=2 WHERE b=6 AND c=6 ; SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 ; COUNT(*) SUM(a) 15 30 EXPLAIN UPDATE t1 SET a=2 WHERE b=6 AND c=6 AND d=6; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t1 NULL index_merge idx_x,idx_x,idx_x idx_x,idx_x 4,4 NULL # 100.00 Using intersect(idx_x,idx_x); Using where SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 AND d=6; COUNT(*) SUM(a) 15 30 UPDATE t1 SET a=2 WHERE b=6 AND c=6 AND d=6; SELECT COUNT(*), SUM(a) FROM t1 WHERE b=6 AND c=6 AND d=6; COUNT(*) SUM(a) 15 30 EXPLAIN UPDATE t1 SET c=6 WHERE d=6 AND a IS NOT NULL AND b=6; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t1 NULL index_merge idx_b,idx_d idx_b,idx_d 4,4 NULL # 100.00 Using intersect(idx_b,idx_d); Using where SELECT COUNT(*), SUM(c) FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6; COUNT(*) SUM(c) 15 90 UPDATE t1 SET c=6 WHERE d=6 AND a IS NOT NULL AND b=6; SELECT COUNT(*), SUM(c) FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6; COUNT(*) SUM(c) 15 90 EXPLAIN UPDATE t1 SET b=0 WHERE d=6 AND a=6 AND c <> 6; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t1 NULL index_merge idx_a,idx_c,idx_d idx_a,idx_d 4,4 NULL # 100.00 Using intersect(idx_a,idx_d); Using where SELECT COUNT(*), SUM(b) FROM t1 WHERE d=6 AND a=6 AND c <> 6; COUNT(*) SUM(b) 0 NULL UPDATE t1 SET b=0 WHERE d=6 AND a=6 AND c <> 6; SELECT COUNT(*), SUM(b) FROM t1 WHERE d=6 AND a=6 AND c <> 6; COUNT(*) SUM(b) 0 NULL EXPLAIN UPDATE t1 SET a=100 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t1 NULL index_merge idx_a,idx_c,idx_d idx_a,idx_d 4,4 NULL # 100.00 Using intersect(idx_a,idx_d); Using where; Using temporary SELECT COUNT(*), SUM(a) FROM t1 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5); COUNT(*) SUM(a) 0 NULL UPDATE t1 SET a=100 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5); SELECT COUNT(*), SUM(a) FROM t1 WHERE d=6 AND a=6 AND c IN (1,2,3,4,5); COUNT(*) SUM(a) 0 NULL EXPLAIN UPDATE t1 SET a=2 WHERE a=5 AND b=4 AND d<3; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t1 NULL index_merge idx_a,idx_b,idx_d idx_a,idx_b 4,4 NULL # 100.00 Using intersect(idx_a,idx_b); Using where; Using temporary UPDATE t1 SET a=2 WHERE a=5 AND b=4 AND d<3; create table t2 ( pk MEDIUMINT NOT NULL AUTO_INCREMENT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, PRIMARY KEY pk (pk), KEY idx_a (a), KEY idx_b (b), KEY idx_c (c), KEY idx_d (d) )engine=InnoDB; INSERT INTO t2 SELECT * FROM t1; ANALYZE TABLE t2; Table Op Msg_type Msg_text test.t2 analyze status OK EXPLAIN UPDATE t2 SET a=2 WHERE pk<2492 AND d=1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t2 NULL range PRIMARY,idx_d PRIMARY 3 const # 100.00 Using where UPDATE t2 SET a=2 WHERE pk<2492 AND d=1; CREATE TABLE t3( a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, e INT ); INSERT INTO t3(a,b,c,d,e) VALUES (3890,3890,3890,3890,3890); INSERT INTO t3(a,b,c,d,e) VALUES (4000,4000,4000,4000,4000); EXPLAIN REPLACE INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 REPLACE t3 NULL ALL NULL NULL NULL NULL # NULL NULL 1 SIMPLE t1 NULL index_merge idx_b,idx_c idx_b,idx_c 4,4 NULL # 100.00 Using intersect(idx_b,idx_c); Using where SELECT COUNT(*) FROM t3; COUNT(*) 2 REPLACE INTO t3 SELECT * FROM t1 WHERE b=6 AND c=6; SELECT COUNT(*) FROM t3; COUNT(*) 17 EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 INSERT t3 NULL ALL NULL NULL NULL NULL # NULL NULL 1 SIMPLE t1 NULL index_merge idx_a,idx_b,idx_d idx_b,idx_d 4,4 NULL # 90.00 Using intersect(idx_b,idx_d); Using where SELECT COUNT(*) FROM t3; COUNT(*) 17 INSERT INTO t3 SELECT * FROM t1 WHERE d=6 AND a IS NOT NULL AND b=6; SELECT COUNT(*) FROM t3; COUNT(*) 32 CREATE TABLE t4 ( pk MEDIUMINT NOT NULL AUTO_INCREMENT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL, PRIMARY KEY pk (pk), KEY idx_cd (c,d), KEY idx_bd (b,d) ); INSERT INTO t4 SELECT * FROM t1; ANALYZE TABLE t4; Table Op Msg_type Msg_text test.t4 analyze status OK EXPLAIN UPDATE t4 SET a=2 WHERE b=6 AND c=6 AND d=6; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 UPDATE t4 NULL index_merge idx_xd,idx_xd idx_xd,idx_xd 8,8 NULL # 100.00 Using intersect(idx_xd,idx_xd); Using where SELECT COUNT(*), SUM(a) FROM t4 WHERE b=6 AND c=6 AND d=6; COUNT(*) SUM(a) 15 30 UPDATE t4 SET a=2 WHERE b=6 AND c=6 AND d=6; SELECT COUNT(*), SUM(a) FROM t4 WHERE b=6 AND c=6 AND d=6; COUNT(*) SUM(a) 15 30 DROP TABLE t1,t2,t3,t4; # # Bug#24829050 INDEX_MERGE_INTERSECTION OPTIMIZATION CAUSES # WRONG QUERY RESULTS # CREATE TABLE t1 (ID int, PRIMARY KEY (ID)); CREATE TABLE t2 (ID int, c1 int DEFAULT NULL, c2 int, PRIMARY KEY (ID), KEY c1_INDEX (c1), KEY c2_INDEX (c2)); insert into t1 values (1); insert into t2 values (1,1,20),(2,1,20),(3,1,30),(4,2,30); SELECT * FROM t1 WHERE t1.ID=1 AND EXISTS ( SELECT 1 FROM t2 WHERE t2.c2=30 AND t2.c1=t1.ID); ID 1 DROP TABLE t1; DROP TABLE t2;