MySQL Forums
Forum List  »  Optimizer & Parser

Adding current entry to previous with join
Posted by: Kevin Regan
Date: July 29, 2008 06:50PM

I have a table structure similar to the following:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table(
group_uid INT UNSIGNED NOT NULL,
insert_order INT UNSIGNED NOT NULL,
t TIMESTAMP,
value INT UNSIGNED
);

The data looks something like this:

+-----------+--------------+---------------------+-------+
| group_uid | insert_order | t | value |
+-----------+--------------+---------------------+-------+
| 1 | 1 | 2008-07-29 17:34:01 | 520 |
| 1 | 2 | 2008-07-29 17:35:01 | 47 |
| 1 | 3 | 2008-07-29 17:36:01 | 535 |
...
| 2 | 1 | 2008-07-29 17:34:01 | 120 |
| 2 | 2 | 2008-07-29 17:35:01 | 81 |
| 2 | 3 | 2008-07-29 17:36:01 | 654 |
...
| 3 | 1 | 2008-07-29 17:34:01 | 75 |
| 3 | 2 | 2008-07-29 17:35:01 | 125 |
| 3 | 3 | 2008-07-29 17:36:01 | 333 |
...

I would like to execute the following query to add the previous inserted row for each group to the current row, based on insert_order and a range of times:

SELECT
c.group_uid,
c.insert_order,
c.t,
c.value,
p.t,
p.value
FROM my_table c, my_table p
WHERE (c.group_uid = p.group_uid) AND
(c.insert_order = (p.insert_order + 1)) AND
(c.group_uid = 8) AND
(c.t >= '2008-07-29 18:14:08' AND c.t <= '2008-07-29 18:37:08');

However, no matter what indexes I create, I can't seem to convince MySQL to use them. Any suggestions on optimizing this.

Options: ReplyQuote


Subject
Views
Written By
Posted
Adding current entry to previous with join
3695
July 29, 2008 06:50PM


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.