使用使用 JOIN 的查询获取大表的最后几行

我想获取包含超过 10M 行的表（名为 CONTENT）的最后（或最后几行）行。该查询包含其他 2 个表的连接，而且速度非常慢。这些是表定义和我的查询：

CREATE TABLE `USER` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `GUID` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `value` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `CONTENT` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `guid_id` int(11) unsigned DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `guid` (`guid_id`),
  KEY `user_id` (`user_id`),
  KEY `timestamp` (`timestamp`),

  CONSTRAINT `CONTENT_ibfk_4` FOREIGN KEY (`guid_id`) REFERENCES `GUID` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `CONTENT_ibfk_5` FOREIGN KEY (`user_id`) REFERENCES `USER` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

询问：

SELECT 
`CONTENT`.`id`,
`GUID`.`value` AS `guid_value`, 
`USER`.`value` AS `user_value` 
FROM `CONTENT`, `USER`, `GUID` 
WHERE `CONTENT`.`user_id` = `USER`.`id` 
AND `CONTENT`.`guid_id` = `GUID`.`id` 
ORDER BY `CONTENT`.`timestamp` DESC LIMIT 1
  # even without ORDER BY the query is slow as seen by explain command

这些是复制为 INSERT 的解释命令的结果：

+------+-------------+---------+--------+---------------+---------+---------+-----------------------------+-------+----------------------------------------------+
| id   | select_type | table   | type   | possible_keys | key     | key_len | ref                         | rows  | Extra                                        |
+------+-------------+---------+--------+---------------+---------+---------+-----------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | GUID    | index  | PRIMARY       | value   | 37      | NULL                        | 16329 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | CONTENT | ref    | guid,user_id  | guid    | 5       | MANAGER.GUID.id             | 293   | Using where                                  |
|    1 | SIMPLE      | USER    | eq_ref | PRIMARY       | PRIMARY | 4       | MANAGER.CONTENT.user_id     | 1     |                                              |
+------+-------------+---------+--------+---------------+---------+---------+-----------------------------+-------+----------------------------------------------+

该查询无法使用，因此我将其拆分为 2 个查询。首先，我检索感兴趣的 CONTENT.id，其次，我WHERE CONTENT.id = x在 SELECT 语句中插入一个附加子句。似乎在原始查询中 MariaDB 优化器不理解我只需要 1 行，因此它使用 GUID 表中的每一行生成笛卡尔积。是否将查询拆分为 2 个子查询？有人可以确认笛卡尔积运算确实是导致问题的运算吗？（解释命令结果的第一行）

解释 Ricks 查询：

+------+-------------+------------+--------+----------------------+-------------------+---------+-----------------------------+---------+-------------+
| id   | select_type | table      | type   | possible_keys        | key               | key_len | ref                         | rows    | Extra       |
+------+-------------+------------+--------+----------------------+-------------------+---------+-----------------------------+---------+-------------+
|    1 | PRIMARY     | <derived2> | ALL    | NULL                 | NULL              | NULL    | NULL                        | 2       |             |
|    1 | PRIMARY     | CONTENT    | eq_ref | PRIMARY,guid,user_id | PRIMARY           | 4       | c.id                        | 1       | Using where |
|    1 | PRIMARY     | USER       | eq_ref | PRIMARY              | PRIMARY           | 4       | MANAGER.CONTENT.user_id     | 1       |             |
|    1 | PRIMARY     | GUID       | eq_ref | PRIMARY              | PRIMARY           | 4       | MANAGER.CONTENT.guid_id     | 1       |             |
|    2 | DERIVED     | CONTENT    | index  | NULL                 | timestamp         | 6       | NULL                        | 9474301 | Using index |
+------+-------------+------------+--------+----------------------+-------------------+---------+-----------------------------+---------+-------------+