select - 在MySQL 5.7中优化查询

这是我的系统：

Linux Ubuntu 18.04 LTS
MySQL 5.7

我有一个查询（如下）花了太长时间才能完成。

大约需要9秒钟才能完成。对于用户来说，在等待网页完成加载时，这是不可行的时间。

同样，数据集很大并且还在增长。事件表有250,000行。我倾向于每天增加1200至1800行。

为了帮助优化，我想添加索引等，但是对于如何（以及是否）可以使用派生/联合选择查询来做到这一点感到困惑。

这是我的查询。（任何人都知道如何限制输出的宽度，以便我们不必向右滚动即可看到完整的行？）

mysql> explain select OUTSIDE.ownerUID as Owner, 
  OUTSIDE.propUID as Property, 
  OUTSIDE.camname as 'Camera Name', 
  OUTSIDE.direction as Direction, 
  OUTSIDE.camtimestamp as 'Event Time', 
  convert_tz(now(),'UTC','US/Central') as Now, 
  sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time' 
from events OUTSIDE, 
  (select camname,max(camtimestamp) as maxtimestamp from events group by camname) as INSIDE 
where OUTSIDE.camname = INSIDE.camname 
  AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+----+-------------+------------+------------+-------+----------------------+--------------+---------+---------------------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys        | key          | key_len | ref                 | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+----------------------+--------------+---------+---------------------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                 | NULL         | NULL    | NULL                | 263103 |   100.00 | Using where |
|  1 | PRIMARY     | OUTSIDE    | NULL       | ref   | camtimestamp,camname | camtimestamp | 6       | INSIDE.maxtimestamp |      1 |    99.73 | Using where |
|  2 | DERIVED     | events     | NULL       | index | camname              | camname      | 257     | NULL                | 263103 |   100.00 | NULL        |
+----+-------------+------------+------------+-------+----------------------+--------------+---------+---------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.03 sec)

这是查询的结果：

mysql> select OUTSIDE.ownerUID as Owner, 
  OUTSIDE.propUID as Property, 
  OUTSIDE.camname as 'Camera Name', 
  OUTSIDE.direction as Direction, 
  OUTSIDE.camtimestamp as 'Event Time', 
  convert_tz(now(),'UTC','US/Central') as Now, 
  sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time' 
from events OUTSIDE, 
  (select camname,max(camtimestamp) as maxtimestamp from events group by camname) as INSIDE 
where OUTSIDE.camname = INSIDE.camname 
  AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
| Owner | Property | Camera Name | Direction | Event Time          | Now                 | Elapsed Time |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
|     1 |        1 | wls1        | In        | 2020-01-30 12:27:31 | 2020-01-30 12:29:53 | 00:03:42     |
|     1 |        1 | wls2        | Out       | 2020-01-30 12:25:29 | 2020-01-30 12:29:53 | 00:07:04     |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
2 rows in set (6.49 sec)

================================================== ========谢谢所有回答问题或发表评论的人。我接受了Uueerdo关于复合索引的建议，并得出以下结果：

mysql> create index CamNameCamTime on events (camname,camtimestamp);

mysql> select OUTSIDE.ownerUID as Owner,
           OUTSIDE.propUID as Property,
           OUTSIDE.camname as 'Camera Name',
           OUTSIDE.direction as Direction,
           OUTSIDE.camtimestamp as 'Event Time',
           convert_tz(now(),'UTC','US/Central') as Now,
           sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time' 
      from events OUTSIDE,
         (select camname,max(camtimestamp) as maxtimestamp 
           from events group by camname) as INSIDE
      where OUTSIDE.camname = INSIDE.camname AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
| Owner | Property | Camera Name | Direction | Event Time          | Now                 | Elapsed Time |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
|     1 |        1 | wls1        | In        | 2020-01-30 18:43:19 | 2020-01-30 18:44:33 | 00:01:54     |
|     1 |        1 | wls2        | Out       | 2020-01-30 18:41:51 | 2020-01-30 18:44:33 | 00:04:42     |
+-------+----------+-------------+-----------+---------------------+---------------------+--------------+
2 rows in set (0.00 sec)

mysql> explain select OUTSIDE.ownerUID as Owner,
    ->            OUTSIDE.propUID as Property,
    ->            OUTSIDE.camname as 'Camera Name',
    ->            OUTSIDE.direction as Direction,
    ->            OUTSIDE.camtimestamp as 'Event Time',
    ->            convert_tz(now(),'UTC','US/Central') as Now,
    ->            sec_to_time(convert_tz(now(),'UTC','US/Central') - OUTSIDE.CamTimeStamp) as 'Elapsed Time'
    ->       from events OUTSIDE,
    ->          (select camname,max(camtimestamp) as maxtimestamp
    ->            from events group by camname) as INSIDE
    ->       where OUTSIDE.camname = INSIDE.camname AND OUTSIDE.camtimestamp = INSIDE.maxtimestamp;
+----+-------------+------------+------------+-------+-------------------------------------+----------------+---------+------------------------------------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys                       | key            | key_len | ref                                | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+-------------------------------------+----------------+---------+------------------------------------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                                | NULL           | NULL    | NULL                               |    2 |   100.00 | Using where              |
|  1 | PRIMARY     | OUTSIDE    | NULL       | ref   | camtimestamp,camname,CamNameCamTime | CamNameCamTime | 263     | INSIDE.camname,INSIDE.maxtimestamp |    1 |   100.00 | NULL                     |
|  2 | DERIVED     | events     | NULL       | range | camname,CamNameCamTime              | CamNameCamTime | 257     | NULL                               |    2 |   100.00 | Using index for group-by |
+----+-------------+------------+------------+-------+-------------------------------------+----------------+---------+------------------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

成功！