MySQL Performance Example Was: Seriously, WTF?

[Robbie Bow]

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

MyISAM is a good choice for non-transactional tasks.

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

Thanks. It sounds like your existing indexes *might* be corrupted.
After you've built the
location index try this:

REPAIR TABLE `histones` QUICK;

>From the create statement I can see the rows are dynamic format.
Consider making them fixed format by changing the varchars to chars.
If you don't mind the disk space cost, it's the gain of using a fixed
length records over variable length records in a plain text file as
far as seeking goes. Here's an example of what your table could look
like:

CREATE TABLE histones_alt (
	id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	chr CHAR(4) NOT NULL,
	location INT(10) UNSIGNED NOT NULL,
	tally INT(10) UNSIGNED NOT NULL,
	histone CHAR(20) NOT NULL,
	PRIMARY KEY id(id),
	KEY chr_loc (chr,location)
) TYPE=MYISAM;

I've assumed the max lengths for your varchars are necessary but if,
for example, histone is never longer than 7 characters, set it to 7
characters.

Also, do the rows get updated much after insertion (if at all)? If not
then, dependent on your query cache size and other activity on that
server, re-running the queries *should* run in < 1 second as the
result could cached from your previous query.

So, summary of suggestions:

1. Do add that location index
2. Run repair table on the table
3. Change the variable width columns to fixed width
4. Check you've configured a decent size query cache

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

I'm sure normal service will return in due course.