MySQL Performance Example Was: Seriously, WTF?

[Christopher Jones]

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.

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.