MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize sub query with large group by
Posted by: Alex K
Date: March 13, 2011 04:59PM

The events table contains a list of people accessing objects. There is an event logged every time a person accesses an object which points to both the person and the object accessed. There would also be a timestamp (left out here for simplicity).

The aim of the query is to get a report that lists how many times each person accessed any object. The trick is that I only want to count the most recent person to access any particular object.

For example if the event table contained:
+----+-----------+-----------+
| id | object_id | person_id |
+----+-----------+-----------+
| 1  |         1 |         1 |
| 2  |         2 |         2 |
| 3  |         1 |         2 |
+----+-----------+-----------+
The output would be:
+-----------+-----------+
| person_id | count(id) |
+-----------+-----------+
|         2 |         2 |
+-----------+-----------+
This is because even though person 1 accessed object 1, person 2 also accessed object 1 more recently. So only person 2 gets the credit.

The sub-query gets a list containing the most recent person to access each object (The group by object_id makes sure only the most recent event for a given object is shown). Then the the outer query totals this for each person to give the final report.

I am trying to find a way to optimize the sub-query or rewrite the query entirely if there is a better way of getting the same results.

Options: ReplyQuote


Subject
Views
Written By
Posted
3851
March 10, 2011 07:44PM
Re: Optimize sub query with large group by
1867
March 13, 2011 04:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.