php - VIEW 上的 mysql 查询性能不佳。如何创建索引？

Question

我必须运行 100 个像这样的独立查询

    SELECT SUM(worked_hours) as work_done, selected_date 
    from my_report_workbydate WHERE userid = ? and tid = ? 
    AND selected_date BETWEEN ? AND ? GROUP BY selected_date

看起来像这样

数据是这样产生的

create view my_report_workbydate as SELECT t.project as projectname, t.name as taskname, w.tid, t.username, w.userid, date(w.work_begin) as selected_date, ROUND(SUM(TIMESTAMPDIFF(SECOND, w.work_begin, w.work_end)/3600), 3) as worked_hours 
FROM my_worktime as w
LEFT JOIN my_task as t
ON w.userid = t.userid
AND t.tid = w.tid
GROUP BY w.userid, w.tid, w.work_begin order by selected_date desc;

在具有大型数据库的慢速服务器上，单个查询需要超过 2 秒。我怎样才能加快查询速度？我无法在 View 上的 phpmyadmin 中创建索引，似乎没有选项？

指标:

| my_task | 0 | PRIMARY | 1 | id 
| my_worktime | 0 | PRIMARY | 1 | tid 
| my_worktime | 0 | PRIMARY | 2 | userid 
| my_worktime | 0 | PRIMARY | 3 | work_begin

my_worktime 表解释:

+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| tid        | bigint(20) | NO   | PRI | NULL    |       |
| userid     | bigint(20) | NO   | PRI | NULL    |       |
| work_begin | datetime   | NO   | PRI | NULL    |       |
| work_end   | datetime   | NO   |     | NULL    |       |
+------------+------------+------+-----+---------+-------+

my_task 解释:

+----------------+--------------+------+-----+-------------------+----------------+
| Field          | Type         | Null | Key | Default           | Extra          |
+----------------+--------------+------+-----+-------------------+----------------+
| id             | int(11)      | NO   | PRI | NULL              | auto_increment |
| tid            | varchar(200) | YES  |     | NULL              |                |
| project        | varchar(255) | NO   |     | NULL              |                |
| name           | varchar(255) | NO   |     | NULL              |                |
| username       | varchar(100) | YES  |     | NULL              |                |
| play_date      | varchar(50)  | YES  |     | NULL              |                |
| worked_time    | varchar(100) | YES  |     | NULL              |                |
| estimated_time | varchar(100) | YES  |     | NULL              |                |
| created_on     | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| userid         | bigint(20)   | NO   |     | NULL              |                |
+----------------+--------------+------+-----+-------------------+----------------+

在任务表中对 userid 使用索引后，我可以将速度提高一个巨大的因素。

查询的解释现在看起来像这样，检索时间大致为 0.08 秒。有什么办法可以进一步加快速度吗？

Answer 1

你可以试试这些……

  CREATE INDEX  work_time_user ON work_time  (userid);
  CREATE INDEX  my_task_user ON my_task  (userid);
  CREATE INDEX  work_time_date ON work_time  (date(w.work_begin) );