title: MySQL Shared key over multiple tables
body: <p>I am trying to optimize my query, and I think I need to add a combined key to two <code>date</code> fields that reside within three different tables.</p>
  
  <p>The tables:</p>
  
  <p><code>playerjoins</code> and <code>playerleaves</code>:</p>
  
  <p></p>
  
  <pre><code>+------------+------------+--------+-------+-----------+------------------+
  |  "Field"   |   "Type"   | "Null" | "Key" | "Default" |     "Extra"      |
  +------------+------------+--------+-------+-----------+------------------+
  | "id"       | "int(11)"  | "NO"   | "PRI" | \N        | "auto_increment" |
  | "globalId" | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  | "date"     | "datetime" | "NO"   | "MUL" | \N        | ""               |
  | "serverId" | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  | "playerId" | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  +------------+------------+--------+-------+-----------+------------------+
  </code></pre>
  
  <p><code>levelsloaded</code>:</p>
  
  <pre><code>+--------------+------------+--------+-------+-----------+------------------+
  |   "Field"    |   "Type"   | "Null" | "Key" | "Default" |     "Extra"      |
  +--------------+------------+--------+-------+-----------+------------------+
  | "id"         | "int(11)"  | "NO"   | "PRI" | \N        | "auto_increment" |
  | "globalId"   | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  | "date"       | "datetime" | "NO"   | "MUL" | \N        | ""               |
  | "serverId"   | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  | "gamemodeId" | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  | "mapId"      | "int(11)"  | "NO"   | "MUL" | \N        | ""               |
  +--------------+------------+--------+-------+-----------+------------------+
  </code></pre>
  
  <p>MySQL query:</p>
  
  <pre><code>SELECT llbelow.id, llbelow.globalId, llbelow.date, llbelow.serverId, llbelow.gamemodeId, llbelow.mapId
  FROM (
      SELECT ll.id, ll.globalId, ll.date, ll.serverId, ll.gamemodeId, ll.mapId, pjl.origin, pjl.date AS pjldate 
      FROM 
      (
          (SELECT id, globalId, date, serverId, playerId, 'playerjoins' AS origin
          FROM playerjoins pj WHERE playerId = 2224)
      UNION ALL
          (SELECT id, globalId, date, serverId, playerId, 'playerleaves' AS origin
          FROM playerleaves pl WHERE playerId = 2224)
      )
      pjl
      JOIN levelsloaded ll
      ON pjl.date &lt;= ll.date
  ) llbelow
  LEFT OUTER JOIN (
      SELECT ll.id, ll.globalId, pjl.date AS pjldate  
      FROM 
      (
          (SELECT id, globalId, date, serverId, playerId, 'playerjoins' AS origin
          FROM playerjoins pj WHERE playerId = 2224)
      UNION ALL
          (SELECT id, globalId, date, serverId, playerId, 'playerleaves' AS origin
          FROM playerleaves pl WHERE playerId = 2224)
      )
      pjl
      JOIN levelsloaded ll
      ON pjl.date &lt;= ll.date
  ) llbelow_inner
  ON llbelow.id = llbelow_inner.id AND (llbelow.pjldate &lt; llbelow_inner.pjldate OR (llbelow.pjldate = llbelow_inner.pjldate AND llbelow.globalId &lt; llbelow_inner.globalId))
  WHERE llbelow_inner.id IS NULL AND origin = 'playerjoins'
  ORDER BY llbelow.date DESC, llbelow.pjldate DESC
  </code></pre>
  
  <p>The <code>DESCRIBE</code> of the query:</p>
  
  <pre><code>+------+----------------+--------------+--------+-----------------+------------+-----------+-------+---------+------------+------------------------------------------------+
  | "id" | "select_type"  |   "table"    | "type" | "possible_keys" |   "key"    | "key_len" | "ref" | "rows"  | "filtered" |                    "Extra"                     |
  +------+----------------+--------------+--------+-----------------+------------+-----------+-------+---------+------------+------------------------------------------------+
  | "1"  | "PRIMARY"      | "&lt;derived2&gt;" | "ALL"  | \N              | \N         | \N        | \N    | "74494" | "100,00"   | "Using where; Using temporary; Using filesort" |
  | "1"  | "PRIMARY"      | "&lt;derived5&gt;" | "ALL"  | \N              | \N         | \N        | \N    | "74494" | "100,00"   | "Using where; Not exists"                      |
  | "5"  | "DERIVED"      | "&lt;derived6&gt;" | "ALL"  | \N              | \N         | \N        | \N    | "92"    | "100,00"   | ""                                             |
  | "5"  | "DERIVED"      | "ll"         | "ALL"  | "date"          | \N         | \N        | \N    | "1578"  | "100,00"   | "Using where; Using join buffer"               |
  | "6"  | "DERIVED"      | "pj"         | "ref"  | "playerId"      | "playerId" | "4"       | ""    | "52"    | "100,00"   | ""                                             |
  | "7"  | "UNION"        | "pl"         | "ref"  | "playerId"      | "playerId" | "4"       | ""    | "40"    | "100,00"   | ""                                             |
  | \N   | "UNION RESULT" | "&lt;union6,7&gt;" | "ALL"  | \N              | \N         | \N        | \N    | \N      | \N         | ""                                             |
  | "2"  | "DERIVED"      | "&lt;derived3&gt;" | "ALL"  | \N              | \N         | \N        | \N    | "92"    | "100,00"   | ""                                             |
  | "2"  | "DERIVED"      | "ll"         | "ALL"  | "date"          | \N         | \N        | \N    | "1578"  | "100,00"   | "Using where; Using join buffer"               |
  | "3"  | "DERIVED"      | "pj"         | "ref"  | "playerId"      | "playerId" | "4"       | ""    | "52"    | "100,00"   | ""                                             |
  | "4"  | "UNION"        | "pl"         | "ref"  | "playerId"      | "playerId" | "4"       | ""    | "40"    | "100,00"   | ""                                             |
  | \N   | "UNION RESULT" | "&lt;union3,4&gt;" | "ALL"  | \N              | \N         | \N        | \N    | \N      | \N         | ""                                             |
  +------+----------------+--------------+--------+-----------------+------------+-----------+-------+---------+------------+------------------------------------------------+
  </code></pre>
  
  <p>What would I need to do to optimize the query?</p>
  
  <p><strong>EDIT:</strong> Added a <a href="http://sqlfiddle.com/#!2/eb702/2" rel="nofollow">http://sqlfiddle.com/#!2/eb702/2</a> with example data. What I want is the following, for people interesting in rebuilding the query, informally spoken:</p>
  
  <ul>
  <li>This database is stores information out of log files from a FPS shooter game.</li>
  <li>A player can join or leave the server.</li>
  <li>The server will load a map every 15-30 minutes averagely.</li>
  <li>I want to show, in a personal log about player x, only the maps in which he was actually playing.</li>
  </ul>
  
  <p>More formally said: I want all rows from <code>levelsloaded</code> for which the last action (join/leave) the player did before the level was loaded, was joining the server.<br>
  (Note: This is just one way of formalizing the query, there is no need to stick to it)</p>
  
  <p>Output data I want for the SQLFiddle: <a href="http://sqlfiddle.com/#!2/eb702/1" rel="nofollow">http://sqlfiddle.com/#!2/eb702/1</a></p>
  
  <p>Also, importantly, the query needs to be <strong>fast</strong>. It should be able to be used live, so I am aiming for a response time of under 100ms indepedent of the database size.<br>
  As you can see, I did use a working query to provide the output data I want, however that query runs in 96 seconds on the live database, and even still 2 seconds if I get rid of the union between <code>playerjoins</code> and <code>playerleaves</code>.</p>