Query Speed

[سيد هادی راستگوی حقی]

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>