blocks|key|4574992|text|如果表未编制索引，请尝试为表forumid编制索引|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4574993|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Try to index the table forumid if it is not indexed

blocks|key|4483899|text|看一看mysql+explain语句。它为您的查询提供了一个执行计划。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4483900|一旦知道了计划，就可以检查计划中涉及的字段是否有索引。如果没有，请创建它们。|4483901|也许该计划揭示了如何以另一种方式编写查询的详细信息，从而使查询更加优化。|4483902|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/using-explain.html^0|3|D|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

Have a look at the <a href="http://dev.mysql.com/doc/refman/5.5/en/using-explain.html" rel="nofollow">mysql explain</a> statement. It gives you a execution plan of your query. 

Once you know the plan, you can check if you have got a index on the fields involved in the plan. If not, create them. 

Perhaps the plan reveals details about how the query can be written in another way, such that the query will be more optimized.

blocks|key|4575117|text|在joins+/+where上没有索引(used+key+=+NULL+on+explain)，这就是查询速度慢的原因。您应该以这种方式对它们进行索引：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4575118|CREATE+INDEX+thread_forumid_index+ON+thread(forumid);
CREATE+INDEX+userthreadviews_forumid_index+ON+userthreadviews(forumid);|code-block|syntax|javascript|4575119|文档here|offset|length|4575120|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html^0|0|0|2|4|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

To have no indexes on joins / where (used key = NULL on explain), this is the reason why your queries are slow. You should index them in such a way :

<pre><code>CREATE INDEX thread_forumid_index ON thread(forumid);
CREATE INDEX userthreadviews_forumid_index ON userthreadviews(forumid);
</code></pre>

Documentation <a href="http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html" rel="nofollow">here</a>

blocks|key|2480154|text|建议：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2480155|2480156|将条件从WHERE子句移动到JOIN子句|unordered-list-item|2480157|将带有条件的JOIN放在其他JOIN|2480158|之前确保您有正确的索引，并且这些索引正在查询中使用(创建所需的索引...索引太多可能和索引太少一样糟糕)|2480159|2480160|以下是我对查询的建议：|2480161|SELECT+
++++`thread`.`title`+AS+'r_title',
++++`thread`.`threadid`+AS+'r_threadid',
++++`thread`.`forumid`+AS+'r_forumid',
++++`thread`.`lastposter`+AS+'r_lastposter',
++++`thread`.`lastposterid`+AS+'r_lastposterid',
++++`forum`.`title`+AS+'f_title',
++++`thread`.`replycount`+AS+'r_replycount',
++++`thread`.`lastpost`+AS+'r_lastpost',
++++`userthreadviews`.`replycount`+AS+'u_replycount',
++++`userthreadviews`.`id`+AS+'u_id',
++++`thread`.`postusername`+AS+'r_postusername',
++++`thread`.`postuserid`+AS+'r_postuserid'+
FROM
++++`thread`+
++++INNER+JOIN+(`forum`)
++++++++ON+((`thread`.`visible`+=+1)
++++++++AND+(`thread`.`lastpost`+>+$time)
++++++++AND+(`thread`.`forumid`+IN+($ids))
++++++++AND+(`thread`.`forumid`+=+`forum`.`forumid`))
++++LEFT+JOIN+(`userthreadviews`)
++++++++ON+((`thread`.`threadid`+=+`userthreadviews`.`threadid`)
++++++++AND+(`userthreadviews`.`userid`+=+$userid))
ORDER+BY
++++`thread`.`lastpost`+DESC
LIMIT
++++0,+30|code-block|syntax|javascript|2480162|这些是需要索引的很好的候选对象：|2480163|-+`forum`.`forumid`
-+`userthreadviews`.`threadid`
-+`userthreadviews`.`userid`
-+`thread`.`forumid`
-+`thread`.`threadid`
-+`thread`.`visible`
-+`thread`.`lastpost`|2480164|似乎你已经有很多索引了.所以，确保你保留了你真正需要的，去掉了那些无用的。|2480165|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|11|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|13|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|14|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|15|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|16|8|@]|9|@]|A|$P|Q]]|$1|R|3|S|5|6|7|17|8|@]|9|@]|A|$]]|$1|T|3|U|5|O|7|18|8|@]|9|@]|A|$P|Q]]|$1|V|3|W|5|6|7|19|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|Y|$]]

Suggestions:

<ul>
<li>move the conditions from the WHERE clause to the JOIN clause</li>
<li>put the JOIN with the conditions before the other JOIN</li>
<li>make sure you have proper indexes and that they are being used in the query (create the ones you'll need... too much indexes can be as bad as too few)</li>
</ul>

Here is my suggestion for the query:

<pre><code>SELECT 
 `thread`.`title` AS 'r_title',
 `thread`.`threadid` AS 'r_threadid',
 `thread`.`forumid` AS 'r_forumid',
 `thread`.`lastposter` AS 'r_lastposter',
 `thread`.`lastposterid` AS 'r_lastposterid',
 `forum`.`title` AS 'f_title',
 `thread`.`replycount` AS 'r_replycount',
 `thread`.`lastpost` AS 'r_lastpost',
 `userthreadviews`.`replycount` AS 'u_replycount',
 `userthreadviews`.`id` AS 'u_id',
 `thread`.`postusername` AS 'r_postusername',
 `thread`.`postuserid` AS 'r_postuserid' 
FROM
 `thread` 
 INNER JOIN (`forum`)
 ON ((`thread`.`visible` = 1)
 AND (`thread`.`lastpost` &gt; $time)
 AND (`thread`.`forumid` IN ($ids))
 AND (`thread`.`forumid` = `forum`.`forumid`))
 LEFT JOIN (`userthreadviews`)
 ON ((`thread`.`threadid` = `userthreadviews`.`threadid`)
 AND (`userthreadviews`.`userid` = $userid))
ORDER BY
 `thread`.`lastpost` DESC
LIMIT
 0, 30
</code></pre>

These are good candidates to be indexed:

<pre><code>- `forum`.`forumid`
- `userthreadviews`.`threadid`
- `userthreadviews`.`userid`
- `thread`.`forumid`
- `thread`.`threadid`
- `thread`.`visible`
- `thread`.`lastpost`
</code></pre>

It seems you already have lots of indexes... so, make sure you keep the ones you really need and remove the useless ones.

I have a query that works, but it's taking at least 3 seconds to run so I think it can probably be faster. It's used to populate a list of new threads and show how many unread posts there are in each thread. I generate the query string before throwing it into <code>$db-&gt;query_read()</code>. In order to only grab results from valid forums, <code>$ids</code> is string with up to 50 values separated by commas.

The <code>userthreadviews</code> table has existed for 1 week and there are roughly 9,500 rows in it. I'm not sure if I need to set up a cron job to regularly clear out thread views more than a week old, or if I will be fine letting it grow.

Here's the query as it currently stands:

<pre><code>SELECT 
 `thread`.`title` AS 'r_title', 
 `thread`.`threadid` AS 'r_threadid', 
 `thread`.`forumid` AS 'r_forumid', 
 `thread`.`lastposter` AS 'r_lastposter', 
 `thread`.`lastposterid` AS 'r_lastposterid', 
 `forum`.`title` AS 'f_title', 
 `thread`.`replycount` AS 'r_replycount', 
 `thread`.`lastpost` AS 'r_lastpost', 
 `userthreadviews`.`replycount` AS 'u_replycount', 
 `userthreadviews`.`id` AS 'u_id',
 `thread`.`postusername` AS 'r_postusername', 
 `thread`.`postuserid` AS 'r_postuserid' 
FROM 
 `thread` 
INNER JOIN 
 `forum` 
 ON (`thread`.`forumid` = `forum`.`forumid`) 
LEFT JOIN 
 (`userthreadviews`) 
 ON (`thread`.`threadid` = `userthreadviews`.`threadid` 
 AND `userthreadviews`.`userid`=$userid) 
WHERE 
 `thread`.`forumid` IN($ids) 
 AND `thread`.`visible`=1 
 AND `thread`.`lastpost`&gt; time() - 604800
ORDER BY `thread`.`lastpost` DESC LIMIT 0, 30
</code></pre>

An alternate query that joins the <code>post</code> table (to only show threads where user has posted) is actually twice as fast, so I think there's got to be something in here that could be changed to speed it up. Could someone provide some advice?

Edit: Sorry, I had put the EXPLAIN in front of the alternate query. Here is the correct output:
As Requested, here is the output generated by EXPLAIN SELECT:
<img src="https://i.stack.imgur.com/d4WBA.png" alt="EXPLAINS Query">

Making an SQL query more efficient

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个可以工作的查询，但它至少需要3秒钟才能运行，所以我认为它可能会更快。它用于填充新线程的列表，并显示每个线程中有多少未读的帖子。在将查询字符串放入$db->query_read()之前，我生成了查询字符串。为了仅从有效论坛获取结果，$ids是一个字符串，其中最多包含50个用逗号分隔的值。userthreadvie...

问提高SQL查询的效率
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问提高SQL查询的效率EN