我正在运行此查询来搜索数据库:
SELECT
IFNULL(firstname, '') AS firstname,
IFNULL(lastname, '') AS lastname,
IFNULL(age, ' ') AS age,
email,
telephone,
comments,
ref
FROM person
RIGHT JOIN
order ON person.oID = order.ref
WHERE
LOWER(firstname) LIKE LOWER ('%{$search}%') OR
LOWER(lastname) LIKE LOWER ('%{$search}%') OR
LOWER(email) LIKE LOWER ('%{$search}%') OR
LOWER(telephone) LIKE LOWER ('%{$search}%') OR
LOWER(ref) LIKE LOWER ('%{$search}%');
它正在做很多处理,但是如何更快地获得这些结果呢?该页面加载大约需要6-7秒,如果我在PHPMyAdmin中运行查询,则查询需要3-4秒才能运行。它不是一个巨大的数据库,大约3000个条目。我已经在
ref
,email
,firstname
和lastname
列中添加了索引,但这似乎没有任何区别。有人可以帮忙吗? 最佳答案
我建议您将right join
更改为inner join
。无论如何,您要查找的字段看起来都来自person
表,因此where
子句将查询变为内部联接。
SELECT
IFNULL(firstname, '') AS firstname,
IFNULL(lastname, '') AS lastname,
IFNULL(age, ' ') AS age,
email,
telephone,
comments,
ref
FROM person INNER JOIN
order
ON person.oID = order.ref
WHERE
LOWER(firstname) LIKE LOWER ('%{$search}%') OR
LOWER(lastname) LIKE LOWER ('%{$search}%') OR
LOWER(email) LIKE LOWER ('%{$search}%') OR
LOWER(telephone) LIKE LOWER ('%{$search}%') OR
LOWER(ref) LIKE LOWER ('%{$search}%');
其次,在
order(ref)
上创建索引。这将大大减少where
子句的搜索空间。语法为:create index order_ref on `order`(ref);
顺便说一句,
order
是表的坏名称,因为它是SQL保留字。我建议改用orders
。关于mysql - SQL查询运行真的很慢,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23844334/