سيد هادی راستگوی حقی <[EMAIL PROTECTED]> wrote on 02/01/2006
11:07:49 AM:
> 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&id=0&t=1";>Get
> Firefox!</a>
This is another variant of the groupwise maximum pattern of query:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
My favorite way to write these kinds of queries is to make a temporary
table (or more if needed) identifying the group and max-per-group then
using that temp table to create the final query. In your case, the group
is the recipient_id and the max-per-group will be MAX(`time`);
CREATE TEMPORARY TABLE tmpLastStatusTimes SELECT
`recipient_id`
,MAX(`time`) as lastmsg
FROM status_log;
CREATE TEMPORARY TABLE tmpLastStatuses SELECT
sl.*
FROM status_log sl
INNER JOIN tmpLastStatusTimes lst
ON lst.`recipient_id` = sl.`recipient_id`
AND lst.lastmsg = sl.`time`;
SELECT * from traffic_log
LEFT JOIN tmpLastStatuses
ON traffic_log.recipient_id=tmpLastStatuses.recipient_id
AND traffic_log.mobile_retry=tmpLastStatuses.mobile_retry
WHERE account_id = 32
order by time;
DROP TEMPORARY TABLE tmpLastStatuses, tmpLastStatusTimes;
In your specific sample, you were only looking for messages from a
particular person (account 32) so we may be able to speed up my example
even more if we change the first statement of this query to read:
CREATE TEMPORARY TABLE tmpLastStatusTimes SELECT
sl.`recipient_id`
,MAX(sl.`time`) as lastmsg
FROM status_log sl
INNER JOIN traffic_log tl
on tl.`recipient_id` = sl.`recipient_id`
and tl.account_id = 32;
That way we keep that table's contents within the scope of the actual
desired results instead of computing the most recent statues for ALL
messages for EVERYONE.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
