数据库instr使用

以前一直知道like查询,不过在一些情况下效率非常糟糕,所以用了instr这个函数。

当然,我这里探究的不是instrlike的效率问题,而是instr的一个细节问题。

看下面一个语句:

select SUM(case when instr(IC.info_course_title,'T8') > 0 then 1
    when instr(IC.info_course_title,'T9') > 0 then 1
    when instr(IC.info_course_title,'T10') > 0 then 1
    else 0 end) as sum_a,
SUM(case when instr(IC.info_course_title,'T19') > 0 then 1
    when instr(IC.info_course_title,'T20') > 0 then 1
    else 0 end) as sum_b,
SUM(case when instr(IC.info_course_title,'T1') > 0 then 1
    when instr(IC.info_course_title,'T2') > 0 then 1
    when instr(IC.info_course_title,'T3') > 0 then 1
    when instr(IC.info_course_title,'T4') > 0 then 1
    when instr(IC.info_course_title,'T5') > 0 then 1
    when instr(IC.info_course_title,'T6') > 0 then 1
    when instr(IC.info_course_title,'T7') > 0 then 1
    when instr(IC.info_course_title,'T16') > 0 then 1
    when instr(IC.info_course_title,'T17') > 0 then 1
    when instr(IC.info_course_title,'T18') > 0 then 1
    else 0 end) as sum_c
from PINFO_TERM_USER PTU 
join ITLMS_REGISTER_COURSE IRC ON PTU.TERM_ID = IRC.TERM_ID
join ITLMS_COURSE IC on IRC.COURSE_ID = IC.ID;

这是我一个朋友的sql。当然很多朋友都喜欢写一些非常非常复杂的sql来做一些事情。但是有时候过于复杂的sql不如拆分一下,因为有些东西自己写程序处理也许会更快。
当然我拆分一下:

select SUM(case when instr(IC.info_course_title,'T19') > 0 then 1
    when instr(IC.info_course_title,'T20') > 0 then 1
    else 0 end) as sum_a
from PINFO_TERM_USER PTU
join ITLMS_REGISTER_COURSE IRC ON PTU.TERM_ID = IRC.TERM_ID
join ITLMS_COURSE IC on IRC.COURSE_ID = IC.ID

好了。然后我再优化一下:

select count(ic.id) as sum_a
from PINFO_TERM_USER PTU
join ITLMS_REGISTER_COURSE IRC ON PTU.TERM_ID = IRC.TERM_ID
join ITLMS_COURSE IC on IRC.COURSE_ID = IC.ID where instr(IC.info_course_title,'T19') > 0 or instr(IC.info_course_title,'T20') > 0

当然也许你觉得这好像没有做什么优化是吧。

那么我们看一下结果:

instr

粗略估算一下,应该差不多有15倍左右的一个差距。

至于为什么,我猜想,大概因为第一种查询出来的结果太多了,然后分别在进行instr判断,所以导致时间增长吧。

当然后来又发现可以使用group by这个,然后相加:

SELECT count(IC.ID) SUM_COUNT,IC.INFO_COURSE_TITLE
FROM ITLMS_REGISTER_COURSE IRC
JOIN ITLMS_COURSE IC ON IRC.COURSE_ID = IC.ID
JOIN PINFO_TERM_USER PTU ON PTU.TERM_ID = IRC.TERM_ID
group by IC.INFO_COURSE_TITLE

当然效率我在MySQL上面一般,但是听说在Oracle上面非常好。

group by