[plug] MySQL and Indexes: testing the speed.

[Michael Hunt]

James Bromberger [mailto:james at rcpt.to] wrote:

> Hello everyone,
>
> I have been playing with indexes on MySQL, and decided to run
> some tests to
> see how much an index on columns that are in 'where' clauses will help.
>
> Results at http://www.james.rcpt.to/programs/mysql/speed/
>
> The code is in Perl with DBI, so anyone wanting to pull it apart
> can.  Its
> horride code, not cleaned up or documented; I wrote it just to get some
> numbers out. Feel free to modify and do your own tests.
>
> The conclusion is that on ~ 70,000 rows you can get a 4.5 times speed
> improvment doing selects and only suffer a 10% hit during inserts (and
> probably the same during updates) by creating an index on just
> the first 3
> characters of a 'varchar' column, whereas a complete index on the entire
> varchar will only give you a 3.2 times speed increase at a cost of 67%
> slower during inserts (and updates?).
>
> Hence, a blind index on a column isn't always optimal, but better than
> no index.
>
> Sorry if this is obvious for our DBAs out there; I am not a DBA, and felt
> this was intersting.
>
>   James

They are obviously not paying you enough James. You brilliance deserves more
appreciation !!!

Please wait for me to find my sunglasses so as to protect my eyes from the
glare ....

Ah that's better.

Don't you have some web servers to look after James ???

Michael Hunt