Re: Query Speed
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 '-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 '-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
Re: Help with query optimization query SUM
Dear Reynier,
You can use JOIN on your both,
The JOIN have to run on the same feilds i.e IDA.
SELECT * FROM carro_de_compras LEFT JOIN os_articulo ON carro_de_compras.IDA
= os_articulo.IDA
This query returns all your users with their articles if any and you can
iterate on it.
but one note:
Use INDEX on both tables. You may encounter problems when your rows grow up.
about the UPDATE query:
UPDATE table SET value=value+1 WHERE id='1'
is enough, use that.
On 2/7/06, Reynier Perez Mira [EMAIL PROTECTED] wrote:
Hi:
I'm develop a simple shopping cart. I have this two tables:
carro_de_compras
--
IDU int(11) NOT NULL
IDA int(11) NOT NULL
CantidadDeArticulos int(11) NOT NULL
os_articulo
--
IDA int(11) NOT NULL auto_increment,
IDC int(11) NOT NULL default '0',
ANombre varchar(200) NOT NULL default '',
ADescripcion text,
ACantidad int(11) NOT NULL default '0',
AImagen varchar(50) default NULL,
IDU int(11) NOT NULL default '0',
APrecio float(6,2) default NULL,
KEY AI_IDA (`IDA`)
Before ask let me explain some things. As you can see in the tables I have
the same field IDU in both tables. So in first(table carro_de_compras) it
means is user ID loged on ecommerce system, the second is the user ID who
upload articles for sale. Something like eBay in wich you can sale and buy
at every time. The arrive the point in wich I need to optimize queries:
PHP Code:
-
$sql = mysql_query(SELECT * FROM carro_de_compras);
$sresultado = mysql_fetch_assoc($sql);
$query = mysql_query(SELECT * FROM os_articulo WHERE
(IDA='.$sresultado['IDA'].'));
while ($record = mysql_fetch_assoc($query)) {
$productos[] = $record;
}
The question for this problem is: exists any way to optimize this query
and leave only in one line? I read in MySQL doc about it and found some
about JOIN but I can't understand how it works. Maybe because I'm cuban and
not understand english as well as I want.
The other questions is how to add some values to a field. For example:
$sql = mysql_query(UPDATE table SET value=value+1 WHERE id='1');
For do this query I do this:
$sql = mysql_query(SELECT value FROM table WHERE id='1');
$result = mysql_query($sql);
$update = mysql_query(UPDATE table SET (value='.$result['value'].' + 1)
WHERE id='1');
So is possible to optimize this query?
Regards
ReynierPM
4to. año Ing. Informática
Usuario registrado de Linux: #310201
*
El programador superhéroe aprende de compartir sus conocimientos.
Es el referente de sus compañeros. Todo el mundo va a preguntarle y él,
secretamente, lo fomenta porque es así como adquiere su legendaria
sabiduría: escuchando ayudando a los demás...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Sincerely,
Hadi Rastgou
a href=http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1;Get
Firefox!/a
Re: MySQL Connection Problem
Hi, Use this : ps awux | grep mysqld -i On 2/4/06, Casey Rhodes [EMAIL PROTECTED] wrote: Casey, in a Terminal window, type ps -ef | grep -i mysql and see if there is a mysqld process actually running and not a zombie (defunct). See if you can connect on the localhost. See if the socket exists. I'd bet that one of those three will find your problem. Please let us know if this helps. -Sheeri I did this and got the following back: ps: illegal option -- f usage: ps [-aACcehjlmMrSTuvwx] [-O|o fmt] [-p pid] [-t tty] [-U user] ps [-L] No idea what this means. What now? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely, Hadi Rastgou a href=http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1;Get Firefox!/a
Re: Query Speed
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 '-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 '-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
Query Speed
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 '-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 '-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=affiliatesamp;id=0amp;t=1;Get
Firefox!/a
Re: Query Speed
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 '-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 '-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=affiliatesamp;id=0amp;t=1http://www.spreadfirefox.com/?q=affiliatesid=0t=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
Re: Query Speed
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, سيد هادی راستگوی حقی [EMAIL PROTECTED] 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, [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 '-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 '-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=affiliatesamp;id=0amp;t=1
http://www.spreadfirefox.com/?q=affiliatesid=0t=1Get
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.htmlhttp://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
