mysql - 大型数据库的查询优化

Question

您好，我需要帮助来优化超过 100 万条的大型数据库记录的查询。当前查询需要 27-30 秒才能执行。

SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
owner_user.first_name AS ownerFirstName,
owner_user.last_name AS ownerLastName,
CONCAT(owner_user.last_name,
        owner_user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
candidate.email2 AS email2 FROM
candidate
    LEFT JOIN
user AS owner_user ON candidate.owner = owner_user.user_id
    LEFT JOIN
saved_list_entry ON saved_list_entry.data_item_type = 100
    AND saved_list_entry.data_item_id = candidate.candidate_id WHERE
is_active = 1 GROUP BY candidate.candidate_id ORDER BY    dateModifiedSort 
DESC LIMIT 0 , 15

有什么方法可以减少查询的执行时间。我还在表中添加了索引，但它无法正常工作。

Answer 1

我已经更改了下面查询中的表别名，使用这个这一定能解决您的问题

SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
user.first_name AS ownerFirstName,
user.last_name AS ownerLastName,
CONCAT(user.last_name,
        user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
candidate.email2 AS email2 FROM
candidate
    LEFT JOIN
user ON candidate.owner = user.user_id
    LEFT JOIN
saved_list_entry ON saved_list_entry.data_item_type = 100
    AND saved_list_entry.data_item_id = candidate.candidate_id WHERE
is_active = 1 GROUP BY candidate.candidate_id ORDER BY    dateModifiedSort 
DESC LIMIT 0 , 15

使用以下查询为连接条件创建索引

create index index_user user(user_id);

create index index_saved_list_entry saved_list_entry(data_item_type,data_item_id);

create index index_candidate candidate(is_active,candidate_id,dateModifiedSort);