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.

[arturkulik]

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/

Thank You, so i guess onlu createing index will increase speed for all queries or will it be ommited?

i can force index additionali in ORM or should i use it every time?

On Email will it be:


{
"indexes": {

"fastSearchIndex": {
"columns": ["name", "bodyPlain"]
}
}
}