mysql - Most appropriate data structure for an ordered list in an RDBMS? -

I am storing an indexed list of hundreds of millions of items in a MySQL database, usually by adding items to the list Should be known or removed; Frequently, an item should be set to be located in the list. I would say that the ratio of reading / writing is approximately 50:50.

Starting with the linked-list model, I read [1] and the various models discussed there. For a strict link-list, the adjacent list model will work well, but since the reading / writing ratio is more or less equal, I went to get a division and win using standard narrow lists;

The estimated list length (~ 10000) in the entire list 'bucket', maintain an index of bucket size and their relative position within the main list. Each item is assigned to a specific bucket and its position is detected within that bucket.

With this approach, the position of an item is determined by adding the sizes of the balls which move the item's bucket forward in the list, after which the item's position is added to your bucket, from the list To insert / remove the object, the item's 'transfer' result is localized in the bucket in which the item is being added or removed; The size of that bucket should also be updated accordingly.

There is some denormalization in this approach (bucket shape), and the underlying thread is not secure, even with the transaction, because the bucket position of items to modify the table of objects during extract / insert Should be asked to determine, and after that the item's bucket is updated to 'shift' all the other items. Unless these actions have to be atom (stored procedure) threads consistently dielock.

Is there an additional approval approach to keep such data in RDBMS? The thread-security issue is creating a big headache for me and it seems that in order to solve this problem I should have a better way to force the use of stored procedures.

Many thanks, Matt.

[1]

If you need a link list (no hierarchy) , Then you just use the approach described in this article in this blog:

, with this simple query:

< (SELECT @r: = 0) vars, t_list from SEID @ SELECT @ RAs_Pearnt, @ R: = (Select ID from t_list where parent = _parent)

Make sure your ID and original have UNIQUE efficiently defined index for them.

.

To know the status of items, simply reverse the query:

  SELECT COUNT (*) from (SELECT @r AD _id, @r: = (to choose from) Parents t_list WHERE id = _id) AS FROM (SELECT @r: = @item_id) vars, t_list) Q