MySQL 统计 10 万条记录条件需要 6 秒

Question

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秒，因为优惠券表中有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 ;

Answer 1

简化:

+------------+-------------------------------+
| CURDATE()  | DATE_FORMAT(NOW(),"%Y-%m-%d") |
+------------+-------------------------------+
| 2019-02-19 | 2019-02-19                    |
+------------+-------------------------------+

所需索引(优化器将选择其中之一):

INDEX(active, is_public, start)
INDEX(active, is_public, end)

请勿使用FLOAT 或DOUBLE 作为货币。使用DECIMAL。