0

Is there anyone how to minimize the data scanned on my database?? please show me.. using index?

query

        SELECT 
        IF (e.channel LIKE 'IAX2%', e.calldate,
            IF(d.channel LIKE 'IAX2%',d.calldate,
                 IF(c.channel LIKE 'IAX2%',c.calldate,
                   IF(b.channel LIKE 'IAX2%',b.calldate,
                     a.calldate )))) 
                     AS true_calldate,

        IF (e.channel LIKE 'IAX2%', e.src,
            IF(d.channel LIKE 'IAX2%',d.src,
                 IF(c.channel LIKE 'IAX2%',c.src,
                   IF(b.channel LIKE 'IAX2%',b.src,
                     a.src )))) 
                     AS true_source,

        IF (e.channel LIKE 'IAX2%', e.dst,
            IF(d.channel LIKE 'IAX2%',d.dst,
                 IF(c.channel LIKE 'IAX2%',c.dst,
                   IF(b.channel LIKE 'IAX2%',b.dst,
                     a.dst )))) 
                     AS true_destination, a.dcontext AS context, a.channel AS trim_channel          

        FROM 
        cdr a
        LEFT JOIN cdr b 
        ON (
        SUBSTRING(a.channel, 1, CHAR_LENGTH(a.channel) - 2) = SUBSTRING(b.dstchannel, 1, CHAR_LENGTH(b.dstchannel) - 2)            
        )      
        LEFT JOIN cdr c ON (
        SUBSTRING(b.channel, 1, CHAR_LENGTH(b.channel) - 2) = SUBSTRING(c.dstchannel, 1, CHAR_LENGTH(c.dstchannel) - 2)            
        )        
        LEFT JOIN cdr d ON (
        SUBSTRING(c.channel, 1, CHAR_LENGTH(c.channel) - 2) = SUBSTRING(d.dstchannel, 1, CHAR_LENGTH(d.dstchannel) - 2)            
        )        
        LEFT JOIN cdr e ON (
        SUBSTRING(d.channel, 1, CHAR_LENGTH(d.channel) - 2) = SUBSTRING(e.dstchannel, 1, CHAR_LENGTH(e.dstchannel) - 2)            
        )         
        WHERE a.accountcode = 003586 AND 
        a.dstchannel = '' AND 
        (a.channel LIKE 'LOCAL%' OR a.channel LIKE 'IAX2%')
        ORDER BY a.calldate

here is the query EXPLAIN output

  id    select_type     table   type    possible_keys               key             key_len     ref     rows    Extra   
  1     SIMPLE          a       ref     accountcode,accountcode_2   accountcode     4           const   334     Using where; Using temporary; Using filesort
  1     SIMPLE          b       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          c       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          d       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          e       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
0

You should give CREATE TABLE statements or at least info about what is indexed and how. But what I can gather from what you posted:

JOIN on functions is bad, not indexable. If you cannot get rid of those substrings, you might store them precomputed in separate columns and index those (at least the dstchannel ones should be enough, because of current join order).

You can extend index on accountcode with dstchannel for better selectivity and calldate to get rid of the filesort.

The OR over a.channel is not very indexable as it is, but as it uses prefix, you might split your query into two, each one using one part of that OR (and "glue" them with UNION) and replace calldate with channel in that index, it will mean doing filesort, but can be better if those conditions on channelare selective enough (you will have to test that probably to find out which variant is better).

  • can you please show me the script sir?? – Bee Jul 27 '15 at 7:14
  • @Bee I can not. You did not give enough information about tables' structure and schema design. I wrote 3 different things you can change, but testing it and deciding the right way for your case is up to you. – jkavalik Jul 27 '15 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.