[plug] MySQL and Indexes: testing the speed.

[James Bromberger]

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
-- 
 James Bromberger <james_AT_rcpt.to> www.james.rcpt.to
 Remainder moved to http://www.james.rcpt.to/james/sig.html
 The Australian Linux Technical Conference 2003: http://www.linux.conf.au/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 227 bytes
Desc: not available
URL: <http://lists.plug.org.au/pipermail/plug/attachments/20020718/05d78f9c/attachment.pgp>