title: Mysql get target row number ±2 rows
body: <p>I have a <code>My_Table</code> that looks something like this:
  <code>PK1 | PK2 | Value | Date_Changed</code></p>
  
  <p>And a query that does essentially this:</p>
  
  <pre><code>SELECT 
    PK1,
    PK2,
    Value,
    ROW_NUMBER() OVER ( ORDER BY Value desc, Date_Changed ASC) AS position
  FROM My_Table
      where PK1 = 1;
  </code></pre>
  
  <p>Then I get the <code>position</code> from that query result where <code>PK2 = myValue</code>.<br>
  And using that <code>position</code>, I go back to that query, and get rows with that <code>position</code> ± 2 rows.</p>
  
  <p>I'm using Mysql 5.7, so I have to do it like:</p>
  
  <pre><code>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 &lt; @bingo + offset
                    AND Position &gt; @bingo - offset
  ORDER BY p_o.Position;
  </code></pre>
  
  <p>This is a horrible query, when done frequently.<br>
  Is there any way I can make it lighter?<br>
  Since it's Mysql 5.7 I've tried adding an inverted value column to <code>value</code> column, since indexes are skipped on <code>DESC</code>, but it did not help much.</p>
  
  <p>I also tried to do it via <code>ROW_NUMBER</code> window function, as an experiment on mysql 8, but results are not great...</p>
  
  <p>Would a cursor help in this case?</p>