rm -rf 87742
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/87742 -v
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/87742 --core-file --socket=/tmp/mysql_ushastry.sock --port=3306 --log-error=$PWD/87742/log.err 2>&1 &

-- set sql_mode='' if needed

DROP TABLE IF EXISTS patients_query;
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;


set @id:=0;

 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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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))
	,md5(rand()*1000000)
	,md5(rand()*1000000)
    ,FLOOR((RAND()*(1-0+1)+0))
	);

  
 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)
	,md5(rand()*1000000)
	,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))
	,md5(rand()*1000000)
	,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;  


DROP TABLE IF EXISTS patients_query_removed;
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;


set @id:=0;

 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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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)
	,md5(rand()*1000000)
	,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))
	,md5(rand()*1000000)
	,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_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)
	,md5(rand()*1000000)
	,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))
	,md5(rand()*1000000)
	,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  100000;  
   
   
 set global internal_tmp_disk_storage_engine = InnoDB;  
 -- reconnect session and attempt to run below query multiple times
 select * from (select * from patients_query_removed union select * from patients_query_removed) A limit 10;
   
###