Slow searchspeed and bad search results

[Cijay22]

Steps to reproduce

1. Add larger IMAP Account
2. Search for sender email adresse or any other search keyword
3. I have also removed and added the same accounts again
4. I have also activate the debug mode and delete the cache for the mailboxes
5. I have empty the complete database table for mail where storing the mail informations and also reset the autoincrement key

Expected behavior

1. Result should be showing in a normal time period
2. It should contain allways results unless there are really no results

Actual behavior

1. Search speed with different sizes of IMAP account extremly slow. Period between 30-60+ seconds (e.g. 19gb Mailbox), in a lot of cases we get also not result found although this mail is available.
2. Compared with snappy mail for nextcloud: in snappy mail we get allways results and search speed is between 1-2 seconds with the same account. The complete loading and search function is a lot slower.

Mail app version

4.0.10

Nextcloud version

30.0.4

Mailserver or service

Postfix 3.6.4 and Dovecot 2.3.16

Operating system

Ubuntu 22

PHP engine version

PHP 8.3

Nextcloud memory caching

APCU and REDIS

Web server

Apache (supported)

Database

MariaDB

Additional info

Chrome latest version
Server has SSD-Raid 10 Storage and 80GB RAM for Webserver and Database
Mailserver is running on a seperate server
All Server has a dedicated 1GB Nic / WAN

[kesselb]

Sounds similar to #10448

[ChristophWurst]

Database

MariaDB

Dear @Cijay22,
Thank you for your report.

Could you please enable mariadb's slow query log with a threshold of ~20 seconds, search again with Mail and look if any slow queries were logged? If so, please provide them. They should not be sensitive because mariadb logs the placeholder, not the bind parameters, but please double check and sanitze the queries if necessary.

[Cijay22]

Hi Christoph,

thanks for your fast response, the query log is really big when you run a mailsearch. I have extract the data for you maybee it will help to understand the issue. If you need more data please let me know.

nc_mail_slow_query_log-18122024.txt

[Cijay22]

sorry I have pushed the wrong button issue is not closed :)

[ChristophWurst]

Dear @Cijay22, thank you very much for the slow query look. It looks like there are lots of results, or at least the app is loading loads of messages from the database. There is some chunking going on, and I assume this takes a while and also bloats the PHP request with a lot of in-memory data.
Right now I'm not able to tell which code path triggers this.