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