MySQL Performance Example Was: Seriously, WTF?

[George Barnett]

On Fri, May 9, 2008 01:01, Christopher Jones wrote:

> CREATE TABLE `histones` (
> `read_id` int(11) NOT NULL auto_increment,
> `chr` varchar(4) NOT NULL default '',
> `location` int(10) unsigned NOT NULL default '0',
> `tally` int(10) unsigned NOT NULL default '0',
> `histone` varchar(20) default NULL,
> PRIMARY KEY  (`read_id`),
> KEY `chr` (`chr`),
> KEY `histone` (`histone`)
> ) ENGINE=MyISAM AUTO_INCREMENT=99694252 DEFAULT CHARSET=latin1

For number of rows, you'd probably get a benefit from using the InnoDB
engine, if only to gain row locking instead of table locking.  This does
of course depend on how many queries you run.  MyISAM does have the
benefit of being able to append to a gapless table which makes it faster
for tables that are mostly insert driven with no deletes.

Are you using read_id for anything asides from being a PK?  If there's a
more "natural" PK in the data, you'll find that using that in innodb will
give you a boost due to way data is stored.

Otherwise posts about multi-col keys are sensible.  MySQL can use multiple
keys, but only since recent 5.0 versions.

That's the other thing.  You'll get a massive boost by going to 4.1.  It's
a much faster code base.

That said, you really ought to look at ditching 4.x as it's old.  I'd wait
for 5.1GA though since 5.0 < 5.0.53 is much slower than 4.1  (.53 is still
a bit slower than 4.1), but not much considering new features.

George