Restructure custom fields value table

[HLeithner]

Maintainers discussed 2 issues several times since custom fields have been introduced.

• Primary Key for the #__fields_values (ex. J 4.x.x + - Add an unique id to fields table #43707)
• Change value type to longtext (ex. Change value column of #__fields_values from text to longtext #21762)
• Make item_id an integer (ex. [com_fields] item_id column in fields_values table is of wrong type (VARCHAR instead of INT) #35795)
• Make rows unique per field_id + context id for "easier query" and save data to json if needed

Pretty sure their are more things people like to change so this discussion is for this.

Changes to consider separately

1. Add a Primary key (easier editing)
2. Change item_id to integer (better performance, needs to be validated)
3. Change value column to a bigger datatype
4. Unique field_id and item_id combination

Annotations

1. Only needed if you make changes in phpmyadmin but might not hurt since MySQL holds an internal index if no primary key exists (IIRC ymmv)
2. Changing this to an integer creates an issue if you have multi column primary key tables which is nativ supported by joomla, also I'm not sure if the performance impact is such big some real benchmarks would be good (for example there are applications which uses GUID as primary key). Never the less, this could be achieve by adding a second integer or big integer column in addition to the item_id column
3. Making the value column a LONGTEXT shouldn't make any noticeably performance impact only issue I see is people fill it with big data which might has a real performance impact and people would complain joomla because of this
4. Personally I hate to group_contact or now with json contact rows just because a couple of fields using multiple rows to save the content. Multiselect is one of the few fields. Using JSON instead would easier allow to filter for values and don't need to group queries. B/C Impact is relevant of course.

Possible solutions

I can think of the following changes:

Modification to the table

#__fields_values (changing the current table)

• id (Primary Bigint) - Solves # 1
• field_id (unique with item_id)
• item_id (keep as varchar but make it unique) - Solves # 4
• context_id (Big/int with the same content as item_id if it's an integer) - Solves # 2
• value (convert to json if multiple item_ids exists and use json query syntax if someone want to filter it) - Solves # 3, # 4 needs convert to JSON

Alternative 1

do the same but just add in a new table to not break 3rd party which expect the database being unchanged

#__fields_values_second_try

• id (Primary Bigint) - Solves # 1
• field_id (unique with item_id)
• item_id (Bigint - unique) - Solves # 2 and # 4
• context (varchar same as item_id but allow custom values) - Solves Test #2 needed to support strings
• value (longtext) or directly JSON - - Solves # 3, # 4 needs convert to JSON

Alternative 2

#__fields_values_3rd_try

• id (Primary Bigint) - Solves # 1
• field_id (unique with item_id)
• item_id (Bigint - unique) - Solves # 2 and # 4
• context (varchar same as item_id but allow custom values) - Solves Test #2 needed to support strings
• value_text (longtext type) - Solves # 3 and might support better performance for different datatypes
• value_json (JSON type) - Solves # 3 and might support better performance for different datatypes
• value_int (bigint type) - Solves # 3 and might support better performance for different datatypes
• value_float (float type) - Solves # 3 and might support better performance for different datatypes
• value_do we have another relevant type?

All of them are only an idea and could be discussed.

[bembelimen]

1. Add a Primary key (easier editing)

I think phpmyadmin should not be the way to edit values, as this is really a key/value table, we should not add an (unused) autoincremental ID, because the values are always deleted and newly saved. The number of operations are therefore limited.

2. Change item_id to integer (better performance, needs to be validated)

Tested on my sites with a few million entries and this has a huge impact on performance

3. Change value column to a bigger datatype

So far had never the issue with not enough space, but who knows... No feelings about this one.

Unique field_id and item_id combination

This could probably the primary key we're looking for?

[coolcat-creations]

Unique field_id and item_id combination

But they are not unique at all, I have for example an article custom field and it looks like that:

item_id - field_id - value
1234 30 87663
1234 30 87664
1234 30 87665

[HLeithner]

As written above, they are not unique now but should be in my opinion and the values should be stored in json instead multiple rows. It's used by multi select field for example

[coolcat-creations]

Ok but the problem is, is that the values are already stored like that... B/C Break ?

[chmst]

How can they be stored like that? If you store the item with id 1234, then the model first deletes the old field_values and then stores it new.

[HLeithner]

you can save multiple rows, as said already the multi select uses this feature.

fun part, no b/c promise on Database layer, only that we preserve data on conversation.

That's also the reason why you should not manipulate any query or query the database directly.

[rdeutz]

Its true that we gives no b/c promise on the database structure, we "only" have to convert data. But in this case I am pretty sure when we change this we will break a lot of sites. I wouldn't do this.

[chmst]

2. Change item_id to integer (better performance, needs to be validated)

Tested this. My plugin had 2 minutes response time for a search (many fields and 70k fields_values).
I changed the id to integer and response time was 2 seconds. (big query with many joins).

[HLeithner]

can you post an explain on your query, because it seems that mysql doesn't use an index or the wrong index

[AndySDH]

can you post an explain on your query, because it seems that mysql doesn't use an index or the wrong index

It's explained here in my OP: #43709 (reply in thread)

Trying to compare an integer value against a varchar column, means that the index won't be used on the query.

[sakiss]

Please mind that this table can be used for filtering. Filtering is essential for medium to big websites, using custom fields.

A typical (but simplified) sql query for getting item_id(s), based on custom field values from 2 distinct custom fields (ids: 2, 4) looks like:

SELECT `fields_values`.`item_id`
FROM `#__fields_values` AS `fields_values`
INNER JOIN `#__fields_values` AS `fields_values2` ON `fields_values`.`item_id`=`fields_values2`.`item_id`
WHERE (`fields_values`.`value` IN ('Sweet', 'Salty') AND `fields_values`.`field_id`=2) AND (`fields_values2`.`value` IN ('Tall'. 'Short') AND `fields_values2`.`field_id`=4);

The whole concept here, is based on crosschecking (inner joining) records returned based on their value.

Considerations

• Querying and indexing specific fields within JSON, is slower compared to normalized relational data.
• Indexing JSON fields, seem to be quite sophisticated and has a lot of idiosyncrasies (JSON_EXRACT vs native SQL query).
• The size of a json column is slightly lower to the long text (data types storage).
• Changing the database schema in such a table, will certainly break a lot of websites, using 3rd party or custom solutions.

Also, i am not sure that i understand the benefits of your approach to using json for storing multiple values.

If you want to avoid duplication of information (1NF) and avoid partial dependencies (2NF), the way to do it is by creating 1 more table to store the values per custom field and then use their pk to store the item_id > field_value_id dependency. But again, i think this requires a lot of code changes and will break a lot of websites due to changes in the db schema.

If the scope of that suggestion is just to improve performance, a unique index can be created, using all the 3 fields (field_id, item_id, value) with limits to the length of the value.

[HLeithner]

• Did you ever tried querying a column with JSON compared with multi row entries? at least my short test gives me a 100% performance gain with a json array compared to values in multiple rows.
• at lease you can index json fields
• the size doesn't matter here because in our case we would have the same data split into multiple rows (for an json array we have additional 4 bytes [""] on single value rows and additional 3 ,"" for multi row columns compared to the field_id (4 bytes) item_id (varchar()).
• I could say that it's not intended that you every touch the data in the database directly but of course that's unrealistic, so a change needs to be done in a way that allows a migration over a long time period (maybe our 2 major b/c policy), this might could be achieved with a new column and triggers

but as said I'm searching for solutions not for "no that's not possible because xyz"

[sakiss]

Please provide an example of the query you tried

[HLeithner]

mysql> SELECT SQL_NO_CACHE count(*), field_id, item_id, value FROM `HcHUw_fields_values` a WHERE a.field_id = 2 and JSON_CONTAINS(a.value, '1') group by field_id;
+----------+----------+----------+---------+
| count(*) | field_id | item_id  | value   |
+----------+----------+----------+---------+
|   123660 |        2 | 37820647 | [1,2,3] |
+----------+----------+----------+---------+
1 row in set, 1 warning (0.42 sec)

mysql> SELECT SQL_NO_CACHE count(*), field_id, item_id, value FROM `HcHUw_fields_values` a WHERE a.field_id = 3 and a.value = '1' group by field_id;
+----------+----------+----------+-------+
| count(*) | field_id | item_id  | value |
+----------+----------+----------+-------+
|   123657 |        3 | 36591435 | 1     |
+----------+----------+----------+-------+
1 row in set, 1 warning (0.95 sec)

in this case mysql has to consider 123660 with json data and 370971 with a number in a mediumtext field.

if this is a usual case is different question but saying that json is always slower is simple not true. That because how the data is saved.

[Fedik]

Would do, version 1.
But keep item_id for b/k as it is (and trash it at some point in future), and use content_id (instead of context_id, it is confusing name to me)

id 
field_id 
item_id 
content_id 
value 

And I would not bother with JSON, just a BIGTEXT for the value will be fine.

Having "multiy type" columns (or tables) will give us only much headache with minimal gain.