MySQL - GROUP BY

[elsc]

Hello,

 

I created a table named "syslog" to store users activity,

CREATE TABLE `syslog` (
  `id` int(11) NOT NULL auto_increment,
  `table_name` varchar(100) NOT NULL default '',
  `row_id` int(11) NOT NULL default '0',
  `log_date` datetime default NULL,
  `actions` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


INSERT INTO `syslog` VALUES (1,'user_profile',1,'2009-09-11 01:03:43','add record');
INSERT INTO `syslog` VALUES (2,'user_profile',2,'2009-09-11 01:04:43','add record');
INSERT INTO `syslog` VALUES (3,'user_profile',1,'2009-09-13 09:11:00','edit record');
INSERT INTO `syslog` VALUES (4,'user_profile',3,'2009-09-15 09:23:43','add record');
INSERT INTO `syslog` VALUES (5,'user_profile',1,'2009-09-17 12:00:00','delete record');
INSERT INTO `syslog` VALUES (6,'user_profile',2,'2009-09-18 12:00:00','view record');

 

how to select latest activity from syslog?

here is my sql statement

SELECT * FROM syslog
WHERE table_name = 'user_profile'
GROUP BY row_id
ORDER BY log_date DESC

and it returned:

 

4,"user_profile",3,"2009-09-15 09:23:43","add record"

2,"user_profile",2,"2009-09-11 01:04:43","add record"

1,"user_profile",1,"2009-09-11 01:03:43","add record"

 

but it should be

 

4,"user_profile",3,"2009-09-15 09:23:43","add record"

2,"user_profile",2,"2009-09-11 01:04:43","view record"

1,"user_profile",1,"2009-09-11 01:03:43","delete record"

 

 

 

[gizmola]

What you're doing doesn't make sense.  When you group rows together, you in essence reduce multiple rows to single rows.  The dates at that point are unpredictable as are the values of any other columns.  You could use a summary operator like this to handle the date, but I suspect that is not going to be what you are after.  So for example, you could issue:

 

select row_id, MAX(log_date) as max_log_date

FROM syslog

WHERE table_name = 'user_profile'

GROUP BY row_id

ORDER BY max_log_date desc

 

And you will find the dates to be what you expect, but the actions column will not be predictable. 

[elsc]

i need to display the actions because i want to know when and what happened.

 

i have tried this way and it is working, but the speed seems slow when syslog have large amount of records and joined to > 4 tables.

 

SELECT table_name, row_id, log_date ,actions
FROM syslog  
WHERE id IN
(SELECT MAX(id) FROM syslog WHERE table_name = 'user_profile'   GROUP BY row_id)
ORDER BY log_date DESC;

 

 

is it any better way to run it faster?

[gizmola]

Well, your bottom query is totally different from the top one.  Needless to say, the bottom one will be as slow as the number of row_id's it has to churn through, and it will get slower and slower because you are

 

-probably table scanning the entire syslog table, since I'm going to assume that the table_name has low cardinality in the syslog table,

-- then grouping (a temp table probably)

---  then passing the entire list of row_id's in the correlated subquery back to the main query

-returning a lot of rows

 

While I can surmise this is some sort of generic logging system, I don't know what the significance of row_id is, although again I can surmise that it's the primary key of the table in question.

 

Last but not least, I don't know what you are trying to get out of the query.  Is this suppossed to list just the last few transactions?  Some number of transactions? 

 

Hard to say, without a clear requirement from you.

[kickstart]

Hi

 

While I agree with the above that more details are required, this mod to you existing SQL should improve things (if that is what you want).

 

SELECT a.table_name, a.row_id, a.log_date ,a.actions
FROM syslog a
JOIN (SELECT row_id, MAX(id) maxid FROM syslog WHERE table_name = 'user_profile' GROUP BY row_id) b
ON a.row_id = b.row_id AND a.id = b.maxid
ORDER BY log_date DESC;

 

All the best

 

Keith

[elsc]

I have 50+ tables in my database: customers, user_profiles, sales, purchase_order .. etc..

and I have to log every activity, example:

when a user add a record to sales, my php script will insert a row into

syslog with user_id(Current User), table_name(sales), row_id(Last ID from sales), log_date(current date), actions(add/edit/delete/print).

 

I need to generate a log report to view latest action taken by user in sales table,

row_id 1 have more than 1 rows in syslog example 1/1/2009 - add record, 3/1/2009 - edit record, 3/4/2009 - print record and so on ...

how to write a good statement to query latest actions group by row_id in sales ?

and i will filter the result by actions or by user_id.