Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a log table that I use for logging files sent to clients. I'm not querying that table, just use it for logging (INSERT) and when I need to check when something was sent to clients, usually once a week, maybe less.

Does having this table with BLOB field affects my DB performance? I'm putting PDF files in that table.

share|improve this question
    
How frequently is this table updated? You understand that writes are more impactful to database performance than reads, yes? –  Andrew Brennan Jul 2 at 9:13
    
every 5-20 min. yes i understand that insert are slow. Another alternative is to write the files to disk, But im not querying the files that often, so i should be good. Is there some table so i can see what is the difference in performance between inserting path to file vs inserting a file in BLOB field. –  Mirko Jul 2 at 11:06
    
Lets say only having that big table in DB, is it impacting the performance on the entire DB when there are no inserts ? –  Mirko Jul 2 at 11:11
    
Is it InnoDB or MyISAM? –  akuzminsky Jul 2 at 13:54
    
Im using InnoDB –  Mirko Jul 2 at 14:49

1 Answer 1

In my experience, a big table should not slow down the whole database. it can slow down the queries on same table though but that too will depend on the structure of table and indexes.

You dont have to store the PDF in database, I'd recommend that you eliminate the BLOB column and make it a CHAR. then store the PDF files on disk and their names into database. Here is how I'd have done this. My table would be:

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name CHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX USING BTREE (file_name)
) ENGINE = INNODB;

and then create a SHA256 sum of PDF file and use it as filename.

Some obvious benefits:

  1. This table is a fixed width table (See this), hence faster in all ways.
  2. file_name column is a SHA256 hash of file which is always 64 characters in length and unique
  3. You have option to save PDF files in any directory on disk and reconstruct the path from this data, for example if you store the files at /home/username/html/storage/log_reports/pdf and categorise them by month or week of creation you have the created_at timestamp which you can use.

so splitting the data by days of month in a given year is

/home/username/html/storage/log_reports/pdf
    |
    |-- 2014
    |    |
    |    |-- May
    |        |
    |        |-- 5th
    |             |-- 4355a46b19d348dc2f57c046f8ef63d4538ebb936000f3c9ee954a27460dd865.pdf
    |             |-- 53c234e5e8472b6ac51c1ae1cab3fe06fad053beb8ebfd8977b010655bfdd3c3.pdf

and so on.

Example case:

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name CHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX USING BTREE (file_name)
) ENGINE = INNODB;

Insert some random data

INSERT INTO 
  logs (file_name, created_at)
VALUES
  ('4355a46b19d348dc2f57c046f8ef63d4538ebb936000f3c9ee954a27460dd865', TIMESTAMP('2014-5-10')),
  ('53c234e5e8472b6ac51c1ae1cab3fe06fad053beb8ebfd8977b010655bfdd3c3', TIMESTAMP('2014-5-9')),
  ('1121cfccd5913f0a63fec40a6ffd44ea64f9dc135c66634ba001d10bcf4302a2', TIMESTAMP('2014-5-5')),
  ('7de1555df0c2700329e815b93b32c571c3ea54dc967b89e81ab73b9972b72d1d', TIMESTAMP('2014-5-1'));

Recreate the path to file:

SELECT CONCAT(DATE_FORMAT(created_at, '%Y/%D/%M/'), file_name, '.pdf') from logs where id = 3;

output:

2014/5th/May/1121cfccd5913f0a63fec40a6ffd44ea64f9dc135c66634ba001d10bcf4302a2.pdf

Then you can just prepend the path to storage directory and send the file for download or attachment.

Here is an example fiddle on this

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.