性能Mysql使用varchar加入或表

If I have a table like this:

如果我有这样的表:

CREATE TABLE IF NOT EXISTS `codetransactions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_customer` int(11) DEFAULT NULL,
`id_cashier` int(11) DEFAULT NULL,
`id_code` int(11) DEFAULT NULL,
`program_type` int(11) DEFAULT NULL,
`id_codeaccount` int(11) DEFAULT NULL,
`tmstmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`value1` int(11) DEFAULT NULL,
`value2` int(11) DEFAULT NULL,
`comment` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I am struggling with the issue that with massive datamining - this varchar field will massively slow down the operations.

我正在努力解决大规模数据挖掘的问题 - 这个varchar字段会大大减慢操作速度。

My real question now is: is it better to split this table into two tables andextracting the "comment" field to another table? not every transaction will have a comment and with datamining the varchar will be irrelevant or is this just an illusion and mysql will take care of that?

我现在真正的问题是:将这个表分成两个表并将“注释”字段提取到另一个表是否更好?不是每个事务都会有一个注释,并且与datamining一起varchar将是无关紧要的,或者这仅仅是一种错觉,而mysql会处理这个问题?

2 个解决方案

#1

As suggested - create an index to another table where you keep the comments. And also you are aware that the (11) you add to the int field doesn't actually do anything other then set the visibility?

如建议的那样 - 为另一个表创建一个索引,您可以在其中保留注释。而且你也知道你添加到int字段的(11)实际上没有做任何其他事情然后设置可见性?

#2

make another table say (table2) having columns -

- id - foreign key from your base table,
- comment - varchar(150)

使另一个表说(table2)有列 - - id - 来自基表的外键, - comment - varchar(150)

so when ever any user comments then that user's id and comment will go to this table2
if you want to get the user comment then you only have to search for the existing key in table2.
so no need to insert empty row..

因此,当任何用户评论时,如果您想获得用户评论,那么该用户的ID和评论将转到此表2,那么您只需要搜索table2中的现有密钥。所以不需要插入空行..

#1