Use FTS for contact search instead of LIKE

[fladi]

We are experiencing long delays when selecting contacts to create new shares for. Searching takes about 20-30 seconds after the user has finished typing.

Our installation runs Nextcloud 16 with PHP 7.3 and MariaDB 10.3. We have 22k rows in oc_cards and 140k rows in oc_cards_properties.

Testing the queries created by OCA\DAV\CardDAV\CardDavBackend->search manually showed that each one takes about 4 seconds to complete, like this one:

SELECT `c`.`carddata`, `c`.`uri` FROM `oc_cards` `c` WHERE `c`.`id` IN (SELECT DISTINCT `cp`.`cardid` FROM `oc_cards_properties` `cp` WHERE (`cp`.`addressbookid` = 'XXX') AND ((`cp`.`name` = 'EMAIL') OR (`cp`.`name` = 'FN')) AND (`cp`.`value`  COLLATE utf8mb4_general_ci LIKE '%pattern%'));

As LIKE statements can only make use of indizes if they do not start with a % wildcard, each query has to to a full table scan.
I tried to compare this with a FTS index on the oc_cards_properties.value field and using MATCH ... AGAINST ... instead of LIKE:

CREATE FULLTEXT INDEX oc_cards_properties_name_FTS ON nextcloud.oc_cards_properties (value);
SELECT `c`.`carddata`, `c`.`uri` FROM `oc_cards` `c` WHERE `c`.`id` IN (SELECT DISTINCT `cp`.`cardid` FROM `oc_cards_properties` `cp` WHERE (`cp`.`addressbookid` = 'XXX') AND ((`cp`.`name` = 'EMAIL') OR (`cp`.`name` = 'FN')) AND (MATCH(`cp`.`value`) AGAINST('pattern')));

Now the query took only ~220ms instead of 4000ms.

Would you consider switching to Full-Text-Search, at least for MySQL/MariaDB? Large installations like ours would certainly benefit from it. Doctrine support for MATCH is available in https://github.com/beberlei/DoctrineExtensions.

[kesselb]

Thanks for reporting 👍

Probably this patch #18816 could also improve the lookup speed?

[fladi]

@kesselb I don't think so, the patch in #18816 only limits the number of results for a search but in the worst case (number of rows matching is lower than the limit) still results in a full table scan without indices.

I have now patched my local installation to use FTS and the search times have gone down from 30 seconds to 1 second. I'm willing to contribute my changes but I'm not that familiar with writing tests in PHP so I would need someone to guide me on this.

[kesselb]

cc @juliushaertl @nickvergessen

[lostmap]

@fladi could you please somehow share your patch. I think we are facing the same problem. We' ve got about 70k LDAP users and searching takes about 10-20 seconds. Reducing it to 1 second would be a great result for us. In our case one query takes about 8 seconds.

# Query_time: 8.536125  Lock_time: 0.000076  Rows_sent: 2  Rows_examined: 433223
# Rows_affected: 0  Bytes_sent: 19316
SELECT c.carddata, c.uri FROM oc_cards c WHERE c.id IN (SELECT DISTINCT cp.cardid FROM oc_cards_properties cp WHERE (cp.addressbookid = '1') AND ((cp.name = 'EMAIL') OR (cp.name = 'FN')) AND (cp.value  COLLATE utf8mb4_general_ci LIKE '%Petrov Ivan%'));

[nickvergessen]

Maybe we can also do some other improvements to reduce the complexity of the query.
E.g. when there is a space in the search string, we don't even need to check for an email address anymore. 🤔

[fladi]

@lostmap I just pushed my changes from my local fork: fladi@e2a0f2f

You will need to create the FTS index by hand as described in the commit message.

@nickvergessen I do not think that this will help that much as the fundamental problem is LIKE being unable to use indices. Which, at large tables, will always lead to slow queries.

[nickvergessen]

I do not think that this will help that much as the fundamental problem is LIKE being unable to use indices.

The thing is if we don't run the query at all anymore, the query is a lot faster ;)

[macleajb]

I do not think that this will help that much as the fundamental problem is LIKE being unable to use indices.

The thing is if we don't run the query at all anymore, the query is a lot faster ;)

I would like to confirm that our install (120k+ useri) with this patch reduces 15-25 second wait time on the same query to less than 3 seconds (as that is what we have reporting in the MySQL slow log).