SELECT
`id`, `code`, `description`, `minamt`
FROM `coupons`
WHERE
`starts`<=DATE_FORMAT(NOW(),"%Y-%m-%d")
AND
`ends`>=DATE_FORMAT(NOW(),"%Y-%m-%d")
and
active=1
and
is_public=1
这个mysql需要6到7秒的时间执行,因为在coupons表中有100k条记录。
表结构
CREATE TABLE IF NOT EXISTS `coupons` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bulk_coupon` int(11) DEFAULT '0',
`ctype` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Type',
`code` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'n/a' COMMENT 'Code',
`discount` float(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Discount',
`description` text COLLATE utf8_bin,
`minamt` float(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Min. amount',
`custlogin` tinyint(1) NOT NULL DEFAULT '2' COMMENT 'Requires customer login',
`freeshipping` tinyint(1) NOT NULL DEFAULT '2' COMMENT 'Free shipping',
`customer` text COLLATE utf8_bin,
`products` text COLLATE utf8_bin COMMENT 'Specific products',
`categories` text COLLATE utf8_bin COMMENT 'Spedific categories',
`aod` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Apply on discounted products',
`starts` date NOT NULL COMMENT 'Start on',
`ends` date NOT NULL COMMENT 'Ends on',
`is_public` tinyint(1) DEFAULT '0',
`active` tinyint(1) DEFAULT '2' COMMENT 'Active',
`usage_type` tinyint(1) DEFAULT '0',
`is_used` tinyint(1) DEFAULT '0',
`cod_applicable` tinyint(1) DEFAULT '0',
`return_policy` tinyint(1) DEFAULT '1',
`added` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `startEndDate` (`starts`,`ends`,`is_public`,`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1201682 ;
最佳答案
简化:
+------------+-------------------------------+
| CURDATE() | DATE_FORMAT(NOW(),"%Y-%m-%d") |
+------------+-------------------------------+
| 2019-02-19 | 2019-02-19 |
+------------+-------------------------------+
所需的索引(Optimizer将选择其中一个):
INDEX(active, is_public, start)
INDEX(active, is_public, end)
请勿使用
FLOAT
或DOUBLE
作为货币。使用DECIMAL
。