Star2Billing/a2billing — pull request #67 by dtucny (2013-09-30 13:51:26+00:00)
Title: Cast cc_call.destination as CHAR to allow the use of indexes

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)