[Serusers] optimizing mysql queries

[Iqbal]

Hi

Fixed, use sip_callid..now just need to tune it a little more

Iqbal

On 6/8/2005, "Iqbal" <iqbal at gigo.co.uk> wrote:

>
>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
>
>_______________________________________________
>Serusers mailing list
>serusers at lists.iptel.org
>http://lists.iptel.org/mailman/listinfo/serusers
>
>