The fastest way to represent an array in mysql to retrieve

Question

I have an array of php objects that I want to store in the mysql database table. The only way I can think of is to simply have a table for representing the object with a unique identifier and a separate table for storing the array (there may be an array_id and object_id column), but to get it requires a connection, which, in my opinion, can be expensive. Is there a better way? I don't care about the storage time or the insertion time as much as the search time.

I don't need this to work for associative arrays, but if a solution could, it would be preferable.

Answer 1

Building a tree structure (read as an Array) in mysql can be tricky, but it does this all the time. Almost any forum with nested threads has some mechanism for storing a tree structure. Like another poster said they should not be expensive.

The real question is how you want to use the data. If you need to be able to add / remove data fields from individual nodes in the tree, you can use one of two models

1) Model list of attachments

2) Modified pre-order tree traversal algorithm

(They sound scary, but it's not so bad, I promise.)

The first of these is probably the more common one you'll come across, and the second is the one I started using more often and has some useful advantages when you hug it around. Take a look at this page - it has an EXCELLENT record of everyone. http://articles.sitepoint.com/article/hierarchical-data-database

As another poster said, if you don’t need to change the data with queries or search inside the text, use the PHP function to save it in one field.

$array = array('something'=>'fun', 'nothing'=>'to do') $storage_array = serialize($array); //INSERT INTO DB //DRAW OUT OF DB $array = unserialize($row['stored_array']); 

Presto-changeo, it's easy.

Answer 2

If it’s more convenient for you not to perform SQL searches on the data in the array, you can add one column to the table and serialize the array in it. You will need to deserialize it on re-promotion.

You can use JSON / PHP serialization or something more suitable for the language you are developing in.

Answer 3

Connections do not have to be so expensive - you can define an index.