mysql - 如何添加涵盖所有 WHERE 要求的索引

Question

我的速度很慢，据我的 friend 说是因为没有索引涵盖我所有的 WHERE 要求。如果我运行 EXPLAIN，您会看到查询正在使用: user_table_id 索引，这很好，但查询还对不属于索引一部分的 project_table_id 和 date_of_activity 进行过滤。所以我需要的是在所有 3 列上创建一个组合索引，但不确定从哪里开始或如何做。如果您还有更好的想法来使此查询更快，请告诉我。

我的查询是:

SELECT time_activity_log.time_activity_log_id, time_activity_log.user_table_id, time_activity_log.project_table_id, time_activity_log.time_activity_id, time_activity_log.time_activity_log_id, time_activity_log.date_of_activity, time_activity_log.no_of_hours, time_activity_log.billable, time_activity_log.notes, time_activity_log.add_as_bugnote, time_activity_log.email_note, time_activity_log.make_note_private, time_activity_log.date_created, time_activity_log.date_updated 
FROM time_activity_log 
WHERE ( time_activity_log.date_of_activity >= 1412136000 AND time_activity_log.date_of_activity <= 1414814399 ) 
AND time_activity_log.project_table_id in (6,13,16,24,26,37,40,45,51,62,64,75,131,132,134,138,152,148,186,183,189,190,191,210,220,227,240,242,245,246,247,253,257,258,261,276,277,278,279,280,281,282,291,288,289,290,294,320,321,322,329,335,349,351,352,379,380,381,382,383,384,385,386,387,388,389,394,403,406,407,415,416,424,433,435,436,444,449,455,456,457,458,469,470,481,482,483,484,485,486,488,489,490,492,499,504,505,506,507,510,512,513,514,516,517,518,519,520,521,522,523,524,525,527,528,529,530,532,533,534,535) 
AND time_activity_log.user_table_id = 688 
ORDER BY time_activity_log.date_of_activity

到目前为止，当我解释时，我得到:

id : 1
select_type : SIMPLE
table : time_activity_log
type : ref
possible keys : user_table_id,project_table_id,idx_date_of_activity
key : user_table_id
key_len : 4
ref : const
rows : 2817
extra : Using where; Using filesort

Answer 1

一次只能使用一个索引。如果您想在所有 3 列上使用索引，则必须创建复合索引:

ALTER TABLE time_activity_log ADD INDEX (project_table_id, user_table_id, date_of_activity)