CODEWITHSUNDEEP
mysqldatabaserelational-databaseinnodbmyisam
Rob
Rob

Reputation: 2362

MyISAM vs InnoDB in a very large table

I have a very simple table

CREATE TABLE IF NOT EXISTS `largecache` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tooltip` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `attributes` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tooltip` (`tooltip`)
) ENGINE=InnoDB

With about 8 million entries that is used almost entirely for reads. I'm trying to make sure I'm accessing the data as quickly as possible. I'm currently using InnoDB, is it worth it to switch to MyISAM? My only concern is performance on reads. Also any other recommends I could use to speed up since the DB reads are pretty much my only bottleneck in my application.

I'm using MySQL client version: 5.1.47

Thanks.

[edit: More details] I'm using php, and I'm querying based on exact matches of the tooltip field. It's being hosted on a shared VPS with 1 gig of ram, any tweaks to mysql that I can make I'll try, but I know mysql tweaking is a pretty complex issue.

Example query:

select * from largecache where `tooltip` = "Cm8IuIyq9QMSBwgEFS0iGWAd30SUNR2EHJ0nHYYCY-odxZ6okR0pEnPgHWzQbvIiCwgBFXZCAwAYCCAiMAk4_gNAAEgPUBBg_gNqJQoMCAAQvKHZ5oCAgIA6EhUIrcC1xggSBwgEFUn1i18wCTgAQAEY2ojJgQxQAlgA"

I do have APC installed, but not memcached

Upvotes: 4

Views: 10032

Answers (3)

kta
kta

Reputation: 20140

Seems like it's more on the MyISAM side. In response of "any other recommends I could use to speed up" you can implement full-text search.

Upvotes: 0

Ray
Ray

Reputation: 41508

With the default innodb in 5.1x, all things equal, you might eek ou a little performance boost from myisam if properly tweaked based on only your limited details.

My suggestIon is go to 5.5 and innodb.

In reality, we need to see some of your queries to understand how you are accessing it. Do youhave lots of count(*) 's?

Also, if you're dealing with million of rows 1G of RAM might a little tight--assuming this is not the only table you have. This is probably your biggest issue regardless of type of table type you choose.

Upvotes: 1

Srikar Appalaraju
Srikar Appalaraju

Reputation: 73688

It would be good to have the exact query you do. But based on what you have posted here goes-

Are you planning to do any full text search (read myISAM)? Since innodb does row level locking as opposed to myISAM which does table level locking by the looks of it innodb suits your requirements. Try the following using innodb engine-

  1. Put an index on tooltip field.
  2. Try to disable transaction management from innodb config. Although this is more for writes than reads.
  3. Try to increase the cache size for innodb. All these DB engines are suckers for memory, its as simple as throwing more memory at them & suddenly they behave well.
  4. Finally, I cannot end this post without mentioning memcached. This is a distributed cache management module & it awesomely integrates with mysql. This can significantly boost your read operations.

Provided links wherever appropriate.

Check this for completeness sake - Why use InnoDB over MySIAM

Upvotes: 7

Related Questions