Most efficient way to store and access a huge data matrix in MySQL
Since you said you want efficiency in fetching, I would use following table format
Column Row Value 1 1 1.2 2 1 2.3 ...Using the format and indexing on column and row of the matrix, you can fetch any data part as fast as you want.
There are a couple relevant questions here:
- BLOB vs. VARCHAR for storing arrays in a MySQL table
- How to represent a 2-D data matrix in a database
The answers for dense matrices seem to boil down to a normalized table with columns for column, row, and value, as suggested by Taesung above, or doing something like storing individual rows from your original matrix as blobs.
HDF5 looks to be made for this sort of thing. It would be great if someone with experience could comment further.