Get N previous and next records in mariadb / mysql ordered by score table

Question

I have a table containing score (up to 20000 records). I would like to display a scoreboard with a lazy loading function: show only 20 records around the player score and get 20 more previous if he scroll up, or next if he scroll down. This board will be called very often by a big amount of players in same time, so I have to do this in the lightest way.

CREATE TABLE cities (
  cityId SMALLINT UNSIGNED NOT NULL,
  points SMALLINT UNSIGNED NOT NULL, -- not unique at all
  PRIMARY KEY (cityId)
)
ENGINE = INNODB;

ALTER TABLE cities
ADD INDEX points (points);

How can I efficiently get the 10 preceding and 10 following lines, sorted by points descending, of a specified row (WHERE cityId=<myCityId>)

And how can I seek the 20 next ? Because using OFFSET and LIMIT seem not be the best way https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/

Thank you

Edit :

I tried both @Schwern solutions and both doesn't work as expected because I can have lines with same score.

• triple union

select points, cityName from (
    (
        select *  
        from cities
        where points < (select points from cities where cityName = :cityName)
        order by points desc
        limit 5
    )
    union
    select * from cities where cityName = :cityName
    union
    (
        select *
        from cities
        where points >= (select points from cities where cityName = :cityName)
          and cityName != :cityName
        order by points
        limit 5
    )
) t
order by points;

result with limit=5 and cityName=Viry:

points  cityName
0   Nantes
0   Amiens
2223    Roye
3705    Caps City
4446    Toulouse
5187    Viry
5187    Rampillon
5187    Vdr
5187    Chicago
5187    Le Village
5187    Titoucity

Missing lot of lines with same score (ex: 32 lines with points=4446, only one here)

• and Fetching a Row Plus N Rows Either Side in a Single SQL Statement

MariaDB / MySQL version translated from Oracle solution

WITH RECURSIVE boundaries (prevr, nextr, lvl) as (
  select 
    COALESCE(
      (
        select max(c.points)
        from   cities AS c
        where  c.points < c2.points
      ), 
      c2.points
    ) AS prevr,
    COALESCE(
      (
        select min(c.points) 
        from   cities AS c 
        where  c.points > c2.points
      ),
      c2.points
    ) AS nextr,
    1 lvl
  from cities AS c2
  where  cityName = :cityName
  union all
  select 
    COALESCE(
      (
        select max(points) 
        from   cities AS c 
        where  c.points < prevr
      ),
      prevr
    ) AS prevr,
    COALESCE(
      (
        select min(points) 
        from   cities AS c 
        where  c.points > nextr
      ),
      nextr
    ) AS nextr,
    lvl+1 lvl
  from   boundaries
  where  lvl+1 <= :lvl
)
select c.points, c.cityName
from   cities AS c
join   boundaries AS b
on     c.points between b.prevr and b.nextr
and    b.lvl = :lvl
order  by c.points; 

result with lvl=1 and cityName=Viry

points  cityName
4446    Toulouse
4446    Jotown
4446    Guignes
4446    Douns
4446    Colombes
4446    Chambly
4446    Cassandra Gn
4446    Bussyland
4446    Magny Les Hameaux
4446    Palamos
4446    Ville
4446    Loujul
4446    Osny
4446    Sqy
4446    Senlis
4446    Vendres
4446    Amiens
4446    Saint Jean De Luz
4446    Senlis
4446    Abbeville
4446    Ca City
4446    Tolkien
4446    Paiementland
4446    Cash City
4446    Amiens
4446    Beauvais
4446    Kona
4446    St Petaouchnoc'
4446    Amiens
4446    Pick City
4446    Conflans
4446    Versailles          ^ +1
5187    Le Village
5187    Compiegne
5187    Titoucity
5187    Vdr
5187    Rampillon
5187    Chicago
5187    Moustache Ville
5187    Viry                ^  0
5928    Trot Ville          v -1
5928    Amiens
5928    Cityc
5928    Bakel City
5928    Rouen
5928    Noailles
5928    Caps Town
5928    Atlantis
5928    Camon
5928    Smart City
5928    Maville
5928    Azzana
5928    Strasbourg
5928    Sqy Park

It work but I need to decide how much lines I get, sometimes I can have 50 identical scores, sometimes one or two only.

re:edit

retry first solution with second field for order

SET @mypoints := (select points from cities where cityId = :cityId);
select t.points, t.cityId, t.cityName from (
    (
        select *  
        from cities AS c1
        where c1.points <= @mypoints
          AND c1.cityId > :cityId
        order by c1.points DESC, c1.cityId ASC
        limit 5
    )
    union
    select * from cities AS c2 where c2.cityId = :cityId
    union
    (
        select *
        from cities AS c3
        where c3.points >= @mypoints
          AND c3.cityId < :cityId
        order by c3.points ASC, c3.cityId DESC
        limit 5
    )
) t
order by t.points;

result with limit=5 and cityId=36

points  cityId  cityName
0   49  Nantes
1482    53  Paris
1482    51  Mattown
2223    56  Haudiville
3705    37  Caps City
5187    36  Viry           < ==
6669    29  Prospercity
6669    31  Amiens
8892    22  Meteor
20007   34  Ouagadougou
20007   35  Meaux

Same problem as first

Answer 1

Because cities can have the same points, we need to be careful not to duplicate rows between the previous and next.

First, we get the next rows by ordering by points asnd finding those with have equal or more points, excluding the selected city. Simple enough.

select *
from ranking
where points >= (select points from ranking where cityId = :cityId)
  and cityId != :cityId
order by points
limit 10

Then we get the row in question.

select * from ranking where cityId = :cityId

Then we get the previous rows by looking for those with less points, but we have to order by points descending. This gives the results in reverse, we'll fix that in a moment.

select *  
from ranking
where points < (select points from ranking where cityId = :cityId)
order by points desc
limit 10

We can put these all together into one query with unions. Ordering the combined queries fixes the problem of the previous rows being reversed.

select * from (
    (
        select *  
        from ranking
        where points < (select points from ranking where cityId = :cityId)
        order by points desc
        limit 10
    )
    union
    select * from ranking where cityId = :cityId
    union
    (
        select *
        from ranking
        where points >= (select points from ranking where cityId = :cityId)
          and cityId != :cityId
        order by points
        limit 10
    )
) t
order by points;

I benchmarked this vs limit/offset by generating 200,000 random dates. There is a notable performance improvement. Not nearly as dire as you read on the internet, but that might be hardware differences.

Using union takes < 10 ms. limit 10 offset X scales up with X, at 50,000 it takes 20 to 120 ms depending on whether it needs a filesort.

---

Another option is outlined in Fetching a Row Plus N Rows Either Side in a Single SQL Statement. Because it's written for Oracle, replace nvl with coalesce.