MySQL Performance Example

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.

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.

On Thu, 2008-05-08 at 15:25 +0100, Robbie Bow wrote:

> PS apologies to the admins for double-posting with an
amendment. First
> message went from a unregistered address of mine.

Though there are some differences of approach between
various members of
the evil uber-geek cabal that look after these things I
think for the
most part the moderation of this list resolves to
"check the box labeled
'discard all deferred messages', make a quick scan of the
deferred
messages to see if there is anything amusing, press the
submit button".
The list address has been around for quite a while now and
consequently
attracts a lot of spam so doing anything else would be a
pain in the
arse.

If however you regularly face the risk of posting from an
unregistered
address (like you use a client that allows you to use
multiple
addresses) you can sign up with multiple addresses and tick
the box on
all but one of them for "no mail" so you can post
from any of them.

/J
-- 
My guitar kills bloggers

On Sat, May 10, 2008 at 03:05:03PM +0100, Iain Barnett
wrote:
> >On 10 May 2008, at 14:27, David Cantrell wrote:
> >>An ENUM is self-documenting.
> There are 2 things needed for any piece of
documentation, the What,  
> and the Why.

That depends entirely on what the user is doing.  Quite
often when I'm
debuggering someone elses code all I care about is What.  I
don't care
Why they did something, because I already know that What
they've done is
wrong.  Conversely, if I'm trying to make someone elses code
run faster
or run on a different platform, I care more about Why they
did various
things than about What they did.

> How does an enum self-document if there are (at least)
4 reasons for  
> it's use?
> 
> 1. Because (I believe) this is a fixed list

CMYK is a fixed list.  Now, I agree that you might later
need to add
colours like silver and gold that can't be represented in
CMYK-space,
but at that point you're probably going to stop using CMYK
altogether
and switch to Pantone, which is a rather bigger change to
your model
than merely using a lookup table instead of an ENUM.

> 2. Because (I believe) it's faster than a lookup table

It's faster *for a person*.

> Data dictionary + data model + bit of explanation as to
*why* things  
> are done = documentation.
> Enum ain't documentation any more than a table is
'self' documenting  
> or a field name is self documenting.

Feel free to believe that if you like.  I, however, believe
that using
an ENUM is a valid way of avoiding saying "the string
in field 'name' in
the row with id 2 in table 'list_of_pigments'", much
like calling a
variable 'query' is a more convenient way of saying
"the string which
starts at address 0xBEEFCAFE".

Using an ENUM like that is like using meaningful variable
names.  It
isn't enough documentation for all tasks, but it *is* enough
for some
tasks.

> >And one fewer tables to join on. I'd say go table
first and move to  
> >enum
> >as part of optimising denormalisation. But maybe
that's just me.
> Joins aren't expensive on fixed width fields.

Not for the database.  They are damned annoying for a person
writing an
ad-hoc query though.  And used in the right place, an ENUM
isn't even a
denormalisation (relational database theory pedants are
requested to
stay under their bridges at this point).

-- 
David Cantrell | Nth greatest programmer in the world

       23.5 degrees of axial tilt is the reason for the
season

On May 10, 2008, at 4:08 PM, David Cantrell wrote:

> On Sat, May 10, 2008 at 03:05:03PM +0100, Iain Barnett
wrote:

>> 2. Because (I believe) it's faster than a lookup
table
>
> It's faster *for a person*.

I cannot express how emphatically i agree with this point,
even though  
what i primarily do is not databases.  Nobody likes looking
at numbers  
in trace output or data dumps when they could be seeing the
names to  
which those numbers refer, instead.  Even if you can do the 

translation in your head, it saps mental bandwidth, and
prevents you  
seeing important patterns you might otherwise have
discovered by  
accident.

Do you read like to your ascii text files as hexadecimal
numeric  
values?  Didn't think so.  


--
elysse:  You dance better than some.
me:  "Some" what?
elysse:  Some asparagus.

On 11 May 2008, at 5:08 am, muppet wrote:

>
> On May 10, 2008, at 4:08 PM, David Cantrell wrote:
>
>> On Sat, May 10, 2008 at 03:05:03PM +0100, Iain
Barnett wrote:
>
>>> 2. Because (I believe) it's faster than a
lookup table
>>
>> It's faster *for a person*.
>
> I cannot express how emphatically i agree with this
point, even  
> though what i primarily do is not databases.  Nobody
likes looking  
> at numbers in trace output or data dumps when they
could be seeing  
> the names to which those numbers refer, instead.  Even
if you can  
> do the translation in your head, it saps mental
bandwidth, and  
> prevents you seeing important patterns you might
otherwise have  
> discovered by accident.
>
> Do you read like to your ascii text files as
hexadecimal numeric  
> values?  Didn't think so.  
>


Have you all gone a bit doolally? You don't look at the
numbers, you  
use a join and look at the text. You could still use the
numbers with  
an enum, but you wouldn't, so why would you with a lookup
table?


Iain