Announcement

Collapse
No announcement yet.

Search performance issue

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Search performance issue

    When we try search accounts we have performance troubles.



    Analyze SQL query:

    Code:
    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;​
    Code:
    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

  • #2
    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.

    Comment


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

      1. If use expression like
      Code:
      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
      Code:
      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
      Code:
      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));

      Comment


      • #4
        Code:
        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
        Attached Files

        Comment


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

          Comment

          Working...
          X