At 4:07 PM +0000 11/13/01, Carl Troein wrote:
>Carsten H. Pedersen writes:
>
>> I assume that there is a small speed penalty in using TEXT/BLOB
>> fields, as compared to VARCHARs.
>
>I recall seeing some test where TEXT was actually faster, but
>it might have been in some special situation. On the whole,
>I think that being able to specify a length for CHAR is the
>most important difference and the reason I use them for names
>of things (I use TINYTEXT/TEXT for longer descriptions etc.)
I have a little perl script that I use to benchmark my own system
when I am looking to find speed. The speed may be different on your
own system, but here are my results when testing TINYBLOB vs
VARCHARs. All other instances are the same, only the column type
changed. The tests were done on a table with only one column, and
that being whatever was being tested. I don't claim these tests to be
fault proof, just my own results.
Averages are taken from 50,000 rows inserted/selected. No other
access to the database was taking place. Tests were done on a pretty
stock version of MySQL on my desktop Sparc 20.
Table: CREATE TABLE benchmark_test (col1 TINYBLOB)
Insert: INSERT INTO benchmark_test (col1) VALUES ('$c') # $c is a
random 25 digit string
Select: SELECT col FROM benchmark_test
The tinyblob averaged: 0.000414 seconds per row while inserting, via
perl dbi, dbh->do
The tinyblob averaged: 0.000126 seconds per row while selecting, via
perl dbi, fetchrow_array
Table: CREAT TABLE benchmark_test (col1 VARCHAR(255))
Insert: INSERT INTO benchmark_test (col1) VALUES ('$c') # $c is a
random 25 digit string
Select: SELECT col1 FROM benchmark_test
The varchar averaged: 0.000369 seconds per row while inserting, via
perl dbi, dbh->do
The varchar averaged: 0.000124 seconds per row while selecting, via
perl dbi, fetchrow_array
As you can see in the CREATE syntax, no indexes are used, this is
just straight speed comparison testing. The actual speeds will depend
on your hardware and any indexes you use on your columns. But, in my
own personal experience, I have found that the speed -differences- in
the types usually remain constant, while the actual speed itself can
be changed with tweaking various other parts of the setup.
If anyone (for some strange reason) wants a copy of my script, just
email me off-list.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Aaron Williams [EMAIL PROTECTED]
Black Raven.com http://www.backraven.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
