MySQL Performance Example Was: Seriously, WTF?

[Jonathan Stowe]

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  
> happens.
> 

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
where clause.


> 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?
> 
> 
> Chris.
> 
> 
> 
> 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.
>