php - mysql左连接需要太长时间

我有以下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）的更新、使用left join从另一个表中获取的他的个人资料图片以及所有订阅用户的更新，这样我就可以在他的newsfeed中显示它们。
但是，随着更新越来越多，查询需要更多的时间来加载…现在使用codeigniter的profiler我可以看到查询需要1.3793…
现在我已经创建了大约18k个虚拟帐户，并订阅了我的帐户，反之亦然，这样我就可以测试执行时间…考虑到我在本地主机上，我得到的时间是悲惨的…
我还有一些索引，我想在users表（用户名和uid是唯一的）、updates表（update_id是唯一的，uid是索引）中需要更多的索引。
我想我是做错了什么，才得到这么糟糕的结果…
编辑：
运行解释扩展结果：

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上添加一个索引。