CODEWITHSUNDEEP
mysqldatabasedatabase-designblobmysql5
anon
anon

Reputation:

Optimizing MySQL files in database (blob)

due to internal reasons (framework structure) I save many images in a table with a mediumBLOB.

Considering the query to retrive these images are sent with a pretty low rate is there a way to tell mysql to keep off this table from memory? I don't want to have a table of 2GBs in memory used only once in a while.

Are there any way to optimize this?

(Note: if this helps I can move this table in a new database containing only this table)

Thanks

Upvotes: 1

Views: 1950

Answers (2)

araqnid
araqnid

Reputation: 133412

Moving that sort of table into a separate database sounds like a perfectly valid approach to me. At work we have one database server for our operational content (orders, catalogue etc) and one for logs (web logs and copies of emails) and media (images, videos and other binaries). Even running separate instances on the same machine can be worthwhile since, as you mentioned, it partitions the buffer cache (or whatever your storage engine's equivalent is).

Upvotes: 0

karatedog
karatedog

Reputation: 2616

MySQL won't generate in-memory table for BLOB types, as the storage engine doesn't support it.

http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html

"Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

  • Presence of a BLOB or TEXT column in the table"

Which means you should put the BLOB into a different table, and leave other useful data in a BLOBless table so that table will be optimized.

Upvotes: 3

Related Questions