Mysql – BLObs – Should I Store In Database Or in Directory Structure

There could be many way possible to store data for such scenario, as with your design attempt it seems that you are looking to have unique Id to identify each product and if that is the case the you are right with having a productstable and make that product_id an auto increment key.

Now the next thing to make sure that what ever the attributes of all these bugs, Animals, Cars, Shirts, Shoes, Computers which are common can be added in products table, attributes related to sales can be a good choice to save in this table.

Now Let say for car you will have different brand, design, Engine(Diesel/Petrol), seats, Color and so on you may need other look up tables to save these attributes similar for all the other product like Shoes, Computers etc.

Do not use ENGINE=MyISAM, that will lead to blobs being costly for any operation on the table. Instead, use InnoDB.

"Vertical partitioning" is generally not needed, because of the way InnoDB effectively does it for you....

With InnoDB, there are 4 ROW_FORMATs; let's try to pick the optimal one. In all cases, some, or all, of each blob is stored away from the row itself. More in a minute.

But first, let's check the queries. Do not use SELECT *; always specify only the necessary columns. This avoids reaching into other blocks to get the blobs.

The ROW_FORMATs are redundant, compact, dynamic, and compressed. For your case, I recommend either DYNAMIC or COMPRESSED. These store none of the blob in the record itself; instead they have a 20-byte pointer to where the blob is stored. Only if you need to touch the blob does it run off to that location.

COMPRESSED is the same as DYNAMIC, except for a complicated block-level compression technique. Most benchmarks I see say you get about 2x compression. This is not as good as you might get by compressing the blobs in the client. For example, most 'text' compresses 3x. But most image formats are already compressed, so compression is a waste.

So, I leave it to you to decide between dynamic and compressed. If in doubt, test with your data and your queries.

You should have innodb_file_per_table=1 when you CREATE the table. You may need BARRACUDA and you do need at least version 5.6; 5.7 would be better.

Two tables, one for raw, one for combined? Maybe. Given the benefits described above, there may not be any benefit in having two table. However, if there is a lot of activity in one column, it might be better to move it to a separate table (plus 1:1 id, etc). (This is especially important for things like "like/unlike" counters.)

Since you are working with bulky things, be careful that you do not have long-running transactions. Such can lead to timeouts, etc. Still, use BEGIN...COMMIT where appropriate.