Adding current entry to previous with join
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.
Subject
Views
Written By
Posted
Adding current entry to previous with join
3695
July 29, 2008 06:50PM
2255
July 29, 2008 07:31PM
2185
July 30, 2008 01:05AM
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.