Complex database query

Question

PHP

There are 2 tables:
PlayersData:
uid int PK
score int
level int

MapsScores
map_id int PK
user_id int PK
score int

The PlayersData table stores: the player's best result for all games (score), his id and level (level). The MapsScores table stores: map id (map_id), user id (user_id) and the score received by the player on the map (score). The MapsScores table also has a composite key by map id and player id.

The task is to build a top of players for a certain map, this query turned out like this (for example, we will output for a map with id = 3):

SELECT 
   ms.user_id uid, ms.score, pd.level
FROM
    MapsScores ms
        LEFT JOIN PlayersData pd ON ms.user_id = pd.uid
WHERE
    ms.map_id = 3
ORDER BY ms.score DESC
LIMIT 20

Everything is simple here, the top 20 results are taken and displayed:
user_id
score
level

There are 50 such cards, and for each one you need to build the top players. It turns out that in a php script I sort through the id of the cards in a cycle and make 50 requests to the database, with the subsequent saving of the data.
But there are 2 problems here:
- id cards can go out of order
- Somehow I don’t want to make a cycle of 50 requests to the database at a time.

Now a question. Is it possible to make such a query that would immediately display the tops for all cards at
least
in this form : Where, column 1 - card id;

column 2 - player id
column 3 - points
column 4 - level

I.e. in fact, in the request itself, iterate over all the id cards and write the top players to the output. Further, I can already determine in php which row of the result belongs to which table.

Answer 1

If you uploaded the test data somewhere, it would be easier to check, but there is such a trick with MySQL - it's not a fact that it will work faster, and it looks creepy, so the choice to use it or not is entirely up to you :)

SELECT 
  t.map_id,
  t.score,
  pd.level 
FROM
  (SELECT 
    ms1.map_id,
    ms1.score,
    ms1.user_id 
  FROM
    MapsScores ms1 
    LEFT JOIN MapsScores ms2 
      ON ms1.map_id = ms2.map_id 
      AND ms1.score >= ms2.score 
  GROUP BY ms1.map_id,
    ms1.score 
  HAVING COUNT(*) <= 20) t 
  LEFT JOIN PlayersData pd 
    ON t.user_id = pd.uid 
ORDER BY 1,
  2 DESC

Answer 2

SELECT p.uid, p.level, p.score, m.map_id FROM PlayersData p INNER JOIN MapsScores m ON m.map_id=p.map_id ORDER BY p.score DESC GROUP BY p.map_id HAVING COUNT(p.uid) <= 20
Something like this, maybe? Alas, I can't check.

Answer 3

Don't make a bulky request.
> map ids may not be in order
, this is not a problem, get a list of your map ids and foreach, or disinct by MapsScores and foreach

Answer 4

Well, if you want to get mad :)

SET @place := 0, @map_id := 0;

SELECT 
  IF(@map_id != map_id, @place := 1, @place := @place + 1) AS place, 
  IF(@map_id != map_id, @map_id:=map_id, map_id) AS map_id,  
  user_id, map_score , level, @place
FROM (
  SELECT m.map_id, m.user_id, m.score AS map_score, p.level
    FROM MapsScores AS m, PlayersData AS p 
   WHERE m.user_id = p.id
   GROUP BY m.map_id, m.user_id 
   ORDER BY m.map_id ASC, m.score DESC
) AS tops
GROUP BY map_id, user_id HAVING place <= СКОЛЬКО_НАРОДУ_ХОТИТЕ_В_ТОП
ORDER BY map_id ASC, map_score DESC

but it's easier to make 50 requests for each card separately - the muscle will be grateful to you :)

Answer 5

This problem is many years old, and a lot has been written about it on various resources ( search ). In addition to the proposed options for queries of varying degrees of creepiness, you can use denormalization (entering data into an auxiliary top table on triggers or by cron).

Answer 6

this will output all but with sorting

SELECT m.map_id, m.user_id, m.score AS map_score, p.level
  FROM MapsScores AS m, PlayersData AS p
 WHERE m.user_id = p.id 
 GROUP BY m.map_id, m.user_id
 ORDER BY m.map_id ASC, m.score DESC

and here's how to cut off at the time of sampling ...

Answer 7

The answer is not related to the question itself. But the problem itself would be solved well. You can cache the query result and update it in the background using cron once a minute. As a result, the frontend only works with the cache, and does not query the database at all.

Answer 8

In general, did as prompted.
1) I get the id of all cards.
2) For each card, I build a list with the query that is given in the question.
3) I cache the received data.
Caching had to be done in a perverse way. the server works for me on a virtual hosting and Memcached is not available.
The first thing that came to mind was to make a memory table and store the cached data in it, the data will be stored in json format. After tests, it turned out that 65536 bytes are not enough to store all (text) data. As a result, I had to make a regular table and store it with the blob data type .
For those interested, here is the code:

function CacheGet($key)
{
  $query =  "SELECT ";
  $query .= "  data ";
  $query .= "FROM ";
  $query .= "  CacheTable ";
  $query .= "WHERE ";
  $query .= "  name = \"".$key."\" ";
  $query .= "  AND (expire > ".time()." OR locked = 1)";

  $result = mysql_query($query);

  if ($result == false)
  {
    return false;
  }
  else
  {
    $data = mysql_fetch_array($result, MYSQL_ASSOC);

    return $data[data];
  }
}

// lock cache while one process write new updated data
function CacheLock($key)
{
  $query =  "INSERT INTO CacheTable (name, locked) ";
  $query .= "  VALUES(\"".$key."\", 1) ";
  $query .= "ON DUPLICATE KEY UPDATE ";
  $query .= "  name = VALUES(name),";
  $query .= "  locked = VALUES(locked)";

  mysql_query($query);
}

function CacheSet($key, $data, $expire)
{
  $query =  "INSERT INTO CacheTable (name, data, expire, locked) ";
  $query .= "  VALUES(\"".$key."\", \"".mysql_real_escape_string($data)."\", ".(time() + $expire).", 0) ";
  $query .= "ON DUPLICATE KEY UPDATE ";
  $query .= "  name = VALUES(name), ";
  $query .= "  data = VALUES(data), ";
  $query .= "  expire = VALUES(expire), ";
  $query .= "  locked = VALUES(locked)";

  mysql_query($query);
}

Now, when getting the top, if the CacheGet function returns false , then we block the data by this key so that other users also do not start writing new data to the cache. And then, after writing the updated data, we unlock the data using this key.