mysql - 如何减少查询执行时间 MySql

Question

这是我的 Mysql 数据库 View 。

 Create View viewschools As Select
  school.*,
  conference.conference_name,
  conference.division_id,
  division.division_level,
  facility.facility_address,
  GROUP_CONCAT(DISTINCT facility.facility_name) AS Athletic_Venue
FROM school
  INNER JOIN conference
    ON school.conference_id = conference.conference_id
  INNER JOIN division
    ON conference.division_id = division.division_id
  LEFT OUTER JOIN schoolfacilitysport
    ON school.school_id = schoolfacilitysport.school_id
  LEFT OUTER JOIN facilitytosports
    ON schoolfacilitysport.facilitytosports_id = facilitytosports.facilitytosports_id
  LEFT OUTER JOIN facility
    ON facilitytosports.facility_id = facility.facility_id
    group by school.school_id

在数据库表中有 2 万条记录，现在查询执行时间为 13 秒，然后我在数据库中打开此 View 。使用 like 运算符搜索任何学校名称时，查询执行时间会增加。请提出任何减少查询执行时间的建议。

Answer 1

首先在 conference_id、division_id、school_id、facilitytosports_id、facility_id 上创建索引(如果您还没有这样做的话)。
参见 create index