MySQL Performance Example Was: Seriously, WTF?
jns at integration-house.com
Thu May 8 16:22:21 BST 2008
On Thu, 2008-05-08 at 16:01 +0100, Christopher Jones wrote:
> To reply to the first few questions, there isn't (currently) an index
> on `location`. I did try using an index, but I'm pretty certain it
> made the query slower (?!) so I removed it. But that just doesn't
> seem right - so to make sure I've added the index back again and
> (once MySQL is finished) I'll re-do the query and let you know what
I'm not sure about MySQL but elsewhere I'd be inclined to make a single
index on both 'location' and 'chr' (ie "CREATE INDEX
histones_chr_location ON histones(chr, location)" ) which gives the
optimizer a better hint that it should use that index for that kind of
> This is what's in there at the moment (please don't flame for use of
> MyISAM - there are no foreign keys being used, so I didn't bother
> setting an ENGINE, and it defaulted to MyISAM).
> 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
> I'll post you the EXPLAIN statement once its finished adding the index.
> I have to confess to being in a state of shock at only having
> constructive responses to my post, and (so far at least) none asking
> "why would you want to insert 100 million rows into such a sad excuse
> for a relational database anyway its not a database cause it doesn't
> have any foreign keys and really you'd be better off using PG and
> blah, blah, blah...." Is this what happens when the sun comes out?
> On 8 May 2008, at 15:25, Robbie Bow wrote:
> > Christopher Jones wrote:
> >> With MySQL 4.0.20 running on Apple XServe dual G5 2.5GHz, 2Gb RAM;
> >> mysql> select * from histones where chr='1' and location > 10000 and
> >> location < 20000;
> > Can you gives us the result of running this query:
> > SHOW CREATE TABLE `histones`;
> > and
> > EXPLAIN select * from histones where chr='1' and location > 10000 and
> > location < 20000;
> > Just so we can see the storage type, indexes &c. and how the query is
> > performing its search.
> > Cheers
> > PS apologies to the admins for double-posting with an amendment. First
> > message went from a unregistered address of mine.
More information about the london.pm