Putting Order By, Limit and Offset in subqueries in Union All

Question

I have got the following table that holds approx. 700K records:

CREATE TABLE `trades` (
 `tradeId` int(11) NOT NULL AUTO_INCREMENT,
 `userId` int(11) NOT NULL,
 `visible` int(11) NOT NULL DEFAULT '1',
 `sourceItem` int(11) NOT NULL,
 `sourceKeyTierId` int(11) DEFAULT NULL,
 `sourceKeyTypeId` int(11) DEFAULT NULL,
 `sourceKeyAmount` int(11) DEFAULT NULL,
 `sourceModId` int(11) DEFAULT NULL,
 `sourceModLevel` int(11) DEFAULT NULL,
 `destinationItem` int(11) NOT NULL,
 `destinationPlatinum` int(11) DEFAULT NULL,
 `destinationKeyTierId` int(11) DEFAULT NULL,
 `destinationKeyTypeId` int(11) DEFAULT NULL,
 `destinationKeyAmount` int(11) DEFAULT NULL,
 `destinationModId` int(11) DEFAULT NULL,
 `destinationModLevel` int(11) DEFAULT NULL,
 `added` datetime NOT NULL,
 PRIMARY KEY (`tradeId`),
 KEY `userId` (`userId`),
 KEY `sourceKeyTierId` (`sourceKeyTierId`),
 KEY `sourceKeyTypeId` (`sourceKeyTypeId`),
 KEY `sourceModId` (`sourceModId`),
 KEY `destinationKeyTierId` (`destinationKeyTierId`),
 KEY `destinationKeyTypeId` (`destinationKeyTypeId`),
 KEY `destinationModId` (`destinationModId`),
 CONSTRAINT `trades_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`userId`),
 CONSTRAINT `trades_ibfk_2` FOREIGN KEY (`sourceKeyTierId`) REFERENCES `keytiers` (`keyTierId`),
 CONSTRAINT `trades_ibfk_3` FOREIGN KEY (`sourceKeyTypeId`) REFERENCES `keytypes` (`keyTypeId`),
 CONSTRAINT `trades_ibfk_4` FOREIGN KEY (`sourceModId`) REFERENCES `mods` (`modId`),
 CONSTRAINT `trades_ibfk_5` FOREIGN KEY (`destinationKeyTierId`) REFERENCES `keytiers` (`keyTierId`),
 CONSTRAINT `trades_ibfk_6` FOREIGN KEY (`destinationKeyTypeId`) REFERENCES `keytypes` (`keyTypeId`),
 CONSTRAINT `trades_ibfk_7` FOREIGN KEY (`destinationModId`) REFERENCES `mods` (`modId`)
) ENGINE=InnoDB AUTO_INCREMENT=732566 DEFAULT CHARSET=latin1

Now I want to query it and get 10 results from it, as seen by this query:

  (SELECT 'Key' AS tradeOrigin,
          CONCAT(skti.tier, ' ', skty.type) AS trade,
          CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
          'Platinum' AS tradeToOrigin,
          t.destinationPlatinum AS tradeTo,
          '' AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 1
   AND t.destinationItem = 1
   AND t.visible = 1
   AND t.userId = u.userId
   JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
   JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId)
UNION ALL
  (SELECT 'Key' AS tradeOrigin,
          CONCAT(skti.tier, ' ', skty.type) AS trade,
          CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
          'Key' AS tradeToOrigin,
          CONCAT(dkti.tier, ' ', dkty.type) AS tradeTo,
          CONCAT('Amount: ', t.destinationKeyAmount) AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 1
   AND t.destinationItem = 2
   AND t.visible = 1
   AND t.userId = u.userId
   JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
   JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId
   JOIN keytiers dkti ON t.destinationKeyTierId = dkti.keyTierId
   JOIN keytypes dkty ON t.destinationKeyTypeId = dkty.keyTypeId)
UNION ALL
  (SELECT 'Key' AS tradeOrigin,
          CONCAT(skti.tier, ' ', skty.type) AS trade,
          CONCAT('Amount: ', t.sourceKeyAmount) AS tradeInfo,
          'Mod' AS tradeToOrigin,
          dm.name AS tradeTo,
          CONCAT('Level: ', IF(t.destinationModLevel = 0, 'Unranked', t.destinationModLevel)) AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 1
   AND t.destinationItem = 3
   AND t.visible = 1
   AND t.userId = u.userId
   JOIN keytiers skti ON t.sourceKeyTierId = skti.keyTierId
   JOIN keytypes skty ON t.sourceKeyTypeId = skty.keyTypeId
   JOIN mods dm ON t.destinationModId = dm.modId)
UNION ALL
  (SELECT 'Mod' AS tradeOrigin,
          sm.name AS trade,
          CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
          'Platinum' AS tradeToOrigin,
          t.destinationPlatinum AS tradeTo,
          '' AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 2
   AND t.destinationItem = 1
   AND t.visible = 1
   AND t.userId = u.userId
   JOIN mods sm ON t.sourceModId = sm.modId)
UNION ALL
  (SELECT 'Mod' AS tradeOrigin,
          sm.name AS trade,
          CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
          'Key' AS tradeToOrigin,
          CONCAT(dkti.tier, ' ', dkty.type) AS tradeTo,
          CONCAT('Amount: ', t.destinationKeyAmount) AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 2
   AND t.destinationItem = 2
   AND t.visible = 1
   AND t.userId = u.userId
   JOIN mods sm ON t.sourceModId = sm.modId
   JOIN keytiers dkti ON t.destinationKeyTierId = dkti.keyTierId
   JOIN keytypes dkty ON t.destinationKeyTypeId = dkty.keyTypeId)
UNION ALL
  (SELECT 'Mod' AS tradeOrigin,
          sm.name AS trade,
          CONCAT('Level: ', IF(t.sourceModLevel = 0, 'Unranked', t.sourceModLevel)) AS tradeInfo,
          'Mod' AS tradeToOrigin,
          dm.name AS tradeTo,
          CONCAT('Level: ', IF(t.destinationModLevel = 0, 'Unranked', t.destinationModLevel)) AS tradeToInfo,
          u.ingame AS seller,
          DAYSPASSED(added) AS daysPassed,
          DATEDIFF(NOW(), added) AS sortingSince
   FROM trades t
   JOIN users u ON t.sourceItem = 2
   AND t.destinationItem = 3
   AND t.visible = 1
   AND t.userId = u.userId
   JOIN mods sm ON t.sourceModId = sm.modId
   JOIN mods dm ON t.destinationModId = dm.modId) LIMIT 0,
                                                        10

This query takes 196 seconds, so obviously I will be needing to put constraints (ORDER BY, LIMIT) in the several subqueries.

I am confused on how to add them though, I select the LIMIT dynamically with the following PHP:

//Limiting
$offset = ($page - 1) * $limit;
$sql .= " LIMIT {$offset}, {$limit}";

As I am using a page-system to show the results that means that I want something that works for:

• Results 0 to 9 from ~700K rows
• Results 10 to 19 from ~700K rows
• etc.
• Results 699990 to 699999 from ~700K rows

I know that if you only want the tail of the result set, let's say you want 20 records. Then every subquery would need to get 20 * [subquery_count] records, to ensure that you never lose a record.
However how will I do it now that offsets also need to be taken into account?