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"]
}
}
}

[arturkulik]

my bad,

there is an index for email:
MariaDB [espocrm]> SELECT INDEX_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'espocrm' AND TABLE_NAME = 'email' AND COLUMN_NAME = 'name';
+-----------------------------+-------------+
| INDEX_NAME | COLUMN_NAME |
+-----------------------------+-------------+
| IDX_SYSTEM_FULL_TEXT_SEARCH | name |
+-----------------------------+-------------+
1 row in set (0.000 sec)

there was no index on process ( bpmn_process) table