blocks|key|598159|text|在查询和分析结果之前使用EXPLAIN。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598160|这里，您可以找到如何开始优化工作。|offset|length|598161|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/using-explain.html^0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Use EXPLAIN in front of your query and analyze results.

<a href="http://dev.mysql.com/doc/refman/5.0/en/using-explain.html" rel="nofollow">Here</a> you can find how to start your optimization work.

blocks|key|2708660|text|我看没几个问题你可以查证。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2708661|尽管使用了InnoDB引擎，但您没有使用关系。|unordered-list-item|2708662|您正在选择没有索引的字段。|2708663|您将同时选择所有行。|2708664|你一次需要所有这些行吗？也许可以考虑将这个查询分割成多个碎片(分页)？|2708665|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$]]|$1|E|3|F|5|D|7|O|8|@]|9|@]|A|$]]|$1|G|3|H|5|D|7|P|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Q|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|L|$]]

I see few issues you could check.

<ul>
<li>You're not using relations despite using InnoDB engine.</li>
<li>You're selecting fields without index.</li>
<li>You're selecting all rows at once.</li>
</ul>

Do you need all those rows at once? Maybe consider splitting this query to multiple shards (paging)?

blocks|key|598187|text|尝试使用带IS条件的派生查询。你解释说有一个依赖的子查询。忽略使用它，并使用嘲笑查询您的问题。这将提高性能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598188|select
++a.article_id,
++a.article_title,
++a.source_id,
++a.article_publish_date,
++a.article_details,
++n.source_name
from+sources+n
++INNER+JOIN+articles+a
++++ON+(a.source_id+=+n.source_id)
++LEFT+JOIN+(SELECT+*
+++++++++FROM+news_sources_deselected
+++++++++WHERE+user_id+=+5)+AS+nsd
++++ON+nsd.source_id+=+n.source_id
WHERE+n.category_id+=+25
++++AND+nsd.source_id+IS+NULL
ORDER+BY+a.article_publish_date+DESC|code-block|syntax|javascript|598189|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try using a derieved query with IS NULL condition. You explain says there is a dependent subquery. Ignore using it and use derieved query for your problem. This will increase the performance

<pre><code>select
 a.article_id,
 a.article_title,
 a.source_id,
 a.article_publish_date,
 a.article_details,
 n.source_name
from sources n
 INNER JOIN articles a
 ON (a.source_id = n.source_id)
 LEFT JOIN (SELECT *
 FROM news_sources_deselected
 WHERE user_id = 5) AS nsd
 ON nsd.source_id = n.source_id
WHERE n.category_id = 25
 AND nsd.source_id IS NULL
ORDER BY a.article_publish_date DESC
</code></pre>

blocks|key|598204|text|尝试这个查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598205|select
+a.article_id,
+a.article_title,
+a.source_id,
+a.article_publish_date,
+a.article_details,
+n.source_name
from+
+sources+n
INNER+JOIN+
+articles+a
ON+
+n.category_id+=+25+AND+
+a.source_id+=+n.source_id+++++
INNER+JOIN+
+news_sources_deselected+nsd
ON
+nsd.user_id+<>+5+AND+n.source_id+=+nsd.source_id
ORDER+BY+
+a.article_publish_date+DESC|code-block|syntax|javascript|598206|我删除了额外的查询，并通过接受除id+news_sources_deselected以外的user_id的所有source_id，在join中添加了5。|offset|length|style|CODE|598207|或者，正如用户raheelshan所提到的，我们可以只使用连接所需的记录。|598208|select
+a.article_id,
+a.article_title,
+a.source_id,
+a.article_publish_date,
+a.article_details,
+n.source_name
from+
+(select+
+++*+
+from+
+++sources+
+where+
+++category_id+=+25)+n+
INNER+JOIN+
+articles+a
ON+
+a.source_id+=+n.source_id+++++
INNER+JOIN+
+(select+
+++*+
+from+
+++news_sources_deselected+
+where+
+++user_id+<>+5)+nsd
ON
+n.source_id+=+nsd.source_id
ORDER+BY+
+a.article_publish_date+DESC|598209|希望这有帮助..。|598210|entityMap^0|0|0|J|N|19|7|1J|9|22|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]|$I|13|J|14|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|17|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|T|$]]

Try this query 

<pre><code>select
 a.article_id,
 a.article_title,
 a.source_id,
 a.article_publish_date,
 a.article_details,
 n.source_name
from 
 sources n
INNER JOIN 
 articles a
ON 
 n.category_id = 25 AND 
 a.source_id = n.source_id 
INNER JOIN 
 news_sources_deselected nsd
ON
 nsd.user_id &lt;&gt; 5 AND n.source_id = nsd.source_id
ORDER BY 
 a.article_publish_date DESC
</code></pre>

I have removed the extra query and added <code>news_sources_deselected</code> in join by accepting all <code>source_id</code> for <code>user_id</code> other than with id <code>5</code>.

Or we can go for using only needed records for join as user raheelshan has mentioned 

<pre><code>select
 a.article_id,
 a.article_title,
 a.source_id,
 a.article_publish_date,
 a.article_details,
 n.source_name
from 
 (select 
 * 
 from 
 sources 
 where 
 category_id = 25) n 
INNER JOIN 
 articles a
ON 
 a.source_id = n.source_id 
INNER JOIN 
 (select 
 * 
 from 
 news_sources_deselected 
 where 
 user_id &lt;&gt; 5) nsd
ON
 n.source_id = nsd.source_id
ORDER BY 
 a.article_publish_date DESC
</code></pre>

Hope this helps..

blocks|key|598233|text|我通过划分表来解决这个问题，但我仍然愿意听取建议。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|598234|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|$]]

I fixed the issue by partitioning the table, but I am still open to suggestions.

Hello I am looking for ways to optimize the mysql query, basically I am fetching the articles for the user which belong to category_id = 25 and source_id not in a table where I store source id's from which user has unsubscribed. 

<pre><code>select
 a.article_id,
 a.article_title,
 a.source_id,
 a.article_publish_date,
 a.article_details,
 n.source_name
from sources n
 INNER JOIN articles a
 ON (a.source_id = n.source_id)
WHERE n.category_id = 25
 AND n.source_id NOT IN(select
 source_id
 from news_sources_deselected
 WHERE user_id = 5)
ORDER BY a.article_publish_date DESC
</code></pre>

Schema for Articles Table

<pre><code>CREATE TABLE IF NOT EXISTS `articles` (&lt;br&gt;
 `article_id` int(255) NOT NULL auto_increment,&lt;br&gt;
 `article_title` varchar(255) NOT NULL,&lt;br&gt;
 `source_id` int(255) NOT NULL,&lt;br&gt;
 `article_publish_date` bigint(255) NOT NULL,&lt;br&gt;
 `article_details` text NOT NULL,&lt;br&gt;
 PRIMARY KEY (`article_id`),&lt;br&gt;
 KEY `source_id` (`source_id`),&lt;br&gt;
 KEY `article_publish_date` (`article_publish_date`)&lt;br&gt;
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contains articles.';
</code></pre>

Structure for Sources table

<pre><code>CREATE TABLE IF NOT EXISTS `sources` (&lt;br&gt;
 `source_id` int(255) NOT NULL auto_increment,&lt;br&gt;
 `category_id` int(255) NOT NULL,&lt;br&gt;
 `source_name` varchar(255) character set latin1 NOT NULL,&lt;br&gt;
 `user_id` int(255) NOT NULL,&lt;br&gt;
 PRIMARY KEY (`source_id`),&lt;br&gt;
 KEY `category_id` (`category_id`),&lt;br&gt;
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='News Sources.'
</code></pre>

The articles table has around 0.3 Million records and sources table contains around 1000 records, the query takes around 180 seconds to execute.

Any help will be greatly appreciated.

<img src="https://i.stack.imgur.com/wfNGm.png" alt="enter image description here">

MySQL Query is Extremely Slow

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

你好，我正在寻找优化mysql查询的方法，基本上，我是为属于category_id = 25和source_id的用户获取文章，而不是存储用户未订阅的源id的表中。select  a.article_id,  a.article_title,  a.source_id,  a.article_publish_date,...

问MySQL查询非常慢
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL查询非常慢EN