MySQL structuring a query

Question

I've been trying to structure a massive query, and I have succeeded and been able to actually finish the query. However I went from my dev environment (small database) to testing on the live environment (big database), and I've ran into performance problems.

I think the answer can be found here: https://dba.stackexchange.com/a/16376

But is there really no other way around? The reason I am even putting the subqueries in a VIEW is because they have more complex constructs.

Example of the VIEWS / queries:

pjl view:

    (SELECT `pj`.`id` AS `id`,`pj`.`globalId` AS `globalId`,`pj`.`date` AS `date`,`pj`.`serverId` AS `serverId`,`pj`.`playerId` AS `playerId`,'playerjoins' AS `origin`
    FROM `playerjoins` `pj`) 
    UNION ALL 
    (SELECT `pl`.`id` AS `id`,`pl`.`globalId` AS `globalId`,`pl`.`date` AS `date`,`pl`.`serverId` AS `serverId`,`pl`.`playerId` AS `playerId`,'playerleaves' AS `origin`
    FROM `playerleaves` `pl`)

ll_below view:

    SELECT `ll`.`id` AS `id`,`ll`.`globalId` AS `globalId`,`ll`.`date` AS `date`,`ll`.`serverId` AS `serverId`,`ll`.`gamemodeId` AS `gamemodeId`,`ll`.`mapId` AS `mapId`,`pjl`.`origin` AS `origin`,`pjl`.`date` AS `pjldate`,`pjl`.`playerId` AS `playerId`
    FROM `pjl`
    JOIN `levelsloaded` `ll` 
    ON `pjl`.`date` <= `ll`.`date`

the, now simple, query:

    SELECT * FROM
    (
            (SELECT * FROM ll_below WHERE playerId = 976) llbelow
            INNER JOIN
            (SELECT id, MAX(pjldate) AS maxdate FROM ll_below WHERE playerId = 976 GROUP BY id) llbelow_inner
            ON llbelow.id = llbelow_inner.id AND llbelow.pjldate = llbelow_inner.maxdate
    )
    WHERE origin = 'playerjoins'
    ORDER BY date DESC

I could put everything in one big query, but in my eyes it gets a big mess then.

I also know why the performance is being hit so hard, because MySQL cannot use the MERGE algorithm for the pjl view as there is an UNION ALL in it. If I put the WHERE playerId = 976 clauses in the correct places, then the performance hit is gone, but I'd also have a query consisting of 50 lines or something.

Can someone please suggest me what to do if I want performance ánd a query that is still concise?

Answer 1

This clause:

WHERE origin = 'playerjoins'

Means that you didn't need to do a UNION at all, since you're not using any of the rows from pl by the end of the query.

You're right that the view is likely forcing a temporary table instead of using the merge algorithm.

UNION ALL also creates its own temporary table. This case is optimized in MySQL 5.7.3 (still pre-alpha as of this writing), according to Bug #50674 Do not create temporary tables for UNION ALL.

Also, the GROUP BY is probably creating a third level of temporary table.

I see you're also doing a greatest-n-per-group operation, to match the rows with the max date per id. There are different solutions for this type of operation, which don't use a subquery. See my answers for example:

• Retrieving the last record in each group
• Fetch the row which has the Max value for a column

Depending on the number of rows and other conditions, I've seen both solutions for greatest-n-per-group queries give better performance. So you should test both solutions and see which is better given the state and size of your data.

I think you should unravel the views and unions and subqueries. See if you can apply the various WHERE conditions (like playerId=976) directly against the base tables before doing joins and aggregates. That should greatly reduce the number of examined rows, and avoid the multiple layers of temp tables caused by the view and union and group by.

---

Re your comment:

The query you seem to want is the most recent join per level for one specific player.

Something like this:

SELECT ll.id, 
  ll.globalId,
  ll.date AS leveldate,
  ll.serverId,
  ll.gamemodeId,
  ll.mapId,
  pj.date AS joindate,
  pj.playerId
FROM levelsloaded AS ll
INNER JOIN playerjoins AS pj
  ON pj.date <= ll.date
LEFT OUTER JOIN playerjoins AS pj2
  ON pj.playerId = pj2.playerId AND pj2.date <= ll.date AND pj.date < pj2.date 
WHERE pj.playerId = 976
  AND pj2.playerID IS NULL
ORDER BY joindate DESC

(I have not tested this query, but it should get you started.)

Answer 2

Bill is absolutely correct... your views don't even really provide any benefit. I've tried to build something for you, but my interpretation may not be exactly correct. Start by asking yourself IN SIMPLE WORDS what am I trying to get. Here is what I came up with.

I'm looking for a single player (hence your player ID = 976). I'm also only considering the PLAYERJOINS instance (not the player leaving which knocks out the union part). For this player, I want the most recent date they joined a game. From that date as the baseline, I want all Levels Loaded that were created at or after the maximum date joined.

So, the first query is nothing but the maximum date for player 976 from the playerJoined table. Who cares about anything else, or any other user. The ID Here is the same as it would be in the LevelsLoaded table via the join, so getting that player ID and the same levelsLoaded ID for the same person is, IMO, pointless. Then, get the rest of the details from the Levels Loaded on/after the max date for the same person, order by whatever..

If my interpretation of your query is incorrect, offer obvious clarification for adjustments.

SELECT 
      ll.id,
      ll.globalId,
      ll.`date`,
      ll.serverId,
      ll.gamemodeId,
      ll.mapId,
      'playerjoins' as origin,
      playerMax.MaxDate AS pjldate
   FROM 
      ( SELECT MAX( pj.`date` ) as MaxDate
           FROM playerjoins pj 
           where pj.id = 976 ) playerMax
         JOIN levelsloaded ll 
            ON ll.id = 976
           AND playerMax.MaxDate <= ll.`date`