MySQL Performance Example Was: Seriously, WTF?
c.jones at ucl.ac.uk
Thu May 8 16:01:21 BST 2008
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
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`;
> 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.
> 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