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
|
