List:Brisbane MySQL Users Group« Previous MessageNext Message »
From:Morgan Tocker Date:July 19 2007 9:10am
Subject:Re: Technical debate
View as plain text  
Hi Tony,

> Myisam vs InnoDB indexes
>
> Which is quicker numeric, alpha or alphanumeric?

What sort of index - primary key, unique, etc?

If you're talking about just index search, MyISAM indexes are packed
by default which can make them small and fast (but keeping in mind
that it won't cache data, InnoDB will).

As to which is quicker - smaller is quicker, so it depends on your
datatype used.  InnoDB stores data in a clustered index (in primary
key order), so if your definition of "fast" includes write speed, an
alphanumeric (like an md5) will be slower than an auto_increment.

> Same question (InnoDB only) but with hashed indexes?

As in adaptive hash?
http://dev.mysql.com/doc/refman/5.0/en/innodb-adaptive-hash.html

I think Peter Zaitcev <http://www.mysqlperformanceblog.com/> did some
tests which were all in memory datasets when the first Falcon alpha
came out.  It should also show both MyISAM+InnoDB.

I would expect that they might even beat each other in some tests, so
it's likely to be workload dependent.

- Morgan (in Belgium this week)
Thread
Technical debateTony Adermann19 Jul
  • Re: Technical debateMorgan Tocker19 Jul