情况如下:
我有几个表格(如下所述),可以跟踪公寓楼里的居民、他们的单元号,以及他们最后一次"露面"的时间(我们有很多老年人有健康问题,所以每两天左右检查一次他们很重要;有时他们死在这里,这就是我们知道如何检查他们的方式)。
"检查"的限制是,他们必须在过去48小时内被看到;如果没有,查询应该向上拉取它们的记录。以下是我使用的表格定义:
存储居民信息的"人员"表:
MariaDB [olin2]> describe people;
+-------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| fname | varchar(32) | NO | MUL | NULL | |
| lname | varchar(32) | NO | | NULL | |
| dob | date | YES | | NULL | |
| license_no | varchar(24) | NO | | NULL | |
| date_added | timestamp | NO | | CURRENT_TIMESTAMP | |
| status | varchar(8) | NO | | Allow | |
| license_exp | date | YES | | NULL | |
+-------------+-------------+------+-----+-------------------+----------------+
存储单位编号的"单位"表(人们切换单位,所以我不想把它们放在"人员"表中):
MariaDB [olin2]> describe units;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | varchar(3) | NO | MUL | NULL | |
| resident | int(11) | NO | | NULL | |
| type | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
以及"健康"表,存储"检查"的地方(居民的身份证号码、何时被看到以及被谁看到等):
MariaDB [olin2]> describe wellness;
+--------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+----------------+
| wellness_id | int(11) | NO | PRI | NULL | auto_increment |
| people_id | int(11) | NO | | NULL | |
| time_checked | timestamp | NO | | CURRENT_TIMESTAMP | |
| check_type | varchar(1) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
| return_date | timestamp | YES | | NULL | |
+--------------+-------------+------+-----+-------------------+----------------+
"health"表中的"return_date"字段用于当居民离开超过2天时,当结果显示时,它们不会包含在结果中(它们实际上会包含在查询结果中,但我使用PHP来过滤这些结果)。
这是我一直在使用的查询。。。它在几周内运行良好,但随着记录的增加,速度明显变慢了(现在返回结果需要3.5秒):
select p.id, w.time_checked, w.username, w.return_date
from people p
left join units u on p.id = u.resident
left join wellness w on p.id = w.people_id
left join wellness as w2 on w.people_id = w2.people_id
and w.time_checked < w2.time_checked
where w2.people_id is null
and w.time_checked < (now() - interval 48 hour)
order by u.number
我知道我的问题是连接,但我不知道如何在没有连接的情况下获得所需的结果,和/或如何优化此查询以加快速度。。。以下是结果示例(如果需要):
+----+---------------------+----------+---------------------+
| id | time_checked | username | return_date |
+----+---------------------+----------+---------------------+
| 8 | 2013-12-01 11:00:13 | tluce | 0000-00-00 00:00:00 |
+----+---------------------+----------+---------------------+
1 row in set (3.44 sec)
所以,在这个结果集中,居民8已经三天没有露面了。。。结果是正确的,但是3.44秒对于我的用户来说是不能接受的,必须等待。
有什么想法可以改进吗?
编辑(更多信息):
我意识到为每个人更新健康条目会更容易、更快地访问;然而,我喜欢手头有这些数据,因为我从中生成图表来显示A)我们最经常看到特定居民的时间,以及B)哪些工作人员最经常检查人员(也就是说,谁在做他们的工作,谁不在)
我确实使用了索引,下面是我的查询的EXPLAIN结果:
+------+-------------+-------+--------+---------------+---------+---------+----------------- -+------+--------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+----------------- -+------+--------------------------------------------------------------------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 107 | Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY,idx | PRIMARY | 4 | olin2.u.resident | 1 | Using where |
| 1 | SIMPLE | w | ALL | NULL | NULL | NULL | NULL | 7074 | Using where; Using join buffer (flat, BNL join) |
| 1 | SIMPLE | w2 | ALL | NULL | NULL | NULL | NULL | 7074 | Using where; Not exists; Using join buffer (incremental, BNL join) |
+------+-------------+-------+--------+---------------+---------+---------+----------------- -+------+--------------------------------------------------------------------+
人员表中的索引:id, fname, lname, license_no
健康表:wellness_id
单位表:id, number
在可能的键下,每个具有NULL的字段都表示未使用索引。因此,您可以为用于联接的字段添加索引。例如units.residents。你可以对wellness.people_id–做同样的事情
对于这个结果,您有太多的联接。看起来你有"人员"表的唯一原因是连接到"单位",但你在"单位"answers"健康"中都有可以加入的标志。我也不认为第二次加入健康餐桌是必要的。
这就是你所需要的:
Select W.people_id, w.time_checked, w.username, w.return_date
FROM units u
left join wellness w on u.resident = w.people_id
where w.time_checked < (now() - interval 48 hour)
order by u.number
您可能还需要考虑在表中对people_id字段进行索引,因为它不是主键。这将有助于加快您试图从这些表中运行的任何查询。