CODEWITHSUNDEEP
mysql
Craig Fitches
Craig Fitches

Reputation: 51

mySQL Optimisation of query

I'm struggling to optimise this SQL code, can someone please help? Ideally I need the data outputted exactly the same to avoid any php coding.

I basically want to be able to group tariff_freemins by a set range. The below code works but it takes over 2 seconds to run which for a website isn't really quick enough. Is there an optimised version of the code. The table has an index on tariff_freemins setup. 

SELECT 'Free Mins' as category,
       'tariff_freemins' as fieldname, 
       t.range as `element`, 
       count(*) as phone_count 
from ( select deal_count, 
              case 
                when tariff_freemins between 0 and 200 then 'a0TO200' 
                when tariff_freemins between 200 and 400 then 'b200TO400' 
                when tariff_freemins between 400 and 600 then 'c400TO600' 
                when tariff_freemins between 600 and 1000 then 'd600TO1000' 
                when tariff_freemins between 1000 and 2000 then 'e1000TO2000'
                when tariff_freemins >2001 then 'hUnlimited' 
              end as `range` 
       from options
) t group by t.range

The output should be something like the below:

"category"  "fieldname" "element"   "phone_count"
"Data Allowance"    "tariff_dataallowance"  "a0TO1" "289716"
"Data Allowance"    "tariff_dataallowance"  "b1TO2" "64472"
"Data Allowance"    "tariff_dataallowance"  "c2TO5" "114685"
"Data Allowance"    "tariff_dataallowance"  "d5TO11"    "33305"
"Data Allowance"    "tariff_dataallowance"  "e11TO20"   "36798"
"Data Allowance"    "tariff_dataallowance"  "f20TO50"   "5839"
"Data Allowance"    "tariff_dataallowance"  "hUnlimited"    "51114"

UPDATE ////////////////////

Secondly, I'm using many of the above mySQL queries to generate a master table. Each of these have separate group by's and are joined using a UNION ALL (See below). As you can see there is a where clause which states "Where type = 'Contract'". I can only assume there is a way to using the same filtered options table on all of the group by queries? How would this look? Is there a better way to optimise this? Thanks!

SELECT 'Phone Cost' as category,'offer_phonecost' as fieldname, offer_phonecost_range as `element`, count(*) as phone_count  from
options
WHERE 1  AND   type = 'Contract'   group by offer_phonecost_range UNION ALL 

SELECT 'Monthly Cost' as category,'offer_offerental' as fieldname, offer_offerental_range as `element`, count(*) as phone_count  from 
options
WHERE 1  AND   type = 'Contract'   GROUP BY offer_offerental_range UNION ALL 

SELECT 'Data Allowance' as category,'tariff_dataallowance' as fieldname, tariff_dataallowance_range as `element`, count(*) as phone_count  from 
options
WHERE 1  AND   type = 'Contract'   GROUP BY tariff_dataallowance_range

Upvotes: 0

Views: 55

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Create this index:

create index opt_tariff on options( tariff_freemins );

and rewrite the query to this:

select 'Free Mins' as category,
       'tariff_freemins' as fieldname, 
        case 
                when tariff_freemins between 0 and 200 then 'a0TO200' 
                when tariff_freemins between 200 and 400 then 'b200TO400' 
                when tariff_freemins between 400 and 600 then 'c400TO600' 
                when tariff_freemins between 600 and 1000 then 'd600TO1000' 
                when tariff_freemins between 1000 and 2000 then 'e1000TO2000'
                when tariff_freemins >2001 then 'hUnlimited' 
        end as  `element`, 
        sum(cnt) as phone_count 
from (
      select  tariff_freemins, count(*) As cnt
      from options
      group by  tariff_freemins
) x
group by  element
;

The inner subquery uses the index to optimize GROUP BY and it is fast.
The outer query cannot be optimized - it cannot be in MySql, since MySql does not support expression based indexes.
However the outer query performs an aggregation on much smaller set of data, that is already preagregated by the inner subquery, and the whole query should be faster than your's version.

Queries like this can be easily optimized in other RDBMS like Oracle, PostgreSQL and SQL-Server, using expression based indexes.

Upvotes: 1

Related Questions