[Serusers] optimizing mysql queries

[Iqbal]

Hi

The two queries below being used to pull acc info out, seem very slow,
are there any Mysql whizzes on the list, the db is optimized, but the
second query is just far too long for me to debug.

select count(*) from acc t1 right outer join acc t2 on
t1.sip_callid=t2.sip_callid and ((t1.totag=t2.totag and
t1.fromtag=t2.fromtag) or (t1.totag=t2.fromtag and t1.fromtag=t2.totag))
and t1.sip_method='INVITE' where t2.sip_method='BYE' and
isnull(t1.username)  and (t2.username='040633' and
t2.domain='sip.domain.com') and t2.callee_deleted != '1'


(select t1.to_uri as inv_to_uri, t1.sip_to as inv_sip_to,t1.sip_callid as
inv_callid, t1.time as inv_time, t1.fromtag as inv_fromtag,
t1.sip_status as inv_status, t2.to_uri as bye_to_uri,t2.sip_to as
bye_sip_to, t2.sip_callid as bye_callid,t2.time as bye_time,t2.fromtag
as bye_fromtag,t2.totag as bye_totag,t2.from_uri as
bye_from_uri,t2.sip_from as
bye_sip_from,sec_to_time(unix_timestamp(t2.time)-unix_timestamp(t1.time))
as length,ifnull(t1.time, t2.time) as ttime , 'outgoing' as call_type
from acc t1 left outer join acc t2 on t1.sip_callid=t2.sip_callid and
((t1.totag=t2.totag and t1.fromtag=t2.fromtag) or (t1.totag=t2.fromtag
and t1.fromtag=t2.totag)) and t2.sip_method='BYE' where
t1.sip_method='INVITE' and (t1.username='040633' and
t1.domain='sip.domain.com') and t1.caller_deleted != '1' ) union
(select t1.to_uri as inv_to_uri, t1.sip_to as inv_sip_to,t1.sip_callid
as inv_callid,t1.time as inv_time, t1.fromtag as
inv_fromtag,t1.sip_status as inv_status,t2.to_uri as
bye_to_uri,t2.sip_to as bye_sip_to,t2.sip_callid as bye_callid, t2.time
as bye_time,t2.fromtag as bye_fromtag,t2.totag as bye_totag,t2.from_uri
as bye_from_uri, t2.sip_from as
bye_sip_from,sec_to_time(unix_timestamp(t2.time)-unix_timestamp(t1.time))
as length, ifnull(t1.time, t2.time) as ttime , 'outgoing' as call_type
from acc t1 right outer join acc t2 on t1.sip_callid=t2.sip_callid and
((t1.totag=t2.totag and t1.fromtag=t2.fromtag) or (t1.totag=t2.fromtag
and t1.fromtag=t2.totag)) and t1.sip_method='INVITE' where
t2.sip_method='BYE' and isnull(t1.username)  and
(t2.username='040633' and t2.domain='sip.domain.com') and
t2.callee_deleted != '1' )order by ttime desc limit 0, 20


Now I did a "explain" on these (after having added indexes, since the
default install doesn't add indexes.

+-------+-------+---------------------+------------+---------+-------+------+--------------------------+
| table | type  | possible_keys       | key        | key_len | ref   |
rows | Extra                    |
+-------+-------+---------------------+------------+---------+-------+------+--------------------------+
| t1    | ref   | username,sip_method | username   |      64 | const |
1061 | Using where              |
| t2    | ref   | sip_method          | sip_method |     129 | const |
4052 |                          |
| t2    | ref   | username,sip_method | username   |      64 | const |
1061 | Using where              |
| t1    | ref   | sip_method          | sip_method |     129 | const |
2782 | Using where; Not exists  |
| t1    | index | NULL                | username   |      64 | NULL  |
6837 | Using index              |
| t2    | ref   | sip_method          | sip_method |     129 | const |
4052 | Using where; Using index |
+-------+-------+---------------------+------------+---------+-------+------+--------------------------+

this for the second query, so rough maths tells me that
351554245281666581856 rows must be examined in order to return the data
set..which is alot of rows, in any language.

the explain for the first query returns

+-------+------+---------------------+------------+---------+-------+------+-------------+
| table | type | possible_keys       | key        | key_len | ref   |
rows | Extra       |
+-------+------+---------------------+------------+---------+-------+------+-------------+
| t1    | ref  | username,sip_method | username   |      64 | const |
1061 | Using where |
| t2    | ref  | sip_method          | sip_method |     129 | const |
4052 |             |
+-------+------+---------------------+------------+---------+-------+------+-------------+

which is about 4million.

Iqbal