The typical sizes were around 300 kb and 5 mb. Mostly image files and pdf documents. I did not see any problems with efficiency, Of course text searches in documents were not implemented. In one case I stored the PDF OCR text into another table to make searches possible.

One application stores many millions of documents and retrieval is no issue. Since those records are accessed only using the primary key, and only one or a few documents are selected, there is no performance issue regardless the size of the file.

You can also consider horizontal partitioning in MySQL.

>Hi Christian,
>
>That is the exact same way I did my database schema.
>
>I read though that maintaining blob tables are only efficient if the documents fall within the the range of 256kb to 1mb. Otherwise, stick to using the filesystem approach.
>
>What types of documents do you deal with? What are their typical sizes?
>
>
>
>>I did work with files in BLOB using MySQL and never had real problems with it, except that the database size got very large. After reaching 2 TB the performance was still like on the first day.
>>
>>Advantages were that the files are always backed up automatically with the database backup procedures and that access to the documents was automatically restricted by the database security setup.
>>
>>To keep other tables manageable I would create a dedicated table for only documents with a foreign key to the related information, so the other tables you usually work with don't get too large. So the documents table should only have a primary key and the document blob, but everything else should be in another table.
>>
>>
>>>Hi Guys,
>>>
>>>I came across a prospective client who needs to have their documents managed, i.e., stored centrally, accessed quickly via a search facility, viewed or downloaded or even printed.
>>>
>>>The documents are of various types - tax returns, corporate papers, business permits etc - of 100 stores.
>>>
>>>I am aware that this query has been asked before, but in your experiences, which is the way to go - store the documents (scanned as Jpegs or PDFs usually) - in BLOB columns? Or merely let them reside in the computer's File System?
>>>
>>>In my readings, it seems these are two opposing views.
>>>
>>>Your suggestions please!
>>>
>>>Thanks.