3

I am working on a product database in MySQL and the products can be multiple types with different attributes. The number of attributes and there datatype vary. I read about some design patterns (post) used in this situation, but none of them looks promising. I am thinking about mixing them somehow.

Lets say, I have these product types (D - dimension, but could be attribute or entity...):

Product_type1: D1 - int, D2 - int, D3 - string, D4 - date, D5 - string

Product_type2: D1 - int, D2 - string, D3 - date, D4 - int, D5 - string, D6 - int

Solution 1:

I store a fix number of attributes of every datatype for a product, let's say 3 int field, 2 date, 2 string. The most important attributes stored here (important regarding indexing and searching). The rest of the attributes are stored in a JSON field or an array format.

Solution 2:

Store everything in indexed JSON field and search in the table with 'LIKE' queries.

How fast is a Like query in MySQL when you have a big product database (20 000 - 30 000 products). Is solution 1 better or not?

  • JSON array is just EAV stored in a really awkward format. Even relational EAV is better than a JSON array. – Joel Brown Jul 10 '15 at 10:54
  • @JoelBrown don't we face performance problems if we use eav ? – Baby in Database Sep 22 '16 at 10:09
  • 1
    @user12345 What performance problems would you expect from EAV versus storing a JSON string? Searching for a particular row value in an EAV relational database (which can be indexed) would be faster than a full text table scan parsing each row of JSON. – Joel Brown Sep 22 '16 at 10:48
  • @JoelBrown can you please join here : chat.stackexchange.com/rooms/45740/eav – Baby in Database Sep 22 '16 at 10:53
2

Put common things (price, SKU) in MySQL columns; index them if needed.

Put the rest in a JSON column. Do not try to use LIKE to search that.

Instead, first filter on what you can from the other columns, then use your application language to finish the filtering.

To save on space and bandwidth, compress (in the client) the JSON string and put it into a BLOB column.

Write your user-interface to encourage them to filter on the real columns, only reluctantly let them specify the other things.

See also MariaDB's "Dynamic Columns; it's been available for several releases. In the future, 5.7 promises to have a bunch of JSON tools.

  • Update: 5.7 has more JSON tools; 8.0 has even more. There are reasonably efficient ways to index JSON columns. – Rick James Sep 22 '16 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.