List:General Discussion« Previous MessageNext Message »
From:سيد هادی راستگوی حقی Date:February 2 2006 6:18am
Subject:Re: Query Speed
View as plain text  
Another question is that if I run such CREATE TEMPORARY statements in my
query, is MySQL really can do it fast?
Cause this query may be run periodically !


On 2/2/06, سm> wrote:
>
> 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, SGreen@stripped <SGreen@stripped> wrote:
> >
> >
> >
> > سيد هادی راستگوی حقی <hadi.rastgou@gmail.com> 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&amp;id=0&amp;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<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&amp;id=0&amp;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&amp;id=0&amp;t=1">Get
Firefox!</a>
Thread
Query SpeedUnknown Sender1 Feb
  • Re: Query SpeedPeter Brawley1 Feb
  • Re: Query SpeedSGreen1 Feb
    • Re: Query SpeedUnknown Sender2 Feb
      • Re: Query SpeedUnknown Sender2 Feb
      • Re: Query SpeedSGreen2 Feb
        • Re: Query SpeedUnknown Sender3 Feb
          • Re: Query SpeedUnknown Sender8 Feb