mysql 优化

select order_id,order_sn from (select concat('@@',order_sn) as x ,order_sn,order_id from ecs_order_info where 1 = 1 having x like '@@%0002%') as t;

SELECT m.uid,m.realname,IFNULL(r1.paynum,0) as paynum,IFNULL(rr.nopaynum,0) as nopaynum FROM `ims_mc_members` as m
 LEFT JOIN (select uid,count(uid) as nopaynum from ims_fx_activity_records as r where `status`=0 and merchantid='293' GROUP BY uid) as rr on rr.uid=m.uid 
 LEFT JOIN (select uid,count(uid) as paynum from ims_fx_activity_records  as r  where `status`>0 and merchantid='293' GROUP BY uid) as r1 on r1.uid=m.uid 
 where m.merchant_id='292' ORDER BY r1.paynum desc,rr.nopaynum desc  ;

//查询统计多表 去重 分组 排序

  SELECT `order_sn` FROM `ecs_order_info` where concat('@',`order_sn`)  like '@%11101%';

  SELECT `subject` FROM `taobao_product0` where `subject`  like '_%1114%';

字段值前面加前缀 比如 @

SELECT `subject` FROM `taobao_product0` where `subject`  like '@%1114%';

用到索引 速度很快

mysql 按时间排序

ORDER BY UNIX_TIMESTAMP(modifyTime) desc  代替 ORDER BY modifyTime desc 效率提高

[SQL]  SELECT * FROM `taoyang1`.`ecs_order_info` WHERE order_sn in ('171497');
受影响的行: 0
时间: 0.016ms

[SQL] 
 SELECT * FROM `taoyang1`.`ecs_order_info` WHERE order_sn in (171497);
受影响的行: 0
时间: 7.906ms

多个字段 联合搜索

$where=" and CONCAT(`mall_name`,userName,`userId`) LIKE '%$kw%'";

分类匹配 搜索匹配 最佳匹配类目

SELECT * from pdd_cats where is_enabled=1 and `level` in(3,4) and ((cat_name_all REGEXP '^汽车' and cat_name_all REGEXP '汽车|零部件|养护|美容|维|保|汽车|美容|漆|膜|蜡|补|漆|笔|五|菱|荣光|6450pf6407b|车|漆|划痕|修复|亮|米黄|雷|迅|灰|宝石蓝|银色|自|喷漆' ) or cat_name REGEXP '^补漆笔') ORDER BY LOCATE('补漆笔',cat_name) DESC, `level` desc ;