You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
232 lines
6.6 KiB
232 lines
6.6 KiB
# WL10398: Improve SELECT COUNT(*) performance by using
|
|
# handler::records(index) in execution phase.
|
|
|
|
# Mixed SE in a single statement will need to call ha_records from execute
|
|
# phase only.
|
|
|
|
--source include/force_myisam_default.inc
|
|
--source include/have_myisam.inc
|
|
|
|
CREATE TABLE t_innodb(c1 INT NOT NULL PRIMARY KEY,
|
|
c2 INT NOT NULL,
|
|
c3 char(20),
|
|
KEY c3_idx(c3))ENGINE=INNODB;
|
|
|
|
INSERT INTO t_innodb VALUES (1, 1, 'a'), (2,2,'a'), (3,3,'a');
|
|
ANALYZE TABLE t_innodb;
|
|
|
|
CREATE TABLE t_myisam(c1 INT NOT NULL PRIMARY KEY,
|
|
c2 INT NOT NULL DEFAULT 1,
|
|
c3 char(20),
|
|
KEY c3_idx(c3)) ENGINE=MYISAM;
|
|
|
|
INSERT INTO t_myisam(c1) VALUES (1), (2);
|
|
ANALYZE TABLE t_myisam;
|
|
|
|
# ha_records is called from execution phase.
|
|
let query1= SELECT COUNT(*) FROM t_innodb;
|
|
|
|
# Tables are optimized away because there is HA_COUNT_ROWS_INSTANT
|
|
let query2= SELECT COUNT(*) FROM t_myisam;
|
|
|
|
# Neither table is optimized away. ha_records is called from execution phase
|
|
# for both tables.
|
|
let query3= SELECT COUNT(*) FROM t_myisam, t_innodb;
|
|
|
|
# Uses table scan for MIN(c2) because there is no index on c2.
|
|
let query4= SELECT MIN(c2), COUNT(*), MAX(c1) FROM t_innodb;
|
|
|
|
# Uses c3_idx for MIN -> direct call from opt_sum_query (optimization phase).
|
|
# Uses c3_idx for COUNT(*) from end_send_count (execution phase).
|
|
let query5= SELECT MIN(c3), COUNT(*) FROM t_innodb;
|
|
|
|
eval EXPLAIN $query1;
|
|
eval EXPLAIN $query2;
|
|
eval EXPLAIN $query3;
|
|
eval EXPLAIN $query4;
|
|
eval EXPLAIN $query5;
|
|
|
|
FLUSH STATUS;
|
|
eval $query1;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query2;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query3;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query4;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query5;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
# This WL should affect a table with no primary key only to the extent that
|
|
# call to records happens in execution phase now.
|
|
|
|
CREATE TABLE t_nopk(c1 INT NOT NULL , c2 INT NOT NULL)ENGINE=INNODB;
|
|
INSERT INTO t_nopk SELECT c1, c2 FROM t_innodb;
|
|
SHOW CREATE TABLE t_nopk;
|
|
|
|
let query6= SELECT COUNT(*) FROM t_nopk;
|
|
eval EXPLAIN $query6;
|
|
|
|
FLUSH STATUS;
|
|
eval $query6;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
# Add a secondary index and notice the difference in the plan.
|
|
CREATE INDEX c2_idx on t_nopk(c2);
|
|
SHOW CREATE TABLE t_nopk;
|
|
|
|
eval EXPLAIN $query6;
|
|
|
|
FLUSH STATUS;
|
|
eval $query6;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
DROP TABLE t_nopk;
|
|
|
|
# Set of tests to check if records / records_from_index are called correctly.
|
|
|
|
CREATE TABLE t_innodb_nopk_sk(c1 INT NOT NULL,
|
|
c2 INT NOT NULL, KEY c2_idx(c2))ENGINE=INNODB;
|
|
CREATE TABLE t_innodb_pk_nosk(c1 INT NOT NULL PRIMARY KEY,
|
|
c2 INT NOT NULL)ENGINE=INNODB;
|
|
CREATE TABLE t_innodb_nopk_nosk(c1 INT NOT NULL,
|
|
c2 INT NOT NULL)ENGINE=INNODB;
|
|
INSERT INTO t_innodb_nopk_sk(c1,c2) VALUES (1, 1), (2,2), (3,3);
|
|
INSERT INTO t_innodb_pk_nosk(c1,c2) SELECT * FROM t_innodb_nopk_sk;
|
|
INSERT INTO t_innodb_nopk_nosk(c1,c2) SELECT * FROM t_innodb_nopk_sk;
|
|
ANALYZE TABLE t_innodb_nopk_sk, t_innodb_pk_nosk, t_innodb_nopk_nosk;
|
|
|
|
#Uses handler::records_from_index. Cheaper to use secondary index.
|
|
let query7= SELECT COUNT(*) FROM t_innodb_nopk_sk;
|
|
|
|
#Uses ha_innobase::records. Count returned with a single handler call.
|
|
let query8= SELECT COUNT(*) FROM t_innodb_pk_nosk;
|
|
|
|
#Also uses ha_innobase::records.
|
|
let query9= SELECT COUNT(*) FROM t_innodb_nopk_nosk;
|
|
|
|
eval EXPLAIN $query7;
|
|
eval EXPLAIN $query8;
|
|
eval EXPLAIN $query9;
|
|
|
|
#under the current circumstances: non-zero value in Handler_read_next means
|
|
# handler::records_from_index() is called. zero value in Handler_read_next
|
|
# means ha_innobase::records() is called.
|
|
|
|
FLUSH STATUS;
|
|
eval $query7;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query8;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query9;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
DROP TABLE t_innodb_pk_nosk, t_innodb_nopk_sk, t_innodb_nopk_nosk;
|
|
|
|
#Other SEs
|
|
CREATE TABLE t_heap(c1 INT NOT NULL PRIMARY KEY,
|
|
c2 INT NOT NULL,
|
|
c3 char(20)) ENGINE=HEAP;
|
|
CREATE TABLE t_archive(c1 INT NOT NULL, c2 char(20)) ENGINE=ARCHIVE;
|
|
|
|
INSERT INTO t_heap SELECT * FROM t_innodb WHERE c1 > 1;
|
|
INSERT INTO t_archive SELECT c1, c3 FROM t_innodb WHERE c1 > 1;
|
|
|
|
let query10= SELECT COUNT(*) FROM t_heap;
|
|
let query11= SELECT COUNT(*) FROM t_innodb, t_heap;
|
|
let query12= SELECT COUNT(*) FROM t_archive;
|
|
let query13= SELECT COUNT(*) FROM t_innodb, t_archive;
|
|
|
|
eval EXPLAIN $query10;
|
|
eval EXPLAIN $query11;
|
|
eval EXPLAIN $query12;
|
|
eval EXPLAIN $query13;
|
|
|
|
FLUSH STATUS;
|
|
eval $query10;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query11;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query12;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query13;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
DROP TABLE t_archive, t_heap;
|
|
|
|
#FORCE INDEX
|
|
# ha_records is called from execution phase.
|
|
let query14= SELECT COUNT(*) FROM t_innodb FORCE INDEX(c3_idx);
|
|
|
|
# Tables aren't optimized away here because of force index.
|
|
let query15= SELECT COUNT(*) FROM t_myisam FORCE INDEX(c3_idx);
|
|
eval EXPLAIN $query14;
|
|
eval EXPLAIN $query15;
|
|
|
|
FLUSH STATUS;
|
|
eval $query14;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
FLUSH STATUS;
|
|
eval $query15;
|
|
SHOW STATUS LIKE 'Handler_%';
|
|
|
|
#Testcase for specific lines of code.
|
|
|
|
# tests opt_sum_query(): tables_filled &= !(...). Without the "&" we access
|
|
# the temptable SE's temptable::Handler::records
|
|
SELECT COUNT(*) FROM (SELECT DISTINCT c1 FROM t_myisam) dt, t_myisam;
|
|
|
|
# Tests end_send_count: if (copy_fields(....))
|
|
SET @s =1;
|
|
SELECT @s, COUNT(*) FROM t_innodb;
|
|
|
|
set sql_mode='';
|
|
# Test for end_send_count: if (having_is_true(join->having_cond))
|
|
SELECT 1 AS c1, (SELECT COUNT(*) FROM t_innodb HAVING c1 > 0) FROM DUAL;
|
|
SELECT 1 FROM t_innodb HAVING COUNT(*) > 1;
|
|
|
|
SELECT COUNT(*) c FROM t_innodb HAVING c > 1;
|
|
#empty result.
|
|
SELECT COUNT(*) c FROM t_innodb HAVING c > 7;
|
|
|
|
# Tests FORMAT=tree printing of COUNT(*).
|
|
EXPLAIN FORMAT=tree SELECT COUNT(*) c FROM t_innodb HAVING c > 7;
|
|
EXPLAIN FORMAT=tree SELECT COUNT(*) c FROM t_myisam HAVING c > 7;
|
|
|
|
# Should be empty.
|
|
SELECT COUNT(*) c FROM t_innodb LIMIT 10 OFFSET 5;
|
|
|
|
set sql_mode=default;
|
|
|
|
# Bug #28056133: Requires a fix in get_exact_record_count to avoid dummy temp
|
|
# tables.
|
|
SELECT SQL_BIG_RESULT COUNT(*) FROM t_innodb;
|
|
SELECT SQL_BIG_RESULT COUNT(*) FROM t_innodb, t_myisam;
|
|
|
|
# Unqualified COUNT(*) (running in the iterator executor) combined with BNL
|
|
# (running in the pre-iterator executor).
|
|
# TODO(sgunders): Remove when BNL enters the iterator executor.
|
|
--sorted_result
|
|
SELECT /*+ BNL(t2) */ -(t1.c1 + t2.c1) FROM t_innodb t1, t_innodb t2
|
|
UNION ALL SELECT COUNT(*) FROM t_innodb;
|
|
|
|
DROP TABLE t_innodb, t_myisam;
|
|
|