MySQL：InnoDB或MyISAM Engine :::这是更好的很多选择？ [英] MySQL: InnoDB or MyISAM Engine ::: which is better for lot of selects?

问题描述

我有一个巨大的文件（〜26 MB）与大约200列& 30000条记录。我想把它导入到一个数据库（InnoDB Engine）。我不会更新或删除记录。虽然我会查询很多从高度复杂的表中的记录在where子句。我更喜欢哪个表引擎更快的查询响应？它真的会产生很大的区别吗？
PS：所有我的其他表使用InnoDB。

此外，如何避免手动创建一个包含200列的表，并指定每个列的数据类型。大多数列都是浮点数，少数是varchar和date。

解决方案通常的答案是更快，ISAM或innodb 将是ISAM

但是为了最好的性能与一个表，只有很少的更新，你可能想看看 Infobright的列式数据库（集成到mysql中）。

然而，只有30k行，你不会看到innodb，isam和infobright之间的显着差异。

OTOH，你真的应该仔细看看你是否真的需要在一个表中有200列。我怀疑不是这种情况 - 并且该模式在确定性能方面比存储引擎更重要。

I have a huge file (~26 MB) with around 200 columns & 30000 records. I want to import it into a database (InnoDB Engine). I wont't be updating or deleting records ever. ALthough I will be querying a lot of records from the table with high complexity in where clause. Which table engine should i prefer for faster query response? Will it really make a lot of difference? PS: All my other tables use InnoDB.

Also How can I avoid manually creating a table with 200 columns and specifying the datatype for each of them. Most of the columns are float and few are varchar and date.

解决方案

Usually the answer to "which is faster, ISAM or innodb" would be ISAM

But for best performance with a table which has very few updates you might want to have a look at Infobright's columnar db (which is integrated into mysql).

However with only 30k rows you'll not see a significant difference between innodb, isam and infobright.

OTOH, you really should have a long hard look at whether you really need 200 columns in a single table. I suspect that's not the case - and the schema is far more important in determining performance than the storage engine.

这篇关于MySQL：InnoDB或MyISAM Engine :::这是更好的很多选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！