php - MYSQL JOIN 查询需要很长时间才能执行
问题描述
我对 MYSQL JOIN 查询有疑问。
为了获取数据列表,我总共有 5 个表需要加入,这里是表:(daily_activity_control_card-man、personal_master_data、daily_activity_control_card、machine_master_data 和 work_sheet_machine)。
以下是我通过此查询获得的总行数 我SELECT COUNT(*) FROM table
不认为每个表中当前的总行数会减慢查询过程。我有,
daily_activity_control_card-man (52818 total rows),
personnel_master_data (203 total rows),
daily_activity_control_card (8115 total rows),
machine_master_data (204 total rows) and
work_sheet_machine (19959 total rows).
下面是 EXPLAIN 的结果:
EXPLAIN `daily_activity_control_card-man`;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| wo_number | varchar(50) | YES | MUL | NULL | |
| sheet_no | bigint(20) | YES | MUL | NULL | |
| step | bigint(20) | YES | MUL | NULL | |
| reg_no | varchar(12) | YES | | NULL | |
| start_time | datetime | YES | | NULL | |
| finish_time | datetime | YES | | NULL | |
| status_shift | varchar(14) | YES | | NULL | |
| status_working | varchar(16) | YES | | NULL | |
| status_man | varchar(12) | YES | | NULL | |
| machine_code | varchar(12) | YES | | NULL | |
| act_hours | float | NO | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
EXPLAIN `personnel_master_data`;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| reg_no | varchar(25) | NO | PRI | NULL | |
| personnel_name | varchar(50) | YES | | NULL | |
| password | varchar(100) | YES | UNI | NULL | |
| web_token | longtext | NO | | NULL | |
| permission | int(11) | YES | | NULL | |
| status | varchar(35) | NO | | NULL | |
| online_status | varchar(20) | NO | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
EXPLAIN `daily_activity_control_card`;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| wo_number | varchar(50) | YES | MUL | NULL | |
| sheet_no | bigint(20) | YES | MUL | NULL | |
| step | bigint(20) | YES | MUL | NULL | |
| machine_code | varchar(12) | YES | MUL | NULL | |
| start_time | datetime | YES | | NULL | |
| finish_time | datetime | YES | | NULL | |
| note | longtext | YES | | NULL | |
| act_hours | float | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
EXPLAIN `machine_master_data`;
+-----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+-------+
| code | varchar(12) | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| specs | varchar(50) | NO | | NULL | |
| manufucture | varchar(50) | NO | | NULL | |
| year_manufucture | int(6) | NO | | NULL | |
| purchase_price_dollar | bigint(20) | NO | | NULL | |
| purchase_price_rupiah | bigint(20) | NO | | NULL | |
| hour_day | int(12) | NO | | NULL | |
| rate_hour | int(12) | NO | | NULL | |
| machine_group | varchar(25) | NO | | NULL | |
| status | varchar(12) | NO | | NULL | |
| location | varchar(25) | NO | | NULL | |
+-----------------------+-------------+------+-----+---------+-------+
EXPLAIN `work_sheet_machine`;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| date_act | date | YES | | NULL | |
| wo_number | varchar(50) | YES | | NULL | |
| sheet | float | YES | | NULL | |
| reg_no | varchar(20) | YES | | NULL | |
| serial | float | YES | | NULL | |
| machine_code | varchar(20) | YES | | NULL | |
| description | varchar(500) | YES | | NULL | |
| crew_est | bigint(20) | YES | | NULL | |
| crew_act | bigint(20) | YES | | NULL | |
| jam_est | varchar(20) | YES | | NULL | |
| jam_act | varchar(20) | YES | | NULL | |
| note | varchar(100) | YES | | NULL | |
| manhour_est | float | YES | | NULL | |
| manhour_act | float | YES | | NULL | |
| machine_rate | bigint(50) | YES | | NULL | |
| totalhour_est | bigint(50) | YES | | NULL | |
| totalhour_act | bigint(50) | YES | | NULL | |
| status | varchar(12) | NO | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
以下是我从表中获取数据的连接查询。
SELECT a.id
, a.wo_number
, a.sheet_no
, a.step
, a.reg_no
, DATE_FORMAT(a.start_time, '%Y-%m-%d %H:%i') start_time
, DATE_FORMAT(a.finish_time, '%Y-%m-%d %H:%i') finish_time
, CASE WHEN a.status_shift = 'shift_shift' THEN 'SF'
WHEN a.status_shift = 'shift_nonshift' THEN 'NS'
WHEN a.status_shift = 'long_shift' THEN 'LS' END status_shift
, CASE WHEN a.status_working = 'normal' THEN 'NN'
WHEN a.status_working = 'overtime' THEN 'OT' END status_working
, ROUND(a.act_hours, 1) act_hours
, b.personnel_name
, a.machine_code
, c.note
, e.manhour_est
, CASE WHEN a.status_man = 'CONTINUE' THEN 'C'
WHEN a.status_man = 'FINISH' THEN 'F' END status
, d.name
, d.specs
FROM `daily_activity_control_card-man` a
LEFT
JOIN personnel_master_data b
ON a.reg_no = b.reg_no
LEFT
JOIN daily_activity_control_card c
on a.wo_number = c.wo_number
AND a.sheet_no = c.sheet_no
AND a.machine_code = c.machine_code
LEFT
JOIN machine_master_data d
on a.machine_code = d.code
LEFT
JOIN work_sheet_machine e
on a.wo_number = e.wo_number
AND a.sheet_no = e.sheet
AND a.step = e.serial
WHERE a.status_man IN ('CONTINUE', 'FINISH')
GROUP
BY a.id HAVING COUNT(*) >= 1
有什么办法可以更快地执行查询吗?
我尝试将此条件添加到查询中WHERE DATE_FORMAT(a.start_time, '%Y-%m-%d') between '2018-07-09' AND '2018-08-09'
,但根据 phpmyadmin 信息需要 45 秒才能获得 2500 多行。
任何帮助都感激不尽。谢谢你。
解决方案
WHERE DATE_FORMAT(a.start_time, '%Y-%m-%d') between '2018-07-09' AND '2018-08-09'
不能使用索引,但这可以...
WHERE a.start_time BETWEEN '2018-07-09 00:00:00' AND '2018-08-09 23:59:59'
推荐阅读
- c# - 硬编码还是抽象视图?
- sql - 在传输中编辑 SQL 请求
- webdeploy - 使用 Web 部署部署 ASP.NET MVC5 应用程序时定位 CLI 错误日志失败
- c++ - C++ 升压日志格式器
- jmeter - 将父事务控制器的名称添加到 jmeter 结果的每一行中
- esper - com.espertech.esper.schedule.ScheduleSlot 在 5.5.0 版本中
- python - PySpark Pipeline.fit(df) 方法给出 PicklingError: Could not serialize object: ValueError: substring not found while using Elephas
- excel - 如果单元格更改,清除内容离开公式
- sql - 有没有办法在没有联合的情况下组合列?
- bash - 如何使用“sed”shell命令来混淆某些字符指示的信息