Module Faster Search 504 when going to app-settings/meilisearch

[jsarola]

Hi,

I'm using freescout with 7.571.426 threads (more or less), I have problems with searching, and I installed faster search module. Now it works like a charm, but If I want to go to settings, the service gives me a 504 error (timeout).

Searching a little, I found the sql used:

select count(*) as aggregate from threads where id < '7888888' and type in (1,2,3);

And it's very slow (as long as 5 minutes to execute). Someone have this problem and fixed? Adding some index to table threads in MySQL could be better?

PHP version: 8.3.12 FreeScout version: 1.8.154 Database: MySQL Are you using CloudFlare: No Are you using non-official modules: No

[freescout-help]

Apparently your DB server is not powerful enough to digest the number of threads you have.

[jsarola]

Hi,

@freescout-help The database server isn't the problem. The problem is that we have 7M registers in table threads, and MySQL InnoDB doesn't like so much queries like select count(*) with where clauses without indexes.

I created a workaround to change this behavior:

ALTER TABLE threads ADD INDEX threads_meilisearch_id_type_index (id, type), ALGORITHM=INPLACE, LOCK=NONE;

With this index with the two values used, the query only last 2 seconds. I don't know if it's possible to add this index when deploying freescout.

Regards,

[freescout-help]

The index has been added to the threads table in Faster Search Module v1.0.14