List:General Discussion« Previous MessageNext Message »
From:Emanuel Alexandre Tavares Date:May 26 2003 1:51am
Subject:Re: Optimize this query...
View as plain text  
Hi,

Sorry, but what is RSLCOMID?

BTW, if you have a GROUP BY or a ORDER BY with multiples fields, to optimize
it you need an index with this three fields in the same order.

Three diferent indexes don't work.


Emanuel
São Paulo - Brasil


----- Original Message ----- 
From: "Daniel Kasak" <dkasak@stripped>
To: <mysql@stripped>
Sent: Sunday, May 25, 2003 9:56 PM
Subject: Optimize this query...


> 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
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.483 / Virus Database: 279 - Release Date: 19/5/2003

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