Announcement

Announcement Module
Collapse
No announcement yet.

Help Me optimize my query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help Me optimize my query

    Hi I have a table with 2350k records I have the following query

    SELECT Name ,
    count(CASE WHEN P_A = 'Y' and CC = 'Y' THEN Name END),
    count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '1' THEN Name END),
    count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '0' and Score >= 76 and Score < 300 THEN Name END),
    count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '0' and Score >= 300 and Score <= 330 THEN Name END)
    FROM Table1
    WHERE IF($P{StartDate} = $P{EndDate} ,Date_from_Table like CONCAT(DATE_FORMAT(IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()), '%m%d%Y'),"%"), date_format(str_to_date(CONCAT(SUBSTRING(Date_from _Table,1,2 ), "-",LOWER(SUBSTRING(Date_from_Table,4,3)),"-",'20',SUBSTRING(Date_from_Table,8,2)), "%d-%b-%Y"), "%Y-%m-%d") BETWEEN IF(length($P{StartDate}) > 4,$P{StartDate},curdate()) AND IF(length($P{EndDate}) > 4,$P{EndDate},curdate()))
    GROUP BY ClientName;

    Now The first part of the IF condition works quit fast on its own like .15 sec but when I extend it to consider a range of Date It takes about a minute or so.

    Date_from_Table unfortunately is in string format and I cannot at the moment alter the Table.

    IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()) part is for if no date is specified

    Also I would like to get the total of the columns . Can I do that??
Working...
X