问题描述
我有两个桌子.一个是在用户ID上带有主键的User表，另一个是使用外键引用该用户表的表.
I have got two tables. One is a User table with a primary key on the userid and the other table references the user table with a foreign key.
用户"表仅具有一个条目(目前)，而另一个表则具有一百万个条目.
The User table has only one entry (for now) and the other table has one million entrys.
以下联接使我发疯:
SELECT p0_.*, p1_.*
FROM photo p0_, User p1_
WHERE p0_.user_id = p1_.user_id
ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000
在非常快速的机器上，查询的执行时间为12秒，不按顺序进行的查询时间为0.0005秒.
The query takes 12sec on a very fast machine with the order by and 0.0005 sec without the order by.
我在user_id(IDX_14B78418A76ED395)上有一个索引，在user_id和uploaddate上有一个复合索引("search2").
I've got an index on user_id (IDX_14B78418A76ED395) and a composite index ("search2") on user_id and uploaddate.
EXPLAIN显示以下内容:
EXPLAIN shows the following:
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
| 1 | SIMPLE | p1_ | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | p0_ | ref | IDX_14B78418A76ED395,search2 | IDX_14B78418A76ED395 | 4 | odsfoto.p1_.user_id | 58520 | |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
表定义:
CREATE TABLE `photo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`album_id` int(11) DEFAULT NULL,
`exif_id` int(11) DEFAULT NULL,
`title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`width` int(11) NOT NULL,
`height` int(11) NOT NULL,
`uploaddate` datetime NOT NULL,
`filesize` int(11) DEFAULT NULL,
`path` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`originalFilename` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`mimeType` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`description` longtext COLLATE utf8_unicode_ci,
`gpsData_id` int(11) DEFAULT NULL,
`views` int(11) DEFAULT NULL,
`likes` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_14B78418B0FC9251` (`exif_id`),
UNIQUE KEY `UNIQ_14B7841867E96507` (`gpsData_id`),
KEY `IDX_14B78418A76ED395` (`user_id`),
KEY `IDX_14B784181137ABCF` (`album_id`),
KEY `search_idx` (`uploaddate`),
KEY `search2` (`user_id`,`uploaddate`),
KEY `search3` (`uploaddate`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `user` (
`user_id` int(11) NOT NULL,
`photoCount` int(11) NOT NULL,
`photoViews` int(11) NOT NULL,
`photoComments` int(11) NOT NULL,
`photoLikes` int(11) NOT NULL,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
该如何加快查询速度?
推荐答案
您似乎正遭受MySQL无法进行后期行查找的困扰:
Seems you're suffering from MySQL's inability to do late row lookups:
尝试一下:
SELECT p.*, u.*
FROM (
SELECT id
FROM photo
ORDER BY
uploaddate DESC, id DESC
LIMIT 10
OFFSET 100000
) pi
JOIN photo p
ON p.id = pi.id
JOIN user u
ON u.user_id = p.user_id
这篇关于MYSQL，命令很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！