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 175 forks source link

Cast cc_call.destination as CHAR to allow the use of indexes #67

Closed dtucny closed 10 years ago

dtucny commented 10 years ago

Before: mysql> explain SELECT * FROM cc_call,cc_prefix WHERE card_id = 64 AND cc_call.destination = cc_prefix.prefix ORDER BY starttime DESC LIMIT 0,10; +----+-------------+-----------+------+---------------+------+---------+------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+----------+----------------------------------------------+ | 1 | SIMPLE | cc_call | ALL | NULL | NULL | NULL | NULL | 17432949 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | cc_prefix | ALL | PRIMARY | NULL | NULL | NULL | 17174 | Using where; Using join buffer | +----+-------------+-----------+------+---------------+------+---------+------+----------+----------------------------------------------+ 2 rows in set (0.01 sec)

After: mysql> explain SELECT * FROM cc_call,cc_prefix WHERE card_id = 64 AND cast(cc_call.destination as char) = cc_prefix.prefix ORDER BY starttime DESC LIMIT 0,10; +----+-------------+-----------+--------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | cc_call | index | NULL | starttime | 4 | NULL | 10 | Using where | | 1 | SIMPLE | cc_prefix | eq_ref | PRIMARY | PRIMARY | 60 | func | 1 | Using where | +----+-------------+-----------+--------+---------------+-----------+---------+------+------+-------------+ 2 rows in set (0.01 sec)

areski commented 10 years ago

nice fix!

areski commented 10 years ago

next time you do a pull request, push it to the branch "develop" ;)

Thanks!