php - MySQL select query with join 和大量的结果优化

Question

我有两张 table 。第一个包含所有项目信息，第二个包含带有类别 ID 的项目 ID。这是因为一个项目可以属于多个类别。我的表中有大约 500 000 件元素。

这是一个选择查询的例子:

SELECT SQL_CALC_FOUND_ROWS items.* 
FROM items 
    INNER JOIN cat
        ON items.iid=cat.iid 
WHERE (items.expire>'1308061323' AND cat.cid = '1') 
AND (items.code=71 OR items.code=23) 
ORDER BY price DESC 
LIMIT 0, 50

• iid = 项目编号
• cid = 类别 ID
• 代码 = 我仅用于搜索的代码。
• expire = 过期时间项

我使用SQL_CALC_FOUND_ROWS 因为我想显示总的匹配结果。我在页面上只显示 50 个项目(LIMIT 0、50)。

当我执行该查询时，我的 php 页面需要大约 5 秒的时间来加载(没有查询时不到 1 秒)。

• 有没有办法优化它？
• 使用 SQL_CALC_FOUND_ROWS 还是使用 SELECT COUNT(*) 的第二个查询更快？
• 我听说过索引，但我不知道如何使用它们以及它们的作用。他们能帮忙吗？

Answer 1

1. 是的，您可以使用索引。
2. 最好使用 2 查询，请参见此处: Is there an effect on the speed of a query when using SQL_CALC_FOUND_ROWS in MySQL?在这里:To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
3. 索引帮助 MySQL 更快地查找数据。在这里，您需要一个关于代码、cid、过期时间和价格的索引。

您可以通过执行以下命令在 items.code 上创建索引:

CREATE INDEX idx_items_code ON items (code);

它应该会提高您的查询即时性。

我的建议是通过阅读 stackoverflow.com 上的一些帖子来了解索引是如何工作的.

这是一个很好的例子:What is an index in SQL Server?

编辑:

If indexes are that good, I can create indexes on every fields. What are the consequences of using an index?

实际上，索引是一颗 Elixir 。它每次都有效。太长的查询，繁荣，做一个索引。这就是为什么当您创建主键时，MySQL 会向该字段添加索引。

另一方面，索引占用服务器空间，其他操作:删除、更新、插入，将花费更多时间。

所以如果你不经常删除、更新或插入，并且选择很多，你几乎可以为每个字段创建一个索引。

最好是充分了解索引的用途，这样才能做出好的选择。