Most efficient way to store and access a huge data matrix in MySQL

Question

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.

Answer 1

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.

Answer 2

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.