MySQL query not efficient (PHP search)

Question

I want to show the user, how many calls he made by country prefix.

I need to combine the results by country (since every country code has many sub-codes for cities/regions)

My current query takes 80 seconds to complete, on top of that the query runs again for pagination purposes, and another query for the total amount of calls/time.

This is a very heavy DB (1gb+) every search can take a couple of minutes to complete and I wonder if I can make it more reasonable.

Here's a SQLfiddle

queries:

SELECT 
  sec_to_time(avg(t1.sessiontime)) as aloc,
  CONCAT(TRUNCATE(sum(t1.terminatecauseid = 1) * 100 /   count(*),1),'%') as asr,
  count(t1.terminatecauseid = 1) as calls,
  cast(t4.countryprefix as unsigned) as prefix,
  t4.countryname as destination,
  SEC_TO_TIME(sum(t1.sessiontime)) as duration
FROM
  cc_call AS t1
    inner join
  cc_prefix as t2 ON t1.destination = t2.prefix
    inner join
  cc_country as t4 ON t1.destination like CONCAT(t4.countryprefix, '%')
WHERE
  t1.card_id = '97' AND t1.starttime >= ('2013-04-1')
group by t4.countryprefix
order by duration DESC
LIMIT 0 , 100

The idea here is to iterate over all cc_country prefixes, and see if they match 'destination' in cc_call using LIKE. Again, this is a very big DB, is there a better way to perform this query?

Did you try Explain Plan and see which part of the query is slowing down? — Kevin Rave
– Kevin Rave, Commented Sep 3, 2014 at 15:11
Another hackey way is to remove on of the columns in the select query and try if it makes it faster. Likewise, try with all the columns and the conditions as well to see which part of the query is slowing down — Kevin Rave
– Kevin Rave, Commented Sep 3, 2014 at 15:12
Is there any way you could extract the countryprefix from the destination and store it seperately? I expect this would speed up the join (you could then join on two fixed length int fields for example) and therefore the whole query massively. — mhafellner
– mhafellner, Commented Sep 3, 2014 at 15:17
@KevinRave Never used 'Explain Plan', I removed all but count() from select - same result, even removed the inner join of cc_prefix - same result. I guess it may be the LIKE clause in the third join ? — Nitsan Baleli
– Nitsan Baleli, Commented Sep 3, 2014 at 15:26
@Markus403 I may not change the existing DB, nor add columns to the tables. You're idea is to get rid of the LIKE and just match prefixes ? — Nitsan Baleli
– Nitsan Baleli, Commented Sep 3, 2014 at 15:28

mhafellner · Accepted Answer · 2014-09-04 10:26:00Z

1

So one solution to get rid of the like is this:

SELECT 
    avg(t1.sessiontime) as aloc,
    CONCAT(TRUNCATE(sum(t1.terminatecauseid = 1) * 100 / count(*),1),'%') as asr,
    count(t1.terminatecauseid = 1) as calls,
    t4.countryprefix as prefix,
    t4.countryname as destination,
    sum(t1.sessiontime) as duration
FROM
    cc_call AS t1
        inner join
    cc_country as t4 ON left(t1.destination,length(t4.countryprefix)) = t4.countryprefix
WHERE
    t1.card_id = '97' AND t1.starttime >= ('2013-04-1')
GROUP by t4.countryprefix
ORDER by duration DESC
LIMIT 0 , 100

I also added an index on destination and countryprefix to possibly speed up the join.

If the changes I made really have an effect on your query with the data you have has to be tried out by you.

Additionally you got the SQLFiddle here.

And some usefull information about Query optimizing here.

edited Sep 4, 2014 at 10:26

answered Sep 4, 2014 at 9:18

mhafellner

4683 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nitsan Baleli Over a year ago

For some reason your query takes 15 secs longer, Ill accept your answer for the additional links about Query optimization, thanks.

mhafellner Over a year ago

Sorry to hear that.. have you tried just adding the index and keeping the like? The problem is still that you have to split the destination first before you are able to join on it - no wonder it has poor performance. My solution was just another way to achieve that... apparently not a better way.

mhafellner Over a year ago

btw I just found a problem that again strongly implicates you need to split the country prefix from the destination: have a look at this fiddle. The last 3 rows you have the call with id 13902512 twice.

mhafellner Over a year ago

@NitsanBaleli are you aware of this problem I described?

Nitsan Baleli Over a year ago

could you please explain again what the problem is and where? thank you

|

Collectives™ on Stack Overflow

MySQL query not efficient (PHP search)

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related