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)


请勿使用FLOATDOUBLE作为货币。使用DECIMAL

07-26 00:54