Re: Query Speed

Wed, 01 Feb 2006 10:48:31 -0800

Hadi,
>But it's very slow.
>Do you have any suggestions to fast it?
Your query calls no aggregate functions, so what do you mean to achieve by GROUP BY ... HAVING? For example this bit of logic extracted from your query ...

SELECT * FROM table
GROUP BY pkcol
HAVING pkcol=MAX(pkcol)

is logically equivalent to ...

SELECT * FROM table
ORDER BY pkcol;

if pkcol is unique but orders of magnitude slower. If you want the maximum time for each recipient_id, you need something like ...

SELECT
  recipient_id,
  ...,
  MAX(time)
...
GROUP BY recipient_id;

PB

-----

سيد هادی راستگوی حقی wrote: 
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="" class="moz-txt-link-rfc2396E" href="http://www.spreadfirefox.com/?q=affiliates&id=0&t=1">"http://www.spreadfirefox.com/?q=affiliates&amp;id=0&amp;t=1">Get
Firefox!</a>
  

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006
  
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/248 - Release Date: 2/1/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to