我有以下 SQL 查询:

SELECT 
    upd.*,
    usr.username AS `username`,
    usr.profile_picture AS `profile_picture`
FROM 
    updates AS upd
LEFT JOIN 
    subscribers AS sub ON upd.uid=sub.suid
LEFT JOIN 
    users AS usr ON upd.uid=usr.uid
WHERE 
    upd.deleted='0' && (upd.uid='118697835834' || sub.uid='118697835834')
GROUP BY upd.id
ORDER BY upd.date DESC
LIMIT 0, 15

我从哪里获取所有用户 (118697835834) 更新、他使用左连接从另一个表中获取的个人资料图片以及他的所有订阅用户更新，因此我可以在他的新闻源中显示它们。

然而，随着更新越来越多，查询需要更多时间来加载...现在使用 Codeigniter 的 Profiler，我可以看到查询需要 1.3793...

现在我已经创建了大约 18k 个虚拟账户并订阅了我，反之亦然，所以我可以测试执行时间......考虑到我在本地主机上，我得到的时间是悲惨的......

我也有一些索引，我想在用户表中需要更多(用户名和 uid 是唯一的)，更新表(update_id 是唯一的，uid 是索引)

我想我做错了什么导致如此糟糕的结果......

编辑: 运行 EXPLAIN EXTENDED 结果:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => upd
            [type] => ALL
            [possible_keys] => i2
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 22
            [filtered] => 100.00
            [Extra] => Using where; Using temporary; Using filesort
        )

    [1] => stdClass Object
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => sub
            [type] => ALL
            [possible_keys] => 
            [key] => 
            [key_len] => 
            [ref] => 
            [rows] => 18244
            [filtered] => 100.00
            [Extra] => Using where
        )

    [2] => stdClass Object
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => usr
            [type] => eq_ref
            [possible_keys] => uid
            [key] => uid
            [key_len] => 8
            [ref] => site.upd.uid
            [rows] => 1
            [filtered] => 100.00
            [Extra] => 
        )

)

EDIT2:显示表的创建 用户表:

CREATE TABLE `users` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `uid` bigint(20) NOT NULL,
 `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `email` text CHARACTER SET latin1 NOT NULL,
 `password` text CHARACTER SET latin1 NOT NULL,
 `profile_picture_full` text COLLATE utf8_unicode_ci NOT NULL,
 `profile_picture` text COLLATE utf8_unicode_ci NOT NULL,
 `date_registered` datetime NOT NULL,
 `activated` tinyint(1) NOT NULL,
 `closed` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `uid` (`uid`),
 UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=23521 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

订阅者表:

CREATE TABLE `subscribers` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `sid` bigint(20) NOT NULL,
 `uid` bigint(20) NOT NULL,
 `suid` bigint(20) NOT NULL,
 `date` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18255 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

更新表格:

CREATE TABLE `updates` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `update_id` bigint(19) NOT NULL,
 `uid` bigint(20) NOT NULL,
 `type` text COLLATE utf8_unicode_ci NOT NULL,
 `update` text COLLATE utf8_unicode_ci NOT NULL,
 `date` datetime NOT NULL,
 `total_likes` int(11) NOT NULL,
 `total_comments` int(11) NOT NULL,
 `total_favorites` int(11) NOT NULL,
 `category` bigint(20) NOT NULL,
 `deleted` tinyint(1) NOT NULL,
 `deleted_date` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `i1` (`update_id`),
 KEY `i2` (`uid`),
 KEY `deleted_index` (`deleted`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

最佳答案

试试这个(没有 GROUP BY):

SELECT 
    upd.*,
    usr.username AS `username`,
    usr.profile_picture AS `profile_picture`
FROM 
        updates AS upd
    LEFT JOIN 
        users AS usr 
            ON  upd.uid = usr.uid
WHERE 
    upd.deleted='0' 
  AND 
    ( upd.uid='118697835834'
   OR EXISTS
      ( SELECT *
        FROM   subscribers AS sub 
        WHERE  upd.uid = sub.suid
          AND  sub.uid = '118697835834'
      )
    )
ORDER BY upd.date DESC
LIMIT 0, 15

至少应该为联接中使用的列建立索引:updates.uid、users.uid 和 subscribers.suid。

我还会在 subscribers.uid 上添加一个索引。

关于php - mysql left join耗时太长，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/9482499/