我正在运行此查询来搜索数据库:

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个条目。我已经在refemailfirstnamelastname列中添加了索引,但这似乎没有任何区别。有人可以帮忙吗?

最佳答案

我建议您将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/

10-14 15:18
查看更多