php - 提高 PHP MYSQL 查询性能或服务器问题？

Question

我有这个问题:

SELECT
a.codigo AS code, a.nome AS name, a.especialidade AS expertise, a.telemovel1 AS mobile, a.email AS email, a.codigopostal AS zipcode
FROM table_name_a a
WHERE a.nome != ''
AND a.codigo!='5555555'
AND a.codigo!='705416'


UNION ALL

SELECT
b.cod AS code, b.nome AS name, b.especialidade AS expertise, b.telem1 AS mobile, b.mail AS email, b.cp AS zipcode
FROM table_name_b b
WHERE b.nome != ''
AND b.cod!='5555555'
AND b.cod!='705416'
AND b.cod NOT IN(SELECT assoc.codigo from table_name_a assoc) 

ORDER BY code ASC

表 A: +/-6000 行(并且还在增加)

表 B: +/-2000 行(并且还在增加)

问题:

当一个或两个用户使用服务器对数据库进行多次查询时，此查询运行良好......但是当服务器被使用时让我们说在白天，有 10 个用户进行多次查询并且大约 +/- 1000 个客户端在任何给定时间执行一些查询，查询来自:

显示第 0 - 29 行(总共 7,138，查询耗时 0.0734 秒)

到

显示第 0 - 29 行(总共 7,138，查询耗时 100.0933 秒)

提问:

这是查询性能问题还是服务器配置/性能问题？

已编辑:

---

字段类型:

cod, codigo -> int(11)

nome、telemovel1、telem1、especialidade、codigopostal、cp -> varchar(200)

电子邮件 -> varchar(400)

解释查询:

---

Answer 1

我建议你重写这个:

AND b.cod NOT IN (SELECT assoc.codigo from table_name_a assoc) 

进入这个:

AND NOT EXISTS (SELECT * FROM table_name_a assoc WHERE assoc.codigo = b.cod)

重写的查询应该使用更少的内存，为并发进程留出更多的内存。