path: root/mysql-test/t/statistics.test

--source include/have_innodb.inc
--disable_warnings
drop table if exists t1,t2;
--enable_warnings

set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables;

CREATE VIEW table_stat AS
SELECT * FROM mysql.table_stat;
 
CREATE VIEW column_stat AS
  SELECT db_name, table_name, column_name,
         min_value, max_value,
         CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio',
         CAST(avg_length AS decimal(12,4)) AS 'avg_length',
         CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
    FROM mysql.column_stat;

CREATE VIEW index_stat AS
  SELECT db_name, table_name, index_name, prefix_arity,
         CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency'
    FROM mysql.index_stat;

DELETE FROM mysql.table_stat;
DELETE FROM mysql.column_stat;
DELETE FROM mysql.index_stat;  
   
set optimizer_use_stat_tables='preferably';

CREATE TABLE t1 (
  a int NOT NULL PRIMARY KEY,
  b varchar(32),
  c char(16),
  d date,
  e double,
  f bit(3),
  INDEX idx1 (b, e), 
  INDEX idx2(c, d),
  INDEX idx3 (d),
  INDEX idx4 (e, b, d)
);

INSERT INTO t1 VALUES
  (0, NULL, NULL, NULL, NULL, NULL),
  (7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'),
  (17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'),
  (1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'),
  (12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'),
  (23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'),
  (8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'),
  (22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'),
  (31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'),
  (10, NULL, 'aaaa', NULL, 0.01, b'010'),
  (5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'),
  (15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'),
  (30, NULL, 'bbbbbb', NULL, NULL, b'100'),
  (38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL),
  (18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'),
  (9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL),
  (29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'),
  (3, 'yyy', 'dddddddd',  '1990-05-15', 0.112, b'010'),
  (39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'),
  (14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'),
  (40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL),
  (44, NULL, 'aaaa', '1989-03-12', NULL, b'010'),
  (19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'),
  (21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'),
  (45, NULL, NULL, '1989-03-12', NULL, b'011'),
  (2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'),
  (35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'),
  (4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'),
  (47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'),
  (42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'),
  (32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'),
  (49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL),
  (43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'),
  (37, 'yyy', NULL, '1989-03-12', 0.05, b'011'),
  (41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL),
  (34, 'yyy', NULL, NULL, NULL, NULL),
  (33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'),
  (24,  'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01,  b'101'),
  (11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL),
  (25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01,  b'101');

ANALYZE TABLE t1;

SELECT * FROM table_stat;
SELECT * FROM column_stat;
SELECT * FROM index_stat;

SELECT COUNT(*) FROM t1;

SELECT * FROM column_stat
  WHERE db_name='test' AND table_name='t1' AND column_name='a';
SELECT MIN(t1.a), MAX(t1.a), 
       (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)",
       (SELECT COUNT(t1.a) FROM t1) /
       (SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)"
FROM t1;

SELECT * FROM column_stat
  WHERE db_name='test' AND table_name='t1' AND column_name='b';
SELECT MIN(t1.b), MAX(t1.b), 
       (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) /
       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)",
       (SELECT COUNT(t1.b) FROM t1) /
       (SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)"
FROM t1;

SELECT * FROM column_stat 
  WHERE db_name='test' AND table_name='t1' AND column_name='c';
SELECT MIN(t1.c), MAX(t1.c), 
       (SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) /
       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)",
       (SELECT COUNT(t1.c) FROM t1) /
       (SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)"
FROM t1;

SELECT * FROM column_stat
  WHERE db_name='test' AND table_name='t1' AND column_name='d';
SELECT MIN(t1.d), MAX(t1.d), 
       (SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) /
       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)",
       (SELECT COUNT(t1.d) FROM t1) /
       (SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)"
FROM t1;

SELECT * FROM column_stat
  WHERE db_name='test' AND table_name='t1' AND column_name='e';
SELECT MIN(t1.e), MAX(t1.e), 
       (SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) /
       (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)",
       (SELECT COUNT(t1.e) FROM t1) /
       (SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)"
FROM t1;

SELECT * FROM index_stat
  WHERE db_name='test' AND table_name='t1' AND index_name='idx1';
SELECT 
  (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL)
  AS 'ARITY 1',
  (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1
     WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) 
  AS 'ARITY 2';

SELECT * FROM index_stat
  WHERE db_name='test' AND table_name='t1' AND index_name='idx2';
SELECT 
  (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) 
  AS 'ARITY 1',
  (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1
     WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL)
  AS 'ARITY 2';

SELECT * FROM index_stat
  WHERE db_name='test' AND table_name='t1' AND index_name='idx3';
SELECT 
  (SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL)
  AS 'ARITY 1';

SELECT * FROM index_stat
  WHERE db_name='test' AND table_name='t1' AND index_name='idx4';
SELECT 
  (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL)
  AS 'ARITY 1',
  (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) /
  (SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1
         WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL)
  AS 'ARITY 2',
  (SELECT COUNT(*) FROM t1
     WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) /  
  (SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1
     WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL)
  AS 'ARITY 3';


CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ENGINE=InnoDB;
INSERT INTO t2 SELECT * FROM t1;

ANALYZE TABLE t2;

SELECT * FROM table_stat;
SELECT * FROM column_stat ORDER BY column_name;
SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name;


DELETE FROM mysql.table_stat;
DELETE FROM mysql.column_stat;
DELETE FROM mysql.index_stat;

ALTER TABLE t1
  DROP INDEX idx1,
  DROP INDEX idx4;
ALTER TABLE t1
  MODIFY COLUMN b text,
  ADD INDEX idx1 (b(4), e), 
  ADD INDEX idx4 (e, b(4), d);
  
ANALYZE TABLE t1;

SELECT * FROM column_stat;
SELECT * FROM index_stat;

DROP TABLE t1,t2;

DELETE FROM mysql.table_stat;
DELETE FROM mysql.column_stat;
DELETE FROM mysql.index_stat;

set optimizer_use_stat_tables='never';  

set names utf8;

CREATE DATABASE world;

use world;

--source include/world_schema_utf8.inc

--disable_query_log
--disable_result_log
--disable_warnings
--source include/world.inc
--enable_warnings
--enable_result_log
--enable_query_log

set optimizer_use_stat_tables='preferably';

--disable_result_log
ANALYZE TABLE Country, City, CountryLanguage;
--enable_result_log

SELECT UPPER(db_name), UPPER(table_name), cardinality
  FROM test.table_stat;
SELECT UPPER(db_name), UPPER(table_name), 
       column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
  FROM test.column_stat;
SELECT UPPER(db_name), UPPER(table_name),
       index_name, prefix_arity, avg_frequency
  FROM test.index_stat;

use test;

set optimizer_use_stat_tables='never'; 

CREATE DATABASE world_innodb;

use world_innodb;

--source include/world_schema_utf8.inc

ALTER TABLE Country ENGINE=InnoDB;
ALTER TABLE City ENGINE=InnoDB;
ALTER TABLE CountryLanguage ENGINE=InnoDB;

--disable_query_log
--disable_result_log
--disable_warnings
--source include/world.inc
--enable_warnings
--enable_result_log
--enable_query_log

set optimizer_use_stat_tables='preferably';

--disable_result_log
ANALYZE TABLE Country, City, CountryLanguage;
--enable_result_log

SELECT UPPER(db_name), UPPER(table_name), cardinality
  FROM test.table_stat;
SELECT UPPER(db_name), UPPER(table_name), 
       column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency
  FROM test.column_stat;
SELECT UPPER(db_name), UPPER(table_name),
       index_name, prefix_arity, avg_frequency
  FROM test.index_stat;

use test;

DROP DATABASE world;
DROP DATABASE world_innodb;

DELETE FROM mysql.table_stat;
DELETE FROM mysql.column_stat;
DELETE FROM mysql.index_stat;
  
DROP VIEW test.table_stat;
DROP VIEW test.column_stat;
DROP VIEW test.index_stat;

set optimizer_use_stat_tables=@save_optimizer_use_stat_tables;