Mysql get target row number ±2 rows

Question

I have a My_Table that looks something like this: PK1 | PK2 | Value | Date_Changed

And a query that does essentially this:

SELECT 
  PK1,
  PK2,
  Value,
  ROW_NUMBER() OVER ( ORDER BY Value desc, Date_Changed ASC) AS position
FROM My_Table
    where PK1 = 1;

Then I get the position from that query result where PK2 = myValue.
And using that position, I go back to that query, and get rows with that position ± 2 rows.

I'm using Mysql 5.7, so I have to do it like:

SET @rownum = 0;
SET @bingo = NULL;

SELECT p.PK2 AS PK2,
       p.Value,
       p_o.Position
FROM My_Table p
         JOIN (
    SELECT PK1,
           PK2,
           @rownum := @rownum + 1 AS Position,
           CASE
               WHEN PK2 = param_PK2
                   THEN @bingo := @rownum
               ELSE 0
               END                AS bbb
    FROM My_Table
    WHERE PK1 = param_PK1
    ORDER BY value DESC, Date_Changed ASC
) p_o
              ON p.PK1 = p_o.PK1
                  AND p.PK2 = p_o.PK2
                  AND @bingo IS NOT NULL
                  AND Position < @bingo + offset
                  AND Position > @bingo - offset
ORDER BY p_o.Position;

This is a horrible query, when done frequently.
Is there any way I can make it lighter?
Since it's Mysql 5.7 I've tried adding an inverted value column to value column, since indexes are skipped on DESC, but it did not help much.

I also tried to do it via ROW_NUMBER window function, as an experiment on mysql 8, but results are not great...

Would a cursor help in this case?

score 0 · Answer 1 · answered May 22 '20 at 16:01

In MySQL 8.0, a common-table-expression might come handy here:

with cte as (
    select t.*, row_number() over(order by value desc, date_changed desc) position
    from mytable t
)
select c.* 
from cte c
inner join cte c1 on c.rn between c1.rn - 2 and c1.rn + 2
where c1.pk1 = 1 and c1.pk2 = 'myvalue'

score 0 · Answer 2 · answered May 22 '20 at 16:05

Given

+-----+------------+
| id  | dte        |
+-----+------------+
| 846 | 2020-04-26 |
| 847 | 2020-04-27 |
| 848 | 2020-04-28 |
| 849 | 2020-04-29 |
| 850 | 2020-04-30 |
| 851 | 2020-05-01 |
| 852 | 2020-05-02 |
| 853 | 2020-05-03 |
| 854 | 2020-05-04 |
+-----+------------+
9 rows in set (0.002 sec)

select id,dte
from dates
where dte = '2020-05-01'
union
(select id,dte
from dates
where dte < '2020-05-01'
order by dte desc limit 2
)
union all
(select id,dte
from dates
where dte > '2020-05-01'
order by dte limit 2
)
order by dte;

+-----+------------+
| id  | dte        |
+-----+------------+
| 849 | 2020-04-29 |
| 850 | 2020-04-30 |
| 851 | 2020-05-01 |
| 852 | 2020-05-02 |
| 853 | 2020-05-03 |
+-----+------------+
5 rows in set (0.004 sec)

Mysql get target row number ±2 rows

2 Answers2