Query Serialized Data

Question

I 1000+ have rows of SQL data that look like the followinng

umeta_id | user_id | meta_key | meta_value
433369   | 44      | all_contests | a:1:{s:12:"all_contests";a:2:{i:5011;a:1:{s:7:"entries";i:3;}i:8722;a:1:{s:7:"entries";i:3;}}}
433368   | 63      | all_contests | a:1:{s:12:"all_contests";a:2:{i:5032;a:1:{s:7:"entries";i:3;}i:8724;a:1:{s:7:"entries";i:3;}}}

When you unserialize one of those values you get an array like the following:

Array
(
    [all_contests] => Array
        (
            [5011] => Array
             (
              [entries] => 3
             )

            [8722] => Array
             (
              [entries] => 3
             )

      )

)

I am trying to create a leaderboard out of all the users for a given contest id. The "all_contests" key holds an array keyed by the ids of all the contests the user signs up for. Inside that is the entries for the given contest.

The query needs to look at all the rows containing 'all_contests' keys and find the 10 highest entries values for a given contest id.

I'm not even sure that it is possible to reliably search inside of a piece of serialized data the way that I'm looking to.

Oh boy. This demonstrates perfectly how storing data as serialized arrays makes USING that data next to impossible from a SQL Query — RiggsFolly, Aug 15 '16 at 23:55
You will have to select the rows you might be interested in and then `unserialize()` that data in PHP and then work out what you want to do with each row an probably build another array of data that you can then process to get that information you require — RiggsFolly, Aug 15 '16 at 23:58

score 1 · Accepted Answer · edited May 23 '17 at 10:28

No, it's impractical to search inside a piece of serialized data.

You can do it with enough meticulous usage of MySQL String Functions like INSTR(), SUBSTR(), FIELD(), and so on. But writing queries like that is time-consuming to develop the query, and it won't have good performance.

What you're doing is a variation on a common mistake: storing a comma-separated list in a column of one table.

This is avoiding creating the intersection table to represent a many-to-many relationship. In other words, you have two tables for users and contests, and you need a third table, in which each row represent's one user's participation in one contest.

See my answer to Is storing a delimited list in a database column really that bad? The answer is about comma-separated lists, but it applies equally to serialized arrays like you're doing.

Thank you for your input, I am going to adjust my model. – Nicholas Koskowski Aug 17 '16 at 23:23 — Nicholas Koskowski, Aug 17 '16 at 23:23

Query Serialized Data

1 Answers1