List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 2 2006 4:29pm
Subject:Re: Query Speed
View as plain text  
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



سيد هادی راستگوی حقی <hadi.rastgou@stripped> 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, SGreen@stripped <SGreen@stripped> 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
> >
> > 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">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