################### Environment
        Date | 2023-12-01 12:20:48 UTC (local TZ: CET +0100)
    Hostname | support-cluster03
      Uptime | 679 days, 18:49,  3 users,  load average: 0.73, 0.44, 0.32
    Platform | Linux
     Release | Red Hat Enterprise Linux Server release 7.9 (Maipo)
      Kernel | 5.4.17-2102.206.1.el7uek.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.17
    Compiler | GNU CC version 4.8.5 20150623 (Red Hat 4.8.5-44.0.3).
     SELinux | Disabled
 Virtualized | VMWare
# Processor ##################################################
  Processors | physical = 1, cores = 8, virtual = 16, hyperthreading = yes
      Speeds | 16x2445.406
      Models | 16xAMD EPYC 7J13 64-Core Processor
      Caches | 16x512 KB
# Memory #####################################################
       Total | 117.4G
        Free | 1.1G
        Used | physical = 2.3G, swap allocated = 8.0G, swap used = 1.4G, virtual = 3.7G
      Shared | 7.5G
     Buffers | 114.0G
      Caches | 106.4G
       Dirty | 16188 kB
     UsedRSS | 2.2G
  Swappiness | 60
 DirtyPolicy | 20, 10
 DirtyStatus | 0, 0


--
 uname -an
Linux support-cluster03 5.4.17-2102.206.1.el7uek.x86_64 #2 SMP Wed Oct 6 16:40:40 PDT 2021 x86_64 x86_64 x86_64 GNU/Linux

--
 cat /etc/*release
Oracle Linux Server release 7.9
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9
Red Hat Enterprise Linux Server release 7.9 (Maipo)
Oracle Linux Server release 7.9

--
 df -Th /export/home/tmp/ushastry
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sdb1      xfs   2.0T  1.5T  553G  73% /export/home



####################################### 8.0 ####################################### 
- build (binary tarball)
 cat docs/INFO_SRC
commit: 7dea9692ebadea572a847f436f62e66a18cd2d74
date: 2023-10-12 13:34:21 +0200
build-date: 2023-10-12 11:46:35 +0000
short: 7dea9692eba
branch: mysql-8.0.35-release

MySQL source 8.0.35

-- Start up

rm -rf 28404/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/28404 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/28404 --core-file --socket=/tmp/mysql.sock  --port=3306 --log-error=$PWD/28404/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &


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

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.01 sec)

mysql> use test
Database changed
mysql> create table trade (
    -> `trade_id` bigint NOT NULL AUTO_INCREMENT,
    -> `seller_id` bigint DEFAULT NULL,
    -> `create_time` datetime DEFAULT NULL,
    -> `pay_time` datetime DEFAULT NULL,
    -> `doudian_open_id` varchar(256) DEFAULT NULL,
    -> PRIMARY KEY (`trade_id`),
    -> KEY `pay_time` (`pay_time`),
    -> KEY `seller_id` (`seller_id`,`create_time`),
    -> KEY `idx_doudian_open_id` (`doudian_open_id`));
Query OK, 0 rows affected (0.03 sec)

mysql> -- t1_in_range:
CREATE TABLE t1_in_range like trade;

INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:29:26', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_opemysql> n_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
CREATE TABLE t1_in_range like trade;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));

INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSQuery OK, 0 rows affected (0.03 sec)

mysql>
Emysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:29:26', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2023-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 64 rows affected (0.01 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 256 rows affected (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 512 rows affected (0.04 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 1024 rows affected (0.06 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 2048 rows affected (0.10 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;Query OK, 4096 rows affected (0.08 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 8192 rows affected (0.15 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 16384 rows affected (0.40 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 32768 rows affected (0.64 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 65536 rows affected (1.35 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 131072 rows affected (2.80 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_in_range;
Query OK, 262144 rows affected (8.15 sec)
Records: 262144  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t1_not_in_range like trade;

INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:29:26', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));

INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_Query OK, 0 rows affected (0.03 sec)

mysql>
tmysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:29:26', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:29:27', '2023-11-15 20:29:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) VALUES (6656, '2022-11-15 20:30:26', '2023-11-15 20:30:30', CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)));
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 128 rows affected (0.02 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 256 rows affected (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 512 rows affected (0.03 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 1024 rows affected (0.05 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 2048 rows affected (0.09 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 4096 rows affected (0.09 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 8192 rows affected (0.15 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 16384 rows affected (0.32 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 32768 rows affected (0.63 sec)
Records: 32768  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 65536 rows affected (1.24 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 131072 rows affected (2.99 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1_not_in_range (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, CONCAT('1@#xC8', substring(md5(rand()), 1, 72), substring(md5(rand()), 1, 72)) from t1_not_in_range;
Query OK, 262144 rows affected (7.40 sec)
Records: 262144  Duplicates: 0  Warnings: 0

mysql> create table trade_000001(
`trade_id` bigint NOT NULL AUTO_INCREMENT,
`seller_id` bigint DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`pay_time` datetime DEFAULT NULL,
`doudian_open_id` varchar(256) DEFAULT NULL,
PRIMARY KEY (`trade_id`),
KEY `pay_time` (`pay_time`),
KEY `seller_id` (`seller_id`,`create_time`),
KEY `idx_doudian_open_id` (`doudian_open_id`));

INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_in_range;
INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_in_range;
    -> `trade_id` bigint NOT NULL AUTO_INCREMENT,
    -> `seller_id` bigint DEFAULT NULL,
    -> `create_time` datetime DEFAULT NULL,
    -> `pay_time` datetime DEFAULT NULL,
    -> `doudian_open_id` varchar(256) DEFAULT NULL,
    -> PRIMARY KEY (`trade_id`),
    -> KEY `pay_time` (`pay_time`),
    -> KEY `seller_id` (`seller_id`,`create_time`),
    -> KEY `idx_doudian_open_id` (`doudian_open_id`));
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_in_range;
INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_not_in_range;
INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_not_in_range;
Query OK, 524288 rows affected (11.00 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_in_range;
Query OK, 524288 rows affected (18.12 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_not_in_range;
Query OK, 524288 rows affected (25.93 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> INSERT INTO trade_000001 (seller_id, create_time, pay_time, doudian_open_id) SELECT seller_id, create_time, pay_time, doudian_open_id from t1_not_in_range;
Query OK, 524288 rows affected (34.05 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> create table shop_buyer_000001(
    -> `shop_buyer_id` bigint NOT NULL AUTO_INCREMENT,
    -> `seller_id` bigint DEFAULT NULL,
    -> `buyer_frist_pay_time` datetime DEFAULT NULL,
    -> `doudian_open_id` varchar(256) DEFAULT NULL,
    -> PRIMARY KEY (`shop_buyer_id`),
    -> KEY `idx_doudian_open_id` (`doudian_open_id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO shop_buyer_000001 (seller_id, buyer_frist_pay_time, doudian_open_id) SELECT seller_id, pay_time, doudian_open_id from t1_in_range;
Query OK, 524288 rows affected (8.91 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> INSERT INTO shop_buyer_000001 (seller_id, buyer_frist_pay_time, doudian_open_id) SELECT seller_id, create_time, doudian_open_id from t1_not_in_range;
Query OK, 524288 rows affected (13.35 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql> show variables like '%tmp%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_tmp_storage_engine      | InnoDB    |
| innodb_tmpdir                   |           |
| internal_tmp_mem_storage_engine | TempTable |
| replica_load_tmpdir             | /tmp      |
| slave_load_tmpdir               | /tmp      |
| tmp_table_size                  | 16777216  |
| tmpdir                          | /tmp      |
+---------------------------------+-----------+
7 rows in set (0.01 sec)

mysql> set tmp_table_size = 64 * 16777216;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%tmp%';
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| default_tmp_storage_engine      | InnoDB     |
| innodb_tmpdir                   |            |
| internal_tmp_mem_storage_engine | TempTable  |
| replica_load_tmpdir             | /tmp       |
| slave_load_tmpdir               | /tmp       |
| tmp_table_size                  | 1073741824 |
| tmpdir                          | /tmp       |
+---------------------------------+------------+
7 rows in set (0.00 sec)



mysql> SELECT
    ->          count( DISTINCT doudian_open_id )
    ->  FROM
    ->          (
                                 AND trade.seller_id = 6656
                                 AND buyer.seller_id = 6656
                                 AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'
                                 AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (
                                   SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'
                                   GROUP BY
                                         trade.doudian_open_id
                                   HAVING
                                         count( 1 ) >= 1
                                 )
    ->          SELECT
    ->                  doudian_open_id
    ->          FROM
    ->                  (
    ->                          (
    ->                          SELECT
    ->                                  buyer.doudian_open_id AS doudian_open_id
    ->                          FROM
    ->                                  trade_000001 trade,
    ->                                  shop_buyer_000001 buyer
    ->                          WHERE
    ->                                  trade.doudian_open_id = buyer.doudian_open_id
    ->                                  AND trade.seller_id = 6656
    ->                                  AND buyer.seller_id = 6656
    ->                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'
    ->                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (
    ->                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'
    ->                                    GROUP BY
    ->                                          trade.doudian_open_id
    ->                                    HAVING
    ->                                          count( 1 ) >= 1
    ->                                  )
    ->                          )
    ->                  ) x2
    ->          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (2 min 45.50 sec)


mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (2 min 20.99 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (2 min 19.95 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (2 min 19.67 sec)

----------
mysql> set internal_tmp_mem_storage_engine = MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->          count( DISTINCT doudian_open_id )
    ->  FROM
    ->          (
    ->          SELECT
    ->                  doudian_open_id
    ->          FROM
    ->                  (
    ->                          (
    ->                          SELECT
                                 AND trade.seller_id = 6656
                                 AND buyer.seller_id = 6656
                                 AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'
                                 AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (
                                   SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'
                                   GROUP BY
                                         trade.doudian_open_id
                                   HAVING
                                         count( 1 ) >= 1
                                 )
    ->                                  buyer.doudian_open_id AS doudian_open_id
    ->                          FROM
    ->                                  trade_000001 trade,
    ->                                  shop_buyer_000001 buyer
    ->                          WHERE
    ->                                  trade.doudian_open_id = buyer.doudian_open_id
    ->                                  AND trade.seller_id = 6656
    ->                                  AND buyer.seller_id = 6656
    ->                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'
    ->                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (
    ->                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'
    ->                                    GROUP BY
    ->                                          trade.doudian_open_id
    ->                                    HAVING
    ->                                          count( 1 ) >= 1
    ->                                  )
    ->                          )
    ->                  ) x2
    ->          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (1 min 1.76 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (49.62 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (49.06 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (50.47 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (48.90 sec)

mysql> SELECT          count( DISTINCT doudian_open_id )  FROM          (          SELECT                  doudian_open_id          FROM                  (                          (                          SELECT
              buyer.doudian_open_id AS doudian_open_id                          FROM                                  trade_000001 trade,                                  shop_buyer_000001 buyer                          WHERE
                         trade.doudian_open_id = buyer.doudian_open_id                                  AND trade.seller_id = 6656                                  AND buyer.seller_id = 6656                                  AND buyer.buyer_frist_pay_time >= '2023-01-01 00:00:00'                                  AND buyer.buyer_frist_pay_time <= '2023-11-30 23:59:59' AND trade.doudian_open_id IN (                                    SELECT doudian_open_id FROM trade_000001 trade WHERE seller_id = 6656 AND trade.pay_time >= '2022-11-14'                                    GROUP BY                                          trade.doudian_open_id                                    HAVING
                            count( 1 ) >= 1                                  )                          )                  ) x2          ) tmp;
+-----------------------------------+
| count( DISTINCT doudian_open_id ) |
+-----------------------------------+
|                            524288 |
+-----------------------------------+
1 row in set (49.14 sec)