When we try search accounts we have performance troubles.
Analyze SQL query:
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
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
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
Comment