Re: Query Speed

[Peter Brawley]

Hadi,

>But it's very slow.
>Do you have any suggestions to fast it?

Your query calls no aggregate functions, so what do you mean to achieve 
by GROUP BY ... HAVING? For example this bit of logic extracted from 
your query ...

SELECT * FROM table
GROUP BY pkcol
HAVING pkcol=MAX(pkcol)

is logically equivalent to ...

SELECT * FROM table
ORDER BY pkcol;

if pkcol is unique but orders of magnitude slower. If you want the 
maximum time for each recipient_id, you need something like ...

SELECT
  recipient_id,
  ...,
  MAX(time)
...
GROUP BY recipient_id;

PB

-----

سيد هادی راستگوی حقی wrote:
> Dear All,
> I need your suggestions please.
>
> have to large tables with these schemas:
>
>  Table: traffic_log
> Create Table: CREATE TABLE `traffic_log` (
>   `recipient_id` int(11) NOT NULL default '0',
>   `retry` smallint(4) NOT NULL default '0',
>   `mobile_retry` tinyint(1) NOT NULL default '0',
>   `orig` varchar(13) default NULL,
>   `dest` varchar(13) default NULL,
>   `message` text,
>   `account_id` int(11) NOT NULL default '0',
>   `service_id` int(11) NOT NULL default '0',
>   `dir` enum('IN','OUT') NOT NULL default 'IN',
>   `plugin` varchar(30) NOT NULL default 'UNKNOWN',
>   `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
>   `replied` tinyint(4) default '0',
>   KEY `account_id` (`account_id`,`recipient_id`,`mobile_retry`),
>   KEY `account_id_2` (`account_id`,`date_entered`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> Table: status_log
> Create Table: CREATE TABLE `status_log` (
>   `recipient_id` int(11) NOT NULL default '0',
>   `retry` smallint(4) NOT NULL default '0',
>   `mobile_retry` tinyint(1) NOT NULL default '0',
>   `status` smallint(5) NOT NULL default '0',
>   `time` datetime NOT NULL default '0000-00-00 00:00:00',
>   `smsc` varchar(20) NOT NULL default '',
>   `priority` tinyint(2) unsigned NOT NULL default '0',
>   `ack` varchar(30) NOT NULL default '',
>   KEY `recipient_id_2` (`recipient_id`,`mobile_retry`,`time`,`status`),
>   KEY `time` (`time`,`recipient_id`,`mobile_retry`,`status`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> I want to execute a query to find out each last message's status. So my
> query is :
>
> select * from traffic_log LEFT JOIN status_log ON
> traffic_log.recipient_id=status_log.recipient_id and
> traffic_log.mobile_retry=status_log.mobile_retry WHERE  account_id = 32
> group by status_log.recipient_id HAVING time=max(time) order by time;
>
> And MySQL explanation about this query is:
> *************************** 1. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: traffic_log
>          type: ref
> possible_keys: account_id,account_id_2
>           key: account_id
>       key_len: 4
>           ref: const
>          rows: 1049598
>         Extra: Using temporary; Using filesort
> *************************** 2. row ***************************
>            id: 1
>   select_type: SIMPLE
>         table: status_log
>          type: ref
> possible_keys: recipient_id_2
>           key: recipient_id_2
>       key_len: 5
>           ref: smse.traffic_log.recipient_id,smse.traffic_log.mobile_retry
>          rows: 2
>         Extra:
>
> as you see return records are 1049598.
>
> But it's very slow.
>
> Do you have any suggestions to fast it?
>
> --
> Sincerely,
> Hadi Rastgou
> <a
> href="http://www.spreadfirefox.com/?q=affiliates&amp;id=0&amp;t=1">Get
> Firefox!</a>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006
>   

Attachment: [text/html]

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006