Sorry, but you gave us a "best guess" situation. Your tables do not have
any PRIMARY KEYs defined on them so I had to guess at what made each row
in each table unique from all other rows in that table based only on your
sample query.
What value or combination of values will allow me to uniquely identify a
single record from each table? Armed with that information I can rework
my solution to accurately identify what you want to know. My suggestion is
that you add two integer-based auto_increment columns, one to each table,
and make them the PRIMARY KEYs and foreign keys as appropriate.
For example: What makes a single row of traffic_log different from each of
the others? Is it the `recipient_id` column or a combination of values?
Same for the `status_log` table. What makes each row different from all
others? How do I uniquely identify a single row in `traffic_log` that
corresponds to any random row from `status_log`?
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
سيد هادی راستگوی حقی <[EMAIL PROTECTED]> wrote on 02/02/2006
01:14:35 AM:
> 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>
