Default page

Your IP: 216.73.216.124
Server IP: 50.28.103.30
Server: Linux host.jcukjv-lwsites.com 4.18.0-553.22.1.el8_10.x86_64 #1 SMP Tue Sep 24 05:16:59 EDT 2024 x86_64
Server Software: nginx/1.28.0
PHP Version: 8.3.12
Buat File | Buat Folder
View File Name : filter_single_col_idx_small.test

--disable_warnings

CREATE TABLE t1(
  col1_idx INT DEFAULT NULL,
  col2_idx INT DEFAULT NULL,
  col3 INT DEFAULT NULL,
  col4 INT NOT NULL,
  vc VARCHAR(20),
  vc_ft VARCHAR(20),
  KEY(col1_idx),
  KEY(col2_idx),
  FULLTEXT(vc_ft)
) ENGINE=myisam;

CREATE TABLE t2(
  col1_idx INT DEFAULT NULL,
  col2_idx INT DEFAULT NULL,
  col3 INT DEFAULT NULL,
  KEY(col1_idx),
  KEY(col2_idx)
) ENGINE=myisam;

insert into t1 values (1,1,1,1,'america', 'america'),(2,2,2,2,'england','england');

let $iteration= 0;
let $i= 2;
while ($iteration < 3)
{
  eval insert into t1 select col1_idx+$i, col2_idx+$i, col3+$i, col4+$i, vc, vc_ft from t1;
  let $i=$i*2;  
  inc $iteration;
}

insert into t1 select col1_idx, col2_idx, col3, col4, 'america', 'america' from t1;
insert into t1 select col1_idx, col2_idx, col3, col4, 'england america', 'england america' from t1;
insert ignore into t1 select col1_idx, col2_idx, col3, col4, 'germany england america', 'germany england america' from t1;

insert into t2 select col1_idx,col2_idx,col3 from t1;

analyze table t1;
analyze table t2;

--source include/cond_filter_queries.inc
--enable_warnings

DROP TABLE t1,t2;

--echo # 
--echo # Test that on a tiny table, the computed filtering effect is not
--echo # less than one row for the basic condition filter constants
--echo #

CREATE TABLE t1(
  col1_pk INTEGER PRIMARY KEY,
  col2 INTEGER
);

INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);

--disable_query_log
--disable_result_log
ANALYZE TABLE t1;
--enable_result_log
--enable_query_log

--echo # Filtered column should be 1 / (size of table) and larger than 
--echo # COND_FILTER_EQUALITY
EXPLAIN SELECT * FROM t1 WHERE col2 = 2;

--echo # Filtered column should be (size of table - 1) / (size of table) 
--echo # and less than COND_FILTER_EQUALITY
EXPLAIN SELECT * FROM t1 WHERE col2 <> 2;

--echo # Filtered column should be 1 / (size of table) and larger than
--echo # COND_FILTER_BETWEEN
EXPLAIN SELECT * FROM t1 WHERE col2 BETWEEN 2 AND 4;

--echo # Filtered column should be (size of table - 1) / (size of table)
--echo # and less than COND_FILTER_BETWEEN
EXPLAIN SELECT * FROM t1 WHERE col2 NOT BETWEEN 2 AND 4;

# Make the table even smaller to be able to test COND_FILTER_INEQUALITY
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (0,0),(1,1);

--disable_query_log
--disable_result_log
ANALYZE TABLE t1;
--enable_result_log
--enable_query_log

--echo # Filtered column should be 1 / (size of table) and larger than
--echo # COND_FILTER_INEQUALITY
EXPLAIN SELECT * FROM t1 WHERE col2 > 1;

DROP TABLE t1;

--echo #
--echo # Bug#18438671: MAKE USE OF DATATYPE CONSTRAINTS WHEN CALCULATING
--echo #               FILTERING EFFECT
--echo #

CREATE TABLE t1(
  day_of_week enum('0','1','2','3','4','5','6'),
  bit1 bit(1),
  bit3 bit(3)
) ENGINE=myisam;

INSERT INTO t1 VALUES (1+RAND()*7, RAND()*2, RAND()*8),
                      (1+RAND()*7, RAND()*2, RAND()*8);

let $iteration= 0;
let $i= 2;
while ($iteration < 6)
{
  eval INSERT INTO t1 SELECT 1+RAND()*7, RAND()*2, RAND()*8 FROM t1;
  let $i=$i*2;  
  inc $iteration;
}

EXPLAIN SELECT * FROM t1 WHERE bit1 =  b'1';
EXPLAIN SELECT * FROM t1 WHERE bit1 <=>b'1';
EXPLAIN SELECT * FROM t1 WHERE bit1 >  b'0';
EXPLAIN SELECT * FROM t1 WHERE bit1 >= b'0';
EXPLAIN SELECT * FROM t1 WHERE bit1 <  b'0';
EXPLAIN SELECT * FROM t1 WHERE bit1 <= b'0';
EXPLAIN SELECT * FROM t1 WHERE bit3 =  b'1';
EXPLAIN SELECT * FROM t1 WHERE day_of_week;
EXPLAIN SELECT * FROM t1 WHERE day_of_week =  1;
EXPLAIN SELECT * FROM t1 WHERE day_of_week IN (1);
EXPLAIN SELECT * FROM t1 WHERE day_of_week IN (1,2);
EXPLAIN SELECT * FROM t1 WHERE day_of_week LIKE 'foo';
EXPLAIN SELECT * FROM t1 WHERE NOT day_of_week = 1;

DROP TABLE t1;