count之偏题

2013-10-15

count之难题selectcount(0)fromCRM_CUSTOMER awherea.IS_DEL 0anda.IS_TRASH 0and((a.CUSTOMER_NAME l

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。

热点排行

IBM DB2

count之偏题