When saving a data object, index definitions are not applied to the object_query table.

[andreas-gruenwald]

Bug Report

In my product's class definition there is a field called "remoteId". I recognized performance issues in relation with the product, so I revised the field's index definition:

First of all, the field is marked as "unique", so I assumed that the database definition (object_query_product) would contain a key with a "unique index" definition, or at least a non-unique index. However this was not the case.

Expected behavior

• index flag creates a SQL index for the field "remoteId" in the object_store table.
• index flag creates a SQL index for the field "remoteId" in the object_query table.
• unique flag creates a SQL "unique" index constraint for the field "remoteId" in the object_store table.
• unique flag creates a SQL "non-unique" index constraint for the field "remoteId" in the object_query table (as unique in query_tables is a too big constraint).

Actual behavior

• Indices are created for the store tables, but no index is created for the query table at all.

Steps to reproduce

• Create a product class.
• add a text field named "remoteId".
• Save the class definition.
• Make some changes.
• Edit the class definition and mark the field as "unique" and "index".
• Index is not updated (may require that there are many products/objects in Pimcore).

Pimcore Version

Your Version: v5.8.0

[andreas-gruenwald]

I just figured out, that in DataObject\ClassDefinition\Dao.php the unique key is only applied to the object_store table, not to the object_query table. If a key is defined as "unique", then in the object_query_table no key is defined at all. Does that mean that JOIN statements always should be applied on the store table?

see https://github.com/pimcore/pimcore/blob/master/models/DataObject/ClassDefinition/Helper/Dao.php#L44: enabled is false in my case for the object_query table.

[fashxp]

hmm, cannot be reproduced on demo application.

on which table are you checking?

[andreas-gruenwald]

Please see my screenshot:
$considerUniqueIndex is false for the table "object_query_product", so both, unique index, and index are getting disabled, if both "index" and "unique" are selected. If only "unique" is selected, then it seems that also no index is set on the object_query_product table.

Is this on purpose? In my opinion, the query table should at least receive an (non-unique) index.

[andreas-gruenwald]

@weisswurstkanone Issue description updated.

[weisswurstkanone]

just as a reference but not directly related to it
#2943