set max_recursive_iterations = 10000000;

DROP TABLE IF EXISTS t1;

CREATE TABLE `t1` (

`mbr_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Member ID, Unique Number For Member',

`buynbr_us` bigint(20) NOT NULL COMMENT 'CAS Buyer number for US',

`status` char(1) NOT NULL DEFAULT 'A' COMMENT 'Value is "A" or "I" to indicate status is Active or Inactive.',

PRIMARY KEY (`mbr_id`),

KEY `idx_members_02` (`status`)

) ENGINE=InnoDB AUTO_INCREMENT=19363297 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Table to store Member details';

INSERT INTO `t1`  (

with recursive series as (

select 1 as mbr_id,FLOOR(1 + (RAND() * 2)) as buynbr_us, (CASE FLOOR(1 + (RAND() * 2)) WHEN 1 THEN 'A' WHEN 2 THEN 'I' ELSE 'more' END) as status  union all

select mbr_id +1 as mbr_id, FLOOR(1 + (RAND() * 100000)) as buynbr_us ,(CASE FLOOR(1 + (RAND() * 2)) WHEN 1 THEN 'A' WHEN 2 THEN 'I' ELSE 'more' END) as status from series

where mbr_id < 100000)

select mbr_id,buynbr_us,status from series);

SELECT FLOOR(1 + (RAND() * 2));

DROP TABLE IF EXISTS `t2`;

CREATE TABLE `t2` (

`mbr_id` bigint(20) NOT NULL COMMENT 'MemberID',

`site_grp_cd` varchar(10) NOT NULL COMMENT 'Site grpup Code',

PRIMARY KEY (`mbr_id`,`site_grp_cd`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Table to store member site details';

INSERT INTO `t2`  (

with recursive series as (

select 1 as mbr_id,LEFT(MD5(RAND()), 10) as site_grp_cd   union all

select mbr_id +1 as mbr_id, LEFT(MD5(RAND()), 10) as site_grp_cd from series

where mbr_id < 100000)

select mbr_id,site_grp_cd from series);

explain select * from t1 inner join t2 on (t1.mbr_id=t2.mbr_id and t2.site_grp_cd='USPLUS') where ( t1.status = 'A');