Search code examples
mysqlexplain

how to enable mysql to using index for my table

CREATE TABLE `sqq_merchant`.`other_coupon_order` (
`id`  int NOT NULL AUTO_INCREMENT ,
`tenant_id`  varchar(32) NOT NULL ,
`store_id`  varchar(64) NOT NULL ,
`order_no`  varchar(64) NOT NULL ,
`out_order_no`  varchar(64) NOT NULL ,
`total_fee`  integer NOT NULL DEFAULT 0 ,
`other_coupon_fee`  integer NOT NULL DEFAULT 0 ,
`act_total_fee`  integer NOT NULL DEFAULT 0 ,
`pay_method`  tinyint(4) NOT NULL DEFAULT 0 ,
`trade_time`  datetime NOT NULL ,
`create_time`  datetime NOT NULL ,
`update_time`  datetime NOT NULL ,
PRIMARY KEY (`id`),
INDEX `tenant_store_id` (`tenant_id`, `store_id`) USING BTREE ,
UNIQUE INDEX `order_no` (`order_no`) USING BTREE 
);

and I wanna query the order for defined tenant, defined store and defined trade time range, for example:

select * from other_coupon_order where tenant_id = '9001' and store_id = '1151610006' and 
trade_time > '2018-01-08 00:00:00' AND trade_time < '2018-01-08 23:59:59'

while I use explain for this sql, I find the type is All, the performance is bad, how can I improve the sql

this is the sql performance picture for explain

enter image description here

Solution

  • you could try adding also the trade_time to the composite index as (tenant_id, store_id, trade_time)

     INDEX my_indx_name  (tenant_id, store_id, trade_time)