Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.5
Description
Soemtimes the index will not taken.
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'); |