CODEWITHSUNDEEP
mysqlperformanceselect
Nitsan Baleli
Nitsan Baleli

Reputation: 5458

MySQL query not efficient (PHP search)

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?

Upvotes: 0

Views: 92

Answers (1)

mhafellner
mhafellner

Reputation: 468

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.

Upvotes: 1

Related Questions