CREATE TABLE tab1 (
  c1 INT(11) NOT NULL ,
  c2 VARCHAR(50) NOT NULL ,
  c3 INT(11) NOT NULL ,
  c4 VARCHAR(128) NOT NULL,
  c5 VARCHAR(128) NULL DEFAULT NULL ,
  c6 VARCHAR(128) NOT NULL ,
  c7 BIGINT(20) NOT NULL ,
  PRIMARY KEY (c1,c2) USING BTREE,
  INDEX indx01 (c2, c7) USING BTREE,
  INDEX indx02 (c3) USING BTREE,
  INDEX indx03 (c4,c5,c2) USING BTREE,
  INDEX indx04 (c6) USING BTREE
) ENGINE=InnoDB;


CREATE OR REPLACE PROCEDURE InsertData(IN n INT)
BEGIN

    DECLARE counter INT DEFAULT 1;

    WHILE counter <= n DO
        insert into test.tab1 (c1,c2,c3,c4,c5,c6,c7) values (counter,'c2_01',round(rand()*200,0),'c4_01','c5_00',convert(round(rand()*5000,0),char),round(rand()*10,0));
        insert into test.tab1 (c1,c2,c3,c4,c5,c6,c7) values (counter,'c2_02',round(rand()*200,0),'c4_02','c5_00',convert(round(rand()*5000,0),char),round(rand()*10,0));
        insert into test.tab1 (c1,c2,c3,c4,c5,c6,c7) values (counter,'c2_03',round(rand()*200,0),'c4_03','c5_00',convert(round(rand()*5000,0),char),round(rand()*10,0));
        insert into test.tab1 (c1,c2,c3,c4,c5,c6,c7) values (counter,'c2_04',round(rand()*200,0),'c4_04','c5_00',convert(round(rand()*5000,0),char),round(rand()*10,0));
        insert into test.tab1 (c1,c2,c3,c4,c5,c6,c7) values (counter,'c2_05',round(rand()*200,0),'c4_05','c5_00',convert(round(rand()*5000,0),char),round(rand()*10,0));

        SET counter = counter + 1;

    END WHILE;

END;


# Execute procedure to insert data with poor distribution -> Value of c2 column (c2_01~05)
CALL InsertData(3000);

# Enter data with good selectivity (only one data with the value of c2_06 in the c2 column is entered)
insert into test.tab1 (c1,c2,c3,c4,c5,c6,c7) values (5001,'c2_06',round(rand()*200,0),'c4_01','c5_00',convert(round(rand()*5000,0),char),round(rand()*10,0));

# Check Plan
-- 10.6 Bad index selected based on column in WHERE
-- 10.4 Correct index selected based on column in GROUP BY 



ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;

-- 10.4 & 10.6 Correct index selected based on column in GROUP BY 
ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
