List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 1 2006 7:20pm
Subject:Re: Query Speed
View as plain text  
سيد هادی راستگوی حقی <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">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
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