blocks|key|2115036|text|向表中添加字段使其更大。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2115037|这意味着：|2115038|表扫描将花费更多时间。|unordered-list-item|2115039|较少的记录将被放入页面，从而进入缓存，从而增加缓存丢失的风险。|2115040|但是，使用join来选择这个字段需要更多的时间。|2115041|因此，将这个字段添加到这个表中将使没有选择它的查询运行得更慢，而那些选择它的查询运行得更快。|2115042|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|R|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|S|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|T|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|N|$]]

Adding a field to your table makes it larger.

This means that:

<ul>
<li>Table scans will take more time</li>
<li>Less records will fit into a page and hence into the cache, thus increasing the risk of cache misses</li>
</ul>

Selecting this field with a join, however, would take more time.

So adding this field into this table will make the queries which don't select it run slower, and those which do select it run faster.

blocks|key|917908|text|是的，它会影响演出。至少，根据这篇文章昨天发布的数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|917909|根据它，如果您不想遭受性能问题，最好将它们放在一个单独的表中，并在需要时加入它们。|917910|这是相对部分：|917911|尝试限制表中的列数。一个表中太多的列会使查询的扫描时间比只有几个列的时间长得多。此外，如果您的表中有许多列通常不被使用，那么使用空值字段也会浪费磁盘空间。对于可变大小字段(如text或blob+)也是如此，在这些字段中，表的大小可能比需要的要大得多。在这种情况下，您应该考虑将附加列拆分到不同的表中，在记录的主键上将它们连接在一起。|blockquote|917912|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ibm.com/developerworks/linux/library/os-5waystunelamp/index.html?ca=drs-^0|F|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|X|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|Y|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Z|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Yes, it affect the performance. At least, according to <a href="http://www.ibm.com/developerworks/linux/library/os-5waystunelamp/index.html?ca=drs-" rel="nofollow">this article</a> published yesterday.

According to it, if you don't want to suffer performance issues, it's better to put them in a separate table and JOIN them when needed.

This is the relative section:

<blockquote>
 Try to limit the number of columns in
 a table. Too many columns in a table
 can make the scan time for queries
 much longer than if there are just a
 few columns. In addition, if you have
 a table with many columns that aren't
 typically used, you are also wasting
 disk space with NULL value fields.
 This is also true with variable size
 fields, such as text or blob, where
 the table size can grow much larger
 than needed. In this case, you should
 consider splitting off the additional
 columns into a different table,
 joining them together on the primary
 key of the records
</blockquote>

blocks|key|2114935|text|你应该坐在同一张桌子上。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2114936|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|$]]

You should put in on the same table.

blocks|key|2474292|text|是的，它可能会影响同一表上的其他查询，而且您可能无论如何都应该这样做，因为您可能并不在意。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2474293|根据引擎的不同，blobs要么是内联存储(MyISAM)，一部分是离页(InnoDB)，要么是完全脱离页面(在某些情况下是InnoDB插件)。|2474294|这可能会减少每页的行数，从而增加IO操作的数量以满足某些查询。|2474295|然而，这是非常不可能的，你关心，所以你应该做它无论如何。这个表有多少行?+10%5E9？他们中有多少人对blob有非空值？|2474296|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Yes, it probably will affect other queries on the same table, and you should probably do it anyway, as you probably don't care.

Depending on the engine, blobs are either stored inline (MyISAM), partially off-page (InnoDB) or entirely off-page (InnoDB Plugin, in some cases).

These have the potential to decrease the number of rows per page, and therefore increase the number of IO operations to satisfy some query.

However, it is extremely unlikely that you care, so you should just do it anyway. How many rows does this table have? 10^9 ? How many of them have non-null values for the blob?

blocks|key|2115080|text|它不应该太受欢迎，但是如果您担心性能问题，您应该始终在查询中运行一些基准和运行解说来查看真正的效果。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2115081|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html|1|http://dev.mysql.com/doc/refman/5.5/en/explain.html^0|Y|2|0|13|2|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

It shouldn't be too much of a hit, but if you're worried about performance, you should always run a few <a href="http://dev.mysql.com/tech-resources/articles/pro-mysql-ch6.html" rel="nofollow">benchmarks</a> and run <a href="http://dev.mysql.com/doc/refman/5.5/en/explain.html" rel="nofollow">EXPLAINs</a> on your queries to see the true effect.

blocks|key|2474368|text|你预计会有多少个活动？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2474369|很有可能，如果你没有一卡车的几十万事件，你的表现在任何情况下都会很好。|2474370|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

How many events are you expecting to have?

Chances are that if you don't have a truckload of hundred of thousands events, your performance will be good in any case.

I have the following table structure:

<pre><code>EVENT_ID(INT) EVENT_NAME(VARCHAR) EVENT_DATE(DATETIME) EVENT_OWNER(INT)
</code></pre>

I need to add the field <code>EVENT_COMMENTS</code> which should be a text field or a very big <code>VARCHAR</code>.

I have 2 places where I query this table, one is on a page that lists all the events (in that page I do not need to display the event_comments field).

And another page that loads all the details for a specific events, which I will need to display the event_comments field on.

Should I create an extra table with the event_id and the event_comments for that event? Or should I just add that field on the current table?

In other words, what I'm asking is, if I have a text field in my table, but I don't <code>SELECT</code> it, will it affect the performance of the queries to my table?

MySQL query performance

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我的表结构如下：EVENT_ID(INT)    EVENT_NAME(VARCHAR)    EVENT_DATE(DATETIME)    EVENT_OWNER(INT)我需要添加字段EVENT_COMMENTS，它应该是文本字段或非常大的VARCHAR。我有两个地方可以查询这个表，其中一个位于列出所有事件的页面上(在该页面中，我不需要显示event_comments字段)。另一个页面加载特

问MySQL查询性能
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL查询性能EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL查询性能
EN