I understand the performance point, but when you reference entities of external systems, then the ID there is often a string. Making it an integer now can badly break 3rd party extensions.

Thanks @laoneo, can you make an example of custom fields being used for external system by 3rd party extensions where the ID is a string? In my mind I can't think of any, so I'm curious to know what you refer to.

For example DPCalendare integrates google calendar events where the id is a string https://developers.google.com/calendar/api/v3/reference/events#resource

Or also MS Exchange/Microsoft365 events.

So you made it this way for the only purpose of supporting your own extension... =/

No, just for every developer. You wanted an example, I gave you one as I can only speak for myself.

Yeah but an ID is usually a numeric auto-increment value in a component.

In DPCalendar you could have probably had an auto-increment id column as the primary id of an entity, and a secondary 'external-id' that pointed to the google-calendar string.

Same would go for other developers as well.

So that in the fields_values table, item_id would reference the primary (numeric) id and could universally be an INT.

Honestly, a non-numeric primary ID of an entity is something extremely rare or bad design. At least in Joomla because it's Joomla we're talking about. It's something that I've never seen for any Joomla extension I've ever used in 15 years

You really have to let it go that web sites are monolythic in itself closed systems. The web is connecting each other.

Well, whatever. If we can't change item_id to be an INT (solution "B"), we still have a problem to fix.

So solution "A" in my opening post.

We have to fix the queries that use item_id, and make them treat item_id as a string. Otherwise, the index is not used in the queries, making them unnecessarily much slower, because they're comparing against the wrong data type.

Fixing the queries would be a welcome addition 👍

I want to supplement!

It is very difficult to work with the #__fields_values table, I am currently developing a material filter in which this table slows down the filter. The problem is that the category_id columns are simply needed in the table, This is necessary in order to make a selection already when you are in the parent category, a situation arises when you need to filter the materials of child categories, here a problem arises, you need to make a join from the table #__fields_categories, which may NOT contain child categories, but only the parent. Or Make a selection of materials by category, and then join #__fields_values to this selection by linking the material id and item_id fields from #__fields_values Such selections consume a lot of resources! Since we have to list all the IDs of materials from child categories.

There may be a conflict with the category IDs of other components, so the context field is also needed the same as in the table #__fields

The category id column should contain the actual category of the material, not the selected category in the field settings.
If you do, I will give joomla users a real, fast filter!

Don't forget that you will need to convert all the existing data

Don't forget that you will need to convert all the existing data

Maybe just a new table with a transfer from the old one? ))))

It would still need to be done however you do it

Or you can create an aggregated view, no need to duplicate the data.

Hello, any update on an improvement with this?

If we can't change item_id to be an INT (solution "B"), we still have a problem to fix.

So solution "A" in my opening post.

We have to fix the queries that use item_id, and make them treat item_id as a string. Otherwise, the index is not used in the queries, making them unnecessarily much slower, because they're comparing against the wrong data type.

In addition to the above, the fields_values table does not have a primary ID. Shouldn't all tables have one?

ALSO, I have tried to join the fields_values to the content table by converting the content.id to a string, using:

INNER JOIN m2f0c_fields_values ON
CAST(m2f0c_content.id AS CHAR(5)) = m2f0c_fields_values.item_id

but get a red message telling me

"Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' "

which implies that the database is a mixture of general and unicode types.
The database I'm using was new for a reference copy of Joomla 5.

@AndySDH's option a) above might work within the Joomla environment, but I need to do some filtering in the database first, as it's not possible - as far as I know - to sort by custom field value in Joomla.

In addition to the above, the fields_values table does not have a primary ID. Shouldn't all tables have one?

in this case, a unique identifier is unnecessary and even harmful! since the unique identifier has the property of ending

There are really two ways to go about it.

Not really, I add option c:
c) Add a new content_id column, and use it instead of item_id. And drop item_id somewehre in future.

I encountered the same issue with a customer a few days ago. I have around 2 million rows in that table and running my queries on the table took more than 60 seconds for just that one query. I first converted the column to integer and that reduced the time to a little bit more than 30 seconds. However, when I added a multi-column-key across item_id and field_id, it reduced this to around 12 seconds. Maybe that key could help at least a little bit as well.

The code would be

ALTER TABLE `d58fc_fields_values`
	ADD INDEX `idx_field_id_item_id` (`field_id`, `item_id`);

There are really two ways to go about it. (not a fan of option "a")

a) keeping the item_id column as it is, making sure that all queries on item_id are done with $db->quote around it so that is treated as a string.

@AndySDH The core uses as far as I can see prepared statements and binds the item_id already as a string and handles it also as a string in PHP. $db->quote should only be used with not-prepared statements, of which I haven't found any in the core for that column. The only place I could find where the core handles it as an integer is in the Blog Sample Data plugin when it inserts that author field which is used in that sample data.

So for b/c reasons I would go with solution a.

Regarding the indexes: In J5 index lengths in MySQL/MariaDB are not limited anymore to 191 characters with utf8mb4, so we can change the existing index on that column to use the full length, and we can add the combined index over the field_id and index_id columns.

I encountered the same issue with a customer a few days ago. I have around 2 million rows in that table ...

@Hackwar Is that site J4? Or J5? If J4: Could you update a copy of that site to J5 (assuming the database requirement is fulfilled)?
And then, when J5:
What happens on your test site with the 2 million field values if you revert the change of the item_id column to integer so it's again a varchar(255), modify the existing index so that it uses the full length of that column and not only 191 characters and add a combined index like you suggested, all in following way because the database checker would complain if you use the same name for the index which you modify:

ALTER TABLE `#__fields_values` DROP INDEX `idx_item_id`;
ALTER TABLE `#__fields_values` DROP INDEX `idx_field_id_item_id`;
ALTER TABLE `#__fields_values` MODIFY `item_id` varchar(255) NOT NULL;
ALTER TABLE `#__fields_values` ADD INDEX `idx_item_id_2` (`item_id`);
ALTER TABLE `#__fields_values` ADD INDEX `idx_field_id_item_id` (`field_id`, `item_id`);

Is the performance of the CMS core similar as with your modifications? Or at least better than the unmodified core?

#__fields_values table contains 2 million rows and my testing query contains 5 joins on that table to get different field values, among them a group_concat to get a multi-value field.

Running that query without any modifications ran for 14 minutes and then I aborted it.

Running that query with column changed to int takes 33.37s.

Running that query with index added over field_id and item_id ran for 6 minutes and I also aborted.

Running that query with column changed to int and index over field_id and item_id takes 16.78s.

Result set each time is supposed to be 100k rows.

I think the result is pretty clear. We will have to change the type of the column.