1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
DROP TABLE IF EXISTS items;
CREATE TABLE items (
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
score INT,
created_at DATETIME,
INDEX (score, created_at)
) DEFAULT CHARSET=UTF8;
SHOW CREATE TABLE items;
Table Create Table
items CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `score` (`score`,`created_at`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8
INSERT INTO items (score, created_at) VALUES(1, "2014-09-10 00:00:00");
INSERT INTO items (score, created_at) VALUES(2, "2014-09-11 00:00:00");
INSERT INTO items (score, created_at) VALUES(2, "2014-09-11 00:00:00");
INSERT INTO items (score, created_at) VALUES(2, "2014-09-12 00:00:00");
INSERT INTO items (score, created_at) VALUES(3, "2014-09-13 00:00:00");
SELECT *
FROM items
WHERE score = 2 AND created_at >= "2014-09-11 00:00:00"
ORDER BY created_at DESC;
id score created_at
4 2 2014-09-12 00:00:00
2 2 2014-09-11 00:00:00
3 2 2014-09-11 00:00:00
DROP TABLE items;
|