-- 8.0.21

- build used

cat docs/INFO_SRC
commit: 09901861e2ef411f49aff8905fa99ad479280bf4
date: 2020-06-16 15:51:03 +0200
build-date: 2020-06-16 18:31:54 +0200
short: 0990186
branch: mysql-8.0.21-release

MySQL source 8.0.21


-- server start up 
rm -rf 100253/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/100253 --log-error-verbosity=3 
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/100253 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/100253/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --performance-schema=ON 2>&1 &

-

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)



 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> SOURCE /export/umesh/server/binaries/GABuilds/mysql-8.0.21/case_ddl.sql;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/export/umesh/server/binaries/GABuilds/mysql-8.0.21/case.csv' INTO TABLE test.review_request FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 1403719 rows affected (34.61 sec)
Records: 1403719  Deleted: 0  Skipped: 0  Warnings: 0

mysql> ANALYZE TABLE test.review_request ;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| test.review_request | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: review_request
   partitions: NULL
         type: range
possible_keys: queue_id
          key: queue_id
      key_len: 7
          ref: NULL
         rows: 503116
     filtered: 100.00
        Extra: Using where; Using index for skip scan
1 row in set, 1 warning (0.00 sec)
mysql> CALL test.skip_scan() ; SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;
.
.

+----------+
| @id_list |
+----------+
|          |
+----------+
1 row in set (33.63 sec)

+----------+
| rows_del |
+----------+
|   239850 |
+----------+
1 row in set (33.63 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (33.63 sec)

Query OK, 0 rows affected (33.63 sec)

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


mysql> SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;
+----------+
| COUNT(*) |
+----------+
|   917813 |
+----------+
1 row in set (0.53 sec)


--- Lowest version checked 8.0.13( Skip Scan access method implemented after Bug #88103, thus skipped 5.7)
- build

 cat docs/INFO_SRC
commit: 3672fb2ad9f6c91cd2527866d46fa583b70bfefd
date: 2018-10-07 10:41:27 +0200
build-date: 2018-10-07 10:44:40 +0200
short: 3672fb2
branch: mysql-8.0.13-release

MySQL source 8.0.13



-- server start up 
rm -rf 100253/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/100253 --log-error-verbosity=3 
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/100253 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/100253/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --performance-schema=ON 2>&1 &


 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.08 sec)

mysql> use test
Database changed
mysql> SOURCE /export/umesh/server/binaries/GABuilds/mysql-8.0.21/case_ddl.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/export/umesh/server/binaries/GABuilds/mysql-8.0.21/case.csv' INTO TABLE test.review_request FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 1403719 rows affected (32.35 sec)
Records: 1403719  Deleted: 0  Skipped: 0  Warnings: 0

mysql> ANALYZE TABLE test.review_request ;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| test.review_request | analyze | status   | OK       |
+---------------------+---------+----------+----------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: review_request
   partitions: NULL
         type: range
possible_keys: queue_id
          key: queue_id
      key_len: 7
          ref: NULL
         rows: 467076
     filtered: 100.00
        Extra: Using where; Using index for skip scan
1 row in set, 1 warning (0.00 sec)

mysql> CALL test.skip_scan() ; SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;
.
.
+----------+
| @id_list |
+----------+
|          |
+----------+
1 row in set (2 min 9.01 sec)

+----------+
| rows_del |
+----------+
|  1007660 |
+----------+
1 row in set (2 min 9.01 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (2 min 9.01 sec)

Query OK, 0 rows affected (2 min 9.01 sec)

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM test.review_request WHERE crdate < CURDATE() - INTERVAL 10 DAY;
+----------+
| COUNT(*) |
+----------+
|   150003 |
+----------+
1 row in set (0.08 sec)

mysql>