Mysql – Grabbing data from a query with specific criteria, then the data around it

MySQL

The title may be slightly vague… however, this is the basis of what I want to do:
I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to lowest, which I can already do, but this is where I run into an issue.

I would like to then have said query find a specific bit of data, a row with a specific 'Name' as defined in the query, then get 5 rows that would be above it, and 5 rows that would be below it. It would also be helpful if it does this in a timely fashion. If you need more detail, just ask!

For anyone wondering what research I've done, I've found queries that will do this, however those queries use full table scans, which would not be optimal in this case as there is 123k+ rows of data. Also, this is in mysql.

My current query that does a full table scan and doesn't work extremely well, nor effeciently, in general:

SELECT    allusers.rank, allusers.Name, allusers.Kills
FROM (
 SELECT   AgStats.Name,
        @t:=IF(score<=>@s,@t,@r) AS rank,
        @s:=AgStats.Kills AS Kills,
        @r:=@r+1
 FROM     AgStats, (SELECT @r:=0, @s:=NULL, @t:=NULL) init
 ORDER BY AgStats.Kills DESC
) allusers, (
 SELECT COUNT(*) AS rank
FROM   AgStats
WHERE  Kills > (SELECT Kills FROM AgStats WHERE Name = 'VoidWhisperer')
) user
WHERE    ABS(allusers.rank-user.rank) <= 5
ORDER BY allusers.rank

To give an example of what I'm trying to do: You have 10 rows of data, with the row kills on the end. I want to select a specific row out of those 10 based on name, then get a number of rows above it, in this case, 5 rows above/below, done via the query?

Best Answer

First a query without rank. You may get more than 11 rows in the result, if there people with same number of Kills as your player:

  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills > k.Kills
    ORDER BY a.Kills ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills < k.Kills
    ORDER BY a.Kills DESC
      LIMIT 5
  ) 
ORDER BY Kills DESC ;

This will show no more than 11 rows, even if there hundreds of players with same number of Kills, by applying an order on the two columns: Kills, Name:

  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills, Name  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills AND a.Name > k.Name
      OR a.Kills > k.Kills
    ORDER BY a.Kills ASC, a.Name ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
    WHERE Name = 'VoidWhisperer'
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills, Name  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills AND a.Name < k.Name
      OR a.Kills < k.Kills
    ORDER BY a.Kills DESC, a.Name DESC
      LIMIT 5
  ) 
ORDER BY Kills DESC, Name DESC ;

It will be good if you have a an index on (Kills, Name) and a (unique) index on (Name).

Tested at SQL-Fiddle

Related Question