Tweet
Our Espo installation is quite big: 120.000 accounts, 150.000 contacts, 16.000 leads, 86.000 phone numbers. We have almost 90 CRM users with a linked VOIP profile, and a lot of them works for phone marketing. The load on MySQL was very heavy, and after some investigation I've found tha problem: the plugin do a lot of query in the form of:
format_number(phone_number.name) LIKE "%123456789" or
format_number(phone_number.name) IN ("123456789", "12345", "1234567", "12345678") There is no way that MySQL can use indexes on a field passed through format_number! Given that the plugin activates also on internals calls (e.g. when phne #123 calls #456 in another room), MySQL works like crazy.
Now I'm working on a workaround, overriding plugin classess through \Espo\Custom\* classes, but I'd like to see this approach used natively by the plugin. Basically I've added another column to the `phone_number` table, called `reverse`. Here I write the reversed version of a clean number, so one table's row could be:
id name deleted type reverse
xxxxx +39 (01) 234567 0 Office 7654321093
With this column almost every query can be simplified. The first example become:
phone_number.reverse LIKE '987654321%'
And this is good, because a LIKE search that "begins with" can use indexes.
The second one become way better, because we can search by the reverse of the shortest number version:
phone_number.reverse LIKE '54321%'
Pay attention that queries with ORs or INs often cannot use indexes, so I prefer to get rid of them.
Our server is a 4 core (originally had 2 cores, we increased for performances reasons when we started using VOIP plugin), and under a standard load we improved from a load of 3.5 with MySQL using 350% of CPU to a way more sane 0.7 and isolated busts at 120%.
I've got the authorization by my company to share the code of my modifications, if can be useful for the developers.
format_number(phone_number.name) LIKE "%123456789" or
format_number(phone_number.name) IN ("123456789", "12345", "1234567", "12345678") There is no way that MySQL can use indexes on a field passed through format_number! Given that the plugin activates also on internals calls (e.g. when phne #123 calls #456 in another room), MySQL works like crazy.
Now I'm working on a workaround, overriding plugin classess through \Espo\Custom\* classes, but I'd like to see this approach used natively by the plugin. Basically I've added another column to the `phone_number` table, called `reverse`. Here I write the reversed version of a clean number, so one table's row could be:
id name deleted type reverse
xxxxx +39 (01) 234567 0 Office 7654321093
With this column almost every query can be simplified. The first example become:
phone_number.reverse LIKE '987654321%'
And this is good, because a LIKE search that "begins with" can use indexes.
The second one become way better, because we can search by the reverse of the shortest number version:
phone_number.reverse LIKE '54321%'
Pay attention that queries with ORs or INs often cannot use indexes, so I prefer to get rid of them.
Our server is a 4 core (originally had 2 cores, we increased for performances reasons when we started using VOIP plugin), and under a standard load we improved from a load of 3.5 with MySQL using 350% of CPU to a way more sane 0.7 and isolated busts at 120%.
I've got the authorization by my company to share the code of my modifications, if can be useful for the developers.
Comment