Subject Blob vs Varchar - Performance Benchmarks
Author Rajesh Punjabi
Hi All,

I have a roughly 3.5 GB FB 1.5 production database running on a Linux
FC4 server with an AMD 1.8GHz processor and 3GB RAM. This database is
used to store resumes as we are a recruitment company.

Over the last 1 year the database is slowing down considerably. Users
have increased from 4-5 to about 20 concurrent users in this period. The
primary slowdown occurs when users do a brute force search for keywords
in the body of the resume (stored in the blob). All other queries with
indexes run reasonably fast. (sub 10 secs).

Is there any way to directly optimize blob search that anyone here can
point me to ?

I have looked up varchar fields and if I shift the data from blobs to
varchar will performance improve ? Are there any benchmarks for this ?

I can use another field for limiting the number of blobs searched (eg
freshness of resumes) to keep the search times down, however that does
not improve performance immensely. Also as the number of records in the
database are increasing I have to find a long term solution to this
problem? As my number of users goes up to say 100 I am afraid the
performance will really deteriorate.