with - store matrix in mysql
Most efficient way to store and access a huge data matrix in MySQL (2)
I am going to store a huge amount of matrix data in a mysqlDB what is the most efficient way to store and access the data?
The efficiency is most important when getting the data, the table will not be updated regularly.
The matrix is about 100.000 times 1000 (probably larger in the future)
id1 value value_id1 id1 value value_id2 id2 value value_id1 id2 value value_id2 . . . id 100.000 value value_id1000 vs value_id1, value_id2, value_id3 ... id 1000 id1 value value value id2 value value value id3 value value value . . . id 100.000
When the data is huge what is most efficient, a short call (mysql query) or to have the data stored as a matrix? The data is used regularly so it must be efficient to fetch data.
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.