search

Star2Billing / a2billing

A2Billing is a telecom switch and billing system capable of providing and billing a range of telecom products and services to customers such as calling card products, residential and wholesale VoIP termination, DID resale and callback services.
www.asterisk2billing.org
Other
180 stars 174 forks source link

Function on query prevents mysql using indexes #131

Closed vyspa closed 8 years ago

vyspa commented 8 years ago

The query below cant use the index on starttime as the function UNIX_TIMESTAMP is used. the function is not necessary.

SELECT DATE(t1.starttime) AS day, sum(t1.sessiontime) AS calltime, sum(t1.sessionbill) AS cost, count(*) as nbcall, sum(t1.buycost) AS buy, sum(case when t1.sessiontime>0 then 1 else 0 end) as success_calls FROM cc_call t1 LEFT OUTER JOIN cc_card t2 ON t2.id = t1.card_id LEFT OUTER JOIN cc_trunk t3 ON t1.id_trunk = t3.id_trunk LEFT OUTER JOIN cc_ratecard t4 ON t1.id_ratecard = t4.id LEFT JOIN cc_card_group ON t2.id_group=cc_card_group.id WHERE UNIX_TIMESTAMP(t1.starttime) >= UNIX_TIMESTAMP('2016-04-11') AND (t1. terminatecauseid=1) AND cc_card_group.id_agent = 30 GROUP BY day ORDER BY day;

This query cant use the index on starttime because the function UNIX_TIMESTAMP is applied as starttime is a datetime field there isnt much point in running the query by converting to unixtime stamp

The difference in the querys with and without the function in a db with millions of rows is 118s vs 2 seconds

areski commented 8 years ago

Could you provide a patch with the tested change?

areski commented 8 years ago

Fixed here https://github.com/Star2Billing/a2billing/commit/2f3379142821bdce8001ade2be70864baed5989e