Search performance issue

[dimyy]

When we try search accounts we have performance troubles.



Analyze SQL query:

explain
SELECT account.id               AS `id`,
       account.assigned_user_id AS `assignedUserId`,
       account.created_by_id    AS `createdById`,
       account.name             AS `name`,
       account.inn              AS `inn`,
       account.kpp              AS `kpp`,
       account.org_status       AS `orgStatus`,
       account.created_at       AS `createdAt`,
       account.modified_at      AS `modifiedAt`
FROM `account`
WHERE (((account.id IN (SELECT entity_email_address.entity_id AS `entityId`
                        FROM `entity_email_address`
                                 JOIN `email_address` AS `emailAddress`
                                      ON emailAddress.id = entity_email_address.email_address_id AND
                                         emailAddress.deleted = 0
                        WHERE entity_email_address.deleted = 0
                          AND entity_email_address.entity_type = 'Account'
                          AND emailAddress.lower LIKE '%потапова%'))) OR
       ((account.id IN (SELECT entity_phone_number.entity_id AS `entityId`
                        FROM `entity_phone_number`
                                 JOIN `phone_number` AS `phoneNumber`
                                      ON phoneNumber.id = entity_phone_number.phone_number_id AND
                                         phoneNumber.deleted = 0
                        WHERE entity_phone_number.deleted = 0
                          AND entity_phone_number.entity_type = 'Account'
                          AND phoneNumber.name LIKE '%потапова%'))) OR
       (MATCH(account.name, account.law_name, account.inn) AGAINST('потапова' IN NATURAL LANGUAGE MODE)))
  AND account.deleted = 0
ORDER BY ROUND((MATCH(account.name, account.law_name, account.inn) AGAINST('потапова' IN NATURAL LANGUAGE MODE) / 5),
               0) DESC, account.created_at DESC, account.id DESC
LIMIT 0, 36;​

1,PRIMARY,account,ALL,,,,,572834,Using where; Using temporary; Using filesort
3,MATERIALIZED,entity_phone_number,ALL,"UNIQ_7459056F81257D5D39DFD528C412EE02,IDX_7459056F81257D5D,IDX_7459056F39DFD528",,,,497955,Using where
3,MATERIALIZED,phoneNumber,eq_ref,"PRIMARY,IDX_NUMERIC,IDX_NAME",PRIMARY,98,admin_crm.entity_phone_number.phone_number_id,1,Using where
2,MATERIALIZED,entity_email_address,ALL,"UNIQ_9125AB4281257D5D59045DAAC412EE02,IDX_9125AB4281257D5D,IDX_9125AB4259045DAA",,,,257462,Using where
2,MATERIALIZED,emailAddress,eq_ref,"PRIMARY,IDX_LOWER",PRIMARY,98,admin_crm.entity_email_address.email_address_id,1,Using where
​

DB don't use fulltext index and full scan account entity_phone_number entity_email_address tables.
Also fulltext index and regular index cannot be combined.

explain select * from account WHERE account.id in ("1","2","3") or MATCH(name, law_name, inn) AGAINST('потапова' IN NATURAL LANGUAGE MODE);
1,SIMPLE,account,ALL,PRIMARY,,,,572834,Using where
full table scan


explain select distinct * from account where MATCH(name, law_name, inn) AGAINST('потапова' IN NATURAL LANGUAGE MODE)
union select * from account WHERE account.id in ("1","2","3");
PRIMARY,account,fulltext,IDX_SYSTEM_FULL_TEXT_SEAR CH,IDX_SYSTEM_FULL_TEXT_SEARCH,0,"",1,Using where
UNION,account,range,PRIMARY,PRIMARY,98,,3,Using where
UNION RESULT,"<union1,2>",ALL,,,,,,""


4 rows scan + union results

[yuri]

I didn't have a chance to investigate more, but on my instance full-text search is seems applied, when the name and email address fields are in the text filter list, but no phone number. Tested on MariaDB.

[dimyy]

Also tested on MariaDB (10.6.8-MariaDB-1:10.6.8+maria~focal-log)

1. If use expression like

phoneNumber.name LIKE '%шестаков%'

index not used. Only expressions without leading wildcard using index. Also we need full-text index
2. I try switch off phone from full-text. Also full text index don't used.
3. Expression

explain select * from contact where (id in ('asdfa', 'asdfasd')) or (MATCH(contact.first_name, contact.last_name, contact.inn) AGAINST('шестаков' IN NATURAL LANGUAGE MODE))

doesn't use index at all
4. If use expressions with 'union' all indices are used

explain select distinct * from contact where (id in ('asdfa', 'asdfasd')) union select * from contact where (MATCH(contact.first_name, contact.last_name, contact.inn) AGAINST('шестаков' IN NATURAL LANGUAGE MODE));

[dimyy]

explain
SELECT distinct contact.id
FROM `contact` use index (`PRIMARY`)
WHERE
    ((contact.id IN (SELECT entity_phone_number.entity_id AS `entityId`
                        FROM `entity_phone_number`
                                 JOIN `phone_number` AS `phoneNumber`
                                      ON phoneNumber.id = entity_phone_number.phone_number_id AND
                                         phoneNumber.deleted = 0
                        WHERE entity_phone_number.deleted = 0
                          AND entity_phone_number.entity_type = 'Contact'
                          AND MATCH(phoneNumber.name) against('шестаков' IN NATURAL LANGUAGE MODE) )))
  AND contact.deleted = 0
union SELECT contact.id
FROM `contact` use index (`PRIMARY`)
WHERE
       ((contact.id IN (SELECT entity_email_address.entity_id AS `entityId`
                        FROM `entity_email_address`
                                 JOIN `email_address` AS `emailAddress`
                                      ON emailAddress.id = entity_email_address.email_address_id AND
                                         emailAddress.deleted = 0[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Screenshot_70.png","data-attachmentid":82451}[/ATTACH]​
                        WHERE entity_email_address.deleted = 0
                          AND entity_email_address.entity_type = 'Contact'
                          AND MATCH(emailAddress.lower) against('шестаков' IN NATURAL LANGUAGE MODE) )))
  AND contact.deleted = 0
union SELECT contact.id
FROM `contact` use index (`PRIMARY`)
WHERE
       (MATCH(contact.first_name, contact.last_name, contact.inn) AGAINST('шестаков' IN NATURAL LANGUAGE MODE))
  AND contact.deleted = 0
LIMIT 0, 36;​

If we use 'union' - all OK

I also manually create full text indexes for name and lower

[yuri]

Unfortunately, I can't take a look any soon.