SET @ORIG_PAUSE_BACKGROUND_WORK = @@ROCKSDB_PAUSE_BACKGROUND_WORK;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = 1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
d DATE,
dt DATETIME PRIMARY KEY,
ts TIMESTAMP,
t TIME,
y YEAR
) ENGINE=rocksdb;
SHOW INDEX IN t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	dt	A	1000	NULL	NULL		LSMTREE		
SET @tm = '2012-04-09 05:27:00';
INSERT INTO t1 (d,dt,ts,t,y) VALUES
('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000'),
('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001'),
('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999'),
('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998'),
('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994'),
(DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm));
EXPLAIN SELECT dt FROM t1 ORDER BY dt LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	#	Using index
SELECT dt FROM t1 ORDER BY dt LIMIT 3;
dt
2010-11-22 11:43:14
2010-11-22 12:33:54
2011-08-27 21:33:56
EXPLAIN SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	#	Using index
SELECT dt FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY dt LIMIT 3;
dt
2010-11-22 11:43:14
2010-11-22 12:33:54
2011-08-27 21:33:56
INSERT INTO t1 (d,dt,ts,t,y) VALUES
('2012-01-11', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000');
ERROR 23000: Duplicate entry '2010-11-22 12:33:54' for key 'PRIMARY'
DROP TABLE t1;
CREATE TABLE t1 (
d DATE,
dt DATETIME,
ts TIMESTAMP,
t TIME,
y YEAR,
pk TIME PRIMARY KEY,
INDEX (ts)
) ENGINE=rocksdb;
SHOW INDEX IN t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	pk	A	1000	NULL	NULL		LSMTREE		
t1	1	ts	1	ts	A	500	NULL	NULL	YES	LSMTREE		
SET @tm = '2012-04-09 05:27:00';
INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','12:00:00'),
('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','12:01:00'),
('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','12:02:00'),
('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','12:03:00'),
('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','12:04:00'),
(DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'12:05:00');
EXPLAIN SELECT ts FROM t1 WHERE ts > NOW();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	ts	ts	5	NULL	#	Using where; Using index
SELECT ts FROM t1 WHERE ts > NOW();
ts
EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
SELECT ts FROM t1 USE INDEX () WHERE ts > NOW();
ts
DROP TABLE t1;
CREATE TABLE t1 (
d DATE,
dt DATETIME,
ts TIMESTAMP,
t TIME,
y YEAR,
pk TIME PRIMARY KEY,
INDEX (y,t)
) ENGINE=rocksdb;
SHOW INDEX IN t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	pk	A	1000	NULL	NULL		LSMTREE		
t1	1	y	1	y	A	250	NULL	NULL	YES	LSMTREE		
t1	1	y	2	t	A	500	NULL	NULL	YES	LSMTREE		
SET @tm = '2012-04-09 05:27:00';
INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES
('2012-01-12', '2010-11-22 12:33:54', '2011-11-14 21:45:55', '00:12:33', '2000','18:18:18'),
('2012-01-12', '2010-11-22 11:43:14', '2011-11-14 21:45:55', '00:12:32', '2001','19:18:18'),
('2012-03-31', '2011-08-28 21:33:56', '1999-04-30 19:11:08', '12:00:00', '1999','20:18:18'),
('2012-03-13', '2011-08-27 21:33:56', '1999-03-30 19:11:08', '12:10:00', '1998','21:18:18'),
('2011-03-31', '2011-08-28 20:33:56', '1997-01-31 11:54:01', '22:04:10', '1994','22:18:18'),
(DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'23:18:18');
EXPLAIN SELECT y, COUNT(*) FROM t1 GROUP BY y;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	y	6	NULL	#	Using index
SELECT y, COUNT(*) FROM t1 GROUP BY y;
y	COUNT(*)
1994	1
1998	1
1999	1
2000	1
2001	1
2012	1
EXPLAIN SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	y	6	NULL	#	Using index; Using temporary; Using filesort
SELECT y, COUNT(*) FROM t1 USE INDEX FOR GROUP BY () GROUP BY y;
y	COUNT(*)
1994	1
1998	1
1999	1
2000	1
2001	1
2012	1
DROP TABLE t1;
SET GLOBAL ROCKSDB_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;