## It seems to me that we need exact data than dummy mysql> CREATE TABLE `patients_query` ( -> `id_` int(11) NOT NULL AUTO_INCREMENT, -> `id` int(11) NOT NULL DEFAULT '0', -> `external_id` varchar(50) NOT NULL DEFAULT '0', -> `master_patient_id` varchar(50) NOT NULL DEFAULT '0', -> `list_type` int(11) NOT NULL DEFAULT '0', -> `list_name` varchar(100) NOT NULL DEFAULT '0', -> `NameFirst` varchar(64) NOT NULL, -> `NameLast` varchar(255) NOT NULL, -> `company_id` int(11) NOT NULL, -> `company_ids` varchar(255) NOT NULL, -> `dob` varchar(50) NOT NULL, -> `AddrStreet` varchar(255) NOT NULL, -> `AddrStreet2` varchar(100) NOT NULL, -> `AddrStreet3` varchar(100) NOT NULL, -> `AddrZip` varchar(15) NOT NULL, -> `AddrCity` varchar(63) NOT NULL, -> `AddrState` varchar(50) NOT NULL, -> `account_number` varchar(255) NOT NULL, -> `admit_service_date` varchar(255) NOT NULL, -> `location_service` varchar(255) NOT NULL, -> `has_patient_list_history` tinyint(4) NOT NULL, -> `is_active` tinyint(4) NOT NULL, -> `phone` varchar(20) NOT NULL, -> `email` varchar(63) NOT NULL, -> `do_not_share_my_data` tinyint(4) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id_`), -> KEY `list_type` (`list_type`), -> KEY `company_id` (`company_id`), -> KEY `list_type_company_id` (`list_type`,`company_id`), -> KEY `master_patient_id` (`master_patient_id`), -> KEY `id` (`id`), -> KEY `master_patien_listy_type` (`master_patient_id`,`list_type`), -> KEY `NameFirst` (`NameFirst`), -> KEY `NameLast` (`NameLast`), -> KEY `dob` (`dob`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) mysql> mysql> mysql> set @id:=0; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into `patients_query` values -> ( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ) -> ,( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ) -> ,( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ) -> ,( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> insert into `patients_query`( -> `id`, -> `external_id`, -> `master_patient_id`, -> `list_type`, -> `list_name`, -> `NameFirst`, -> `NameLast`, -> `company_id`, -> `company_ids`, -> `dob`, -> `AddrStreet`, -> `AddrStreet2`, -> `AddrStreet3`, -> `AddrZip`, -> `AddrCity`, -> `AddrState`, -> `account_number`, -> `admit_service_date`, -> `location_service`, -> `has_patient_list_history`, -> `is_active`, -> `phone`, -> `email`, -> `do_not_share_my_data` -> ) -> select @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> from `patients_query` k1, `patients_query` k2, `patients_query` k3, `patients_query` k4,`patients_query` k5,`patients_query` k6, `patients_query` k7, `patients_query` k8, `patients_query` k9,`patients_query` k0,`patients_query` ka, `patients_query` kb, `patients_query` kc, `patients_query` kd limit 1000000; Query OK, 1000000 rows affected (11 min 46.98 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> DROP TABLE IF EXISTS patients_query_removed; ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ) ,( @id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,@id:=@id+1 ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,FLOOR(RAND() * 99999) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ,FLOOR((RAND() * (1-0+1))+0) ,FLOOR(RAND() * 99999) ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ) ,( @id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,@id:=@id+1 ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,FLOOR(RAND() * 99999) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ,FLOOR((RAND() * (1-0+1))+0) ,FLOOR(RAND() * 99999) ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ) ,( @id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,@id:=@id+1 ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,@id:=@id+1 ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,FLOOR(RAND() * 99999) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ,FLOOR((RAND() * (1-0+1))+0) ,FLOOR(RAND() * 99999) ,md5(rand()*1000000) ,FLOOR((RAND() * (1-0+1))+0) ); insert into `patients_query_removed`( `id`, `external_id`, `master_patient_id`, `list_type`, `list_name`, `NameFirst`, `NameLast`, `company_id`, `company_ids`, `dob`, `AddrStreet`, `AddrStreet2`, `AddrStreet3`, `AddrZip`, `AddrCity`, `AddrState`, `account_number`, `admit_service_date`, `location_service`, `has_patient_list_history`, `is_activQuery OK, 0 rows affected (0.11 sec) mysql> CREATE TABLE `patients_query_removed` ( -> `id_` int(11) NOT NULL AUTO_INCREMENT, -> `id` int(11) NOT NULL DEFAULT '0', -> `external_id` varchar(50) NOT NULL DEFAULT '0', -> `master_patient_id` varchar(50) NOT NULL DEFAULT '0', -> `list_type` int(11) NOT NULL DEFAULT '0', -> `list_name` varchar(100) NOT NULL DEFAULT '0', -> `NameFirst` varchar(64) NOT NULL, -> `NameLast` varchar(255) NOT NULL, -> `company_id` int(11) NOT NULL, -> `company_ids` varchar(255) NOT NULL, -> `dob` varchar(50) NOT NULL, -> `AddrStreet` varchar(255) NOT NULL, -> `AddrStreet2` varchar(100) NOT NULL, -> `AddrStreet3` varchar(100) NOT NULL, -> `AddrZip` varchar(15) NOT NULL, -> `AddrCity` varchar(63) NOT NULL, -> `AddrState` varchar(50) NOT NULL, -> `account_number` varchar(255) NOT NULL, -> `admit_service_date` varchar(255) NOT NULL, -> `location_service` varchar(255) NOT NULL, -> `has_patient_list_history` tinyint(4) NOT NULL, -> `is_active` tinyint(4) NOT NULL, -> `phone` varchar(20) NOT NULL, -> `email` varchar(63) NOT NULL, -> `do_not_share_my_data` tinyint(4) NOT NULL DEFAULT '0', -> PRIMARY KEY (`id_`), -> KEY `list_type` (`list_type`), -> KEY `company_id` (`company_id`), -> KEY `list_type_company_id` (`list_type`,`company_id`), -> KEY `master_patient_id` (`master_patient_id`), -> KEY `id` (`id`), -> KEY `master_patien_listy_type` (`master_patient_id`,`list_type`), -> KEY `NameFirst` (`NameFirst`), -> KEY `NameLast` (`NameLast`), -> KEY `dob` (`dob`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.06 sec) mysql> mysql> mysql> set @id:=0; Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into `patients_query_removed` values -> ( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ) -> ,( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ) -> ,( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ) -> ,( @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> mysql> insert into `patients_query_removed`( -> `id`, -> `external_id`, -> `master_patient_id`, -> `list_type`, -> `list_name`, -> `NameFirst`, -> `NameLast`, -> `company_id`, -> `company_ids`, -> `dob`, -> `AddrStreet`, -> `AddrStreet2`, -> `AddrStreet3`, -> `AddrZip`, -> `AddrCity`, -> `AddrState`, -> `account_number`, -> `admit_service_date`, -> `location_service`, -> `has_patient_list_history`, -> `is_active`, -> `phone`, -> `email`, -> `do_not_share_my_data` -> ) -> select @id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,@id:=@id+1 -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR((RAND() * (1-0+1))+0) -> ,FLOOR(RAND() * 99999) -> ,md5(rand()*1000000) -> ,FLOOR((RAND() * (1-0+1))+0) -> from `patients_query_removed` k1, `patients_query_removed` k2, `patients_query_removed` k3, `patients_query_removed` k4,`patients_query_removed` k5,`patients_query_removed` k6, `patients_query_removed` k7, `patients_query_removed` k8, `patients_query_removed` k9,`patients_query_removed` k0,`patients_query_removed` ka, `patients_query_removed` kb, `patients_query_removed` kc, `patients_query_removed` kd limit 450000; Query OK, 450000 rows affected (2 min 18.14 sec) Records: 450000 Duplicates: 0 Warnings: 0 mysql> mysql> show variables like 'internal%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 1 row in set (0.06 sec) mysql> select * from (select * from patients_query union select * from patients_query_removed) A limit 10; . 1b8edf1804cfde29911f70a3edc | 0 | 0 | 36815 | 937b64734d84ad511463c794acb7f8ad | 1 | +-----+----+-------------+-------------------+-----------+----------------------------------+----------------------------------+----------------------------------+------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+---------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+-----------+-------+----------------------------------+----------------------+ 10 rows in set (2 min 26.54 sec) -- attempted from 2+ sessions and running above query but still not seeing the reported issue ----------------------------+--------------------------+-----------+-------+----------------------------------+----------------------+ 10 rows in set (5 min 27.36 sec) ----------------------------+--------------------------+-----------+-------+----------------------------------+----------------------+ 10 rows in set (2 min 41.31 sec)