List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:May 26 2003 12:56am
Subject:Optimize this query...
View as plain text  
I have a table:

CREATE TABLE `CallData` (
  `PK` int(10) unsigned NOT NULL auto_increment,
  `ID` varchar(10) default '',
  `Client` varchar(60) default '',
  `Suburb` varchar(30) default '',
  `BillingGroup` varchar(50) default '',
  `CallingNo` varchar(20) default '',
  `CalledNo` varchar(25) default '',
  `CallTypeID` varchar(20) default '',
  `CallType` varchar(60) default '',
  `CallSource` varchar(50) default '',
  `AccountNo` varchar(10) default '',
  PRIMARY KEY  (`PK`),
  KEY `IDX_ID` (`ID`),
  KEY `IDX_AccountNo` (`AccountNo`),
  KEY `IDX_Client` (`Client`),
  KEY `IDX_CallingNo` (`CallingNo`)
) TYPE=MyISAM;

which has around 1 million records.
I have a query:

select RSLCOMID, AccountNo, Client
from CallData
group by RSLCOMID, AccountNo, Client

An 'explain' command gives:

+----------+------+---------------+------+---------+------+--------+---------------------------------+
| table    | type | possible_keys | key  | key_len | ref  | rows   | 
Extra                           |
+----------+------+---------------+------+---------+------+--------+---------------------------------+
| CallData | ALL  | NULL          | NULL |    NULL | NULL | 938904 | 
Using temporary; Using filesort |
+----------+------+---------------+------+---------+------+--------+---------------------------------+

I have indexes on all three fields in the select list. The query takes 
about 90 seconds to run (500Mhz / 256MB). Is there any way to speed the 
query up? Would an index across all three fields improve things?

Thanks!

Dan

-- 
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: www.nusconsulting.com

Thread
Optimize this query...Daniel Kasak26 May
  • Re: Optimize this query...Daniel Kasak26 May
  • Re: Optimize this query...Dan Nelson26 May
  • Re: Optimize this query...Emanuel Alexandre Tavares26 May
  • Re: Optimize this query...Emanuel Alexandre Tavares26 May
    • Re: Optimize this query...Daniel Kasak26 May