mysql - SQL 查询 - 内存过载

Question

我有这个非常简单的 SQL 查询

SELECT v.*, u.user_company as company_name, o.`name` as object_name,
o.address as object_address, i.id as installation_id, i.`name` 
as installation_name FROM maintenance as v, users as u,t_local as o,
t_local_objects as i WHERE o.active=1 AND v.done IS NOT NULL

最大的表 - maintenance，它有大约 3k 条记录，其他表大约有 300 条，实际上并没有那么多。当我执行它时，内存过载(试图分配超过 130MB)。如何优化查询？

随着时间的推移，这些表会变大，因此在维护中它必须对超过 10k 条记录有效。

Answer 1

您可能正在寻找 JOINS。顺便说一句，表中的 3K 行并不多，而是太少了。加入表格，您将很快得到结果。

像这样的东西:

SELECT v.*, u.user_company as company_name, o.`name` as object_name,
o.address as object_address, i.id as installation_id, i.`name` 
as installation_name 
FROM maintenance as v inner join users as u on v.id = u.id
.....
WHERE o.active=1 AND v.done IS NOT NULL