--source include/have_xtradb.inc

drop table if exists obs_daily;
CREATE TABLE `obs_daily` ( `iId` int(11) NOT NULL AUTO_INCREMENT, `sLocType` varchar(10) NOT NULL DEFAULT '', `sLocCode` varchar(30) NOT NULL DEFAULT '', `dtDate` date NOT NULL DEFAULT '0000-00-00', PRIMARY KEY (`iId`), UNIQUE KEY `idx_obs_daily_1` (`sLocType`,`sLocCode`,`dtDate`), KEY `idx_obs_daily_2` (`dtDate`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
insert into obs_daily values (null, 'A', 'c1', '2014-11-11'),(null, 'A', 'c2', '2014-11-11'),(null, 'A', 'c3', '2014-11-11'),(null, 'A', 'c4', '2014-11-11'),(null, 'A', 'c5', '2014-11-11'),(null, 'A', 'c6', '2014-11-11'),(null, 'A', 'c7', '2014-11-11'),(null, 'A', 'c8', '2014-11-11');
insert into obs_daily select null, 'A', sLocCode, '2014-08-12' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-13' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-12' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-11' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-10' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-16' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-19' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-22' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-21' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-31' from obs_daily;
insert into obs_daily select null, 'A', concat(sLocCode,iId), '2014-08-30' from obs_daily;
insert into obs_daily select null, 'A', concat('code-1-',iId), '2014-08-31' from obs_daily;
insert into obs_daily select null, 'A', concat('code-2-',iId), '2014-08-31' from obs_daily;
insert into obs_daily select null, 'A', concat('code-2-',iId), '2014-08-29' from obs_daily;
insert into obs_daily select null, 'A', concat('code-3-',iId), '2014-08-09' from obs_daily;
insert into obs_daily select null, 'A', concat('code-4-',iId), '2014-08-05' from obs_daily;
insert into obs_daily select null, 'A', concat('code-5-',iId), '2014-08-03' from obs_daily;
select count(*) from obs_daily;
select count(distinct sLocType) from obs_daily;
select count(distinct sLocCode) from obs_daily;
select count(distinct dtDate) from obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
set global innodb_stats_sample_pages = 8;
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
set global innodb_stats_sample_pages = 3800;
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
set global innodb_stats_sample_pages = 8;
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';
analyze table obs_daily;
select * from information_schema.INNODB_INDEX_STATS where table_name='obs_daily';

drop table obs_daily;