How to store a dynamic List into MySQL column efficiently?

Question

I want to store a list of numbers along with some other fields into MySQL. The number of elements in the list is dynamic (some time it could hold about 60 elements)

Currently I'm storing the list into a column of varchar type and the following operations are done.

e.g. aList = [1234122433,1352435632,2346433334,1234122464]

At storing time, aList is coverted to string as below

aListStr = str(aList)

and at reading time the string is converted back to list as below.

aList = eval(aListStr)

There are about 10 million rows, and since I'm storing as strings, it occupies lot space. What is the most efficient way to do this?

Also what should be the efficient way for storing list of strings instead of numbers?

Answer 1

Since you wish to store integers, an effective way would be to store them in an INT/DECIMAL column.

Create an additional table that will hold these numbers and add an ID column to relate the records to other table(s).

Also what should be the efficient way for storing list of strings instead of numbers?

Beside what I said, you can convert them to HEX code which will be very easy & take less space.

Note that a big VARCHAR may influence badly on the performance. VARCHAR(2) and VARCHAR(50) does matter when actions like sotring are done, since MySQL allocates fixed-size memory slices for them, according to the VARCHAR maximum size. When those slices are too large to store in memory, MySQL will store them on disk.

Answer 2

MySQL also has a SET type, it works like ENUM but can hold multiple items. Of course you'd have to have a limited list, currently MySQL only supports up to 64 different items.

Answer 3

I'd be less worried about storage space and more worried about record retrieveal i.e., indexability/searching.

For example, I imagine performing a LIKE or REGEXP in a WHERE clause to find a single item in the list will be quite bit more expensive than if you normalized each list item into a row in a separate table.

However, if you never need to perform such queries agains these columns, then it just won't matter.

Answer 4

Since you are using relational database you should know that storing non-atomic values in individual fields breaks even the first normal form. More likely than not you should follow Don's advice and keep those values in related table. I can't say that for certain because I don't know your problem domain. It may well be that choosing RDBMS for this data was a bad choice altogether.