Re: [Serusers] optimizing mysql queries

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(a)lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers