count之难题
select count(0) from CRM_CUSTOMER a
where a.IS_DEL = 0 and a.IS_TRASH = 0
and
(
(a.CUSTOMER_NAME like '%艾%')
or (a.CUSTOMER_NAME_PINYIN like '%艾%')
or (a.CUSTOMER_NO = '艾')
or (a.ID_CARD like '%艾%')
or (a.COMPANY like '%艾%')
or (a.MOBILE_PHONE1 like '%艾%')
or (a.MOBILE_PHONE2 like '%艾%')
or (a.PHONE like '%艾%')
or (a.FAX like '%艾%')
)
注a表有400w数据，like的字段有组合索引，主键有索引，a.IS_DEL和a.IS_TRASH有索引
性能好慢20秒 求各路大神各抒己见！！！
[解决办法]

引用:

select count(1) from CRM_CUSTOMER a
where
(a.CUSTOMER_NAME like '%艾%')

CUSTOMER_NAME有单独索引 就这句sql也是快的
你是想说 locate比like要快吗？我试过后 性能是差不多的 也有的人说用函数会更慢~~~

我已经做过实验，发现locate与like几乎同样的慢。　这里因为涉及到多列，不如派生一个冗余列，只用一个like。