创建索引以优化慢查询

Question

有一个查询在250,000行的表上花费的时间太长。我需要加快速度：

create table occurrence (
occurrence_id int(11) primary key auto_increment,
client_id varchar(16) not null,
occurrence_cod varchar(50) not null,
entry_date datetime not null,
zone varchar(8) null default null
)
;

insert into occurrence (client_id, occurrence_cod, entry_date, zone)
values
('1116', 'E401', '2011-03-28 18:44', '004'),
('1116', 'R401', '2011-03-28 17:44', '004'),
('1116', 'E401', '2011-03-28 16:44', '004'),
('1338', 'R401', '2011-03-28 14:32', '001')
;

select client_id, occurrence_cod, entry_date, zone
from occurrence o
where
    occurrence_cod = 'E401'
    and
    entry_date = (
        select max(entry_date)
        from occurrence
        where client_id = o.client_id
    )
;
+-----------+----------------+---------------------+------+
| client_id | occurrence_cod | entry_date          | zone |
+-----------+----------------+---------------------+------+
| 1116      | E401           | 2011-03-28 16:44:00 | 004  |
+-----------+----------------+---------------------+------+
1 row in set (0.00 sec)

表结构来自商业应用程序，不能更改。

优化它的最佳索引是什么？或者更好的查询？

编辑：

它是每个客户端的E401代码的最后一个匹配项，并且仅当最后一个匹配项是该代码时才会出现。

Answer 1

这样的查询的理想索引应该是：

index #1: [client_id] + [entry_date]
index #2: [occurence_cod] + [entry_date]

但是，如果数据具有某些特征，则可以简化这些索引。这将节省文件空间，以及数据更新(插入/删除/更新)的时间。

如果每个client_id很少有一个以上的“发生”记录，那么索引#1只能是client_id。

同样，如果每个occurence_cod很少有一个以上的“发生”记录，那么索引#1只能是occurence_cod。

将索引#2转换为entry_date + occurence_cod可能更有用。这将使您能够对仅在entry_date上的条件使用索引。

致以敬意，

Answer 2

除非您真的想要获取具有最大日期的行，否则当且仅当occurrence_cod匹配时，这应该是可行的：

select client_id, occurrence_cod, entry_date, zone 
from occurrence o 
where occurrence_cod = 'E401'
ORDER BY entry_date DESC
LIMIT 1;

它将返回occurrence_cod='E401‘的最新行

Answer 3

我会重写这个查询：

select client_id, occurrence_cod, max(entry_date), zone
from occurrence 
group by client_id, occurrence_cod, zone;

(假设其他行确实是相同的，并且条目日期是唯一变化的)。