Thanks for your suggestion,
I forget to tell that each message in traffic_log may has at least 2 status
in status_log and I use to columns "recipients_id" and "mobile_retry"
to uniquely find each message's statuses.
May be I have to change my tables structure. I don't know.
It's really important for me to show each message with it's last status. So
I have to use group by because in other way such as
SELECT * FROM status_log
ORDER BY time;
returns all statuses in order of time regards to multiple statuses for any
message. so I think that the query may be like this
(SELECT status, recipient_id, time, mobile_retry FROM status_log GROUP BY
recipient_id HAVING time=MAX(time)) AS sts*
JOIN traffic_log ON traffic_log.recipient_id=sts.recipient_id AND
traffic_log.mobile_retry=sts.mobile_retry
*sts --> to find last status of each message
On 2/1/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
>
> سيد هادی راستگوی حقی <[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<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
--
Sincerely,
Hadi Rastgou
<a href="http://www.spreadfirefox.com/?q=affiliates&id=0&t=1";>Get
Firefox!</a>
