List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:May 26 2003 1:07am
Subject:Re: Optimize this query...
View as plain text  
Daniel Kasak wrote:

> 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
>
Ooooops.
That query should have been:

select ID, AccountNo, Client
FROM CallData
GROUP BY ID, AccountNo, Client

The RSLCOMID field has just been renamed to ID...

-- 
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