Any suggestions?
On 2/3/06, سيد هادی راستگوی حقی <[EMAIL PROTECTED]> wrote:
>
> Dear all,
> Thanks for your replies.
>
> The main table for me is traffic_log. I use combination of recipient_id
> and mobile_retry fields to uniquely identify each row in the traffic_log and
> use the same combination on status_log as my foreign key to traffic_log.
> Each message is saved as a row in traffic_log and its statuses are stored
> in status_log.
> The make me join these tow tables on both fields to find each message's
> statuses.
>
> For performing fast query, I always first select messages from traffic_log
> becuase of it's indices and then select last status for each message.
> I mean that i perform 2 separate queries.
>
> But the problem arises when i want to search on statuses, so i have to
> join these large tables and then select base on my status constraint and
> then sort them base on their times. these parts are time consuming and i
> want to make them fast.
>
> As i said before, you may suggest me to restructure my tables.
> I will appreciate your suggestions.
>
> Thanks for your interest to solving my problem.
>
> On 2/2/06, [EMAIL PROTECTED] < [EMAIL PROTECTED]> wrote:
> >
> >
> > 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
> > <http://www.spreadfirefox.com/?q=affiliates&id=0&t=1>">Get
> > > Firefox!</a>
> >
>
>
>
> --
> 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>
>
--
Sincerely,
Hadi Rastgou
<a href="http://www.spreadfirefox.com/?q=affiliates&id=0&t=1";>Get
Firefox!</a>
