creating INDEX on Espo Db tables

[arturkulik]

Hi Guys,
has anyone created an INDEX on Email Table or any other table?
we have 270k+ emails and queries are terribly slow searching full text

will it be cleared after Espo upgrade?

will it have any impact on other processes than searchng?

is there any risk?

[yuri]

Hi Artur,

Indexes should be defined in metadata. If created in DB directly, they will be dropped on rebuild.

https://docs.espocrm.com/development/db-indexes/

[yuri]

For emails, you can also try the following. Under Administration > Entity Manager > Email > Edit, remove the "Body" field from the "Text Filter Fields" parameter. Leave only "Name (Subject)" and "Body (Plain)". Then run rebuild from CLI.

It should improve full-text search performance.