Some configuration and operational considerations to optimize your queries and application.

Studying the slow queries in the application, we ran into this query

SELECT GET_LOCK('session_60bfca435778a17dc2fa2deaada9bf80a0143aef', 60)

The SELECT GET_LOCK(...) function is used to acquire an application-level advisory lock. Your pt-query-digest output shows that your application is waiting an average of 7 seconds to acquire this lock, with some waits as long as 14 seconds. This means the lock is being held by another process for a very long time, forcing your current process to wait.

The problem isn't with the query's performance—it's with the application's design. The long wait times are a symptom of a process that is acquiring a lock and holding on to it for far too long, blocking other processes that need the same lock.

This query is not a traditional data-retrieval query; it's a session-level lock request. The high execution time indicates a serious concurrency problem in your application.

This problem appears because we moved the native file-based PHP session management to the database a few years ago. We did this to support running the application in a load-balancer multi-docker environment. The truth is that nobody uses that. We should have foreseen that and kept an option to switch between the database and files, like we have done with other functions, but we didn't.

Since supporting files will require a full project with designated resources, we had the idea of copying the table to a MySQL memory table. So the code is the same and the table is also, just that now it lives in RAM instead of disk. This makes it a LOT faster to access and reduces the time spent waiting. Albeit, very little.

You can activate this feature and give it a try by changing the $cbodPHPSessionInDatabase variable in the modules/Settings/configod.php file.

Note some things:

• when you change the value of that variable, you MUST execute drop session_data in the database
• all your users will be logged out of the application
• all users will be logged out when the MySQL server is restarted

Another path that we should investigate is to use a More Granular Locking Strategy:

If your application is using this lock to protect a large resource (like an entire table), consider using a more granular approach. Instead of locking the whole thing, can you lock just the specific record or a smaller piece of data? This would reduce lock contention and allow other processes to run in parallel.

Translating picklist values is a very computationally intensive operation, as we have to reverse translate the given values for searching and similar tasks. This can easily create complex queries like this:

SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_account.accountname, vtiger_potential.related_to, vtiger_potential.potentialname, vtiger_potential.sales_stage, vtiger_potential.amount, vtiger_potential.currency, vtiger_potential.closingdate, vtiger_potential.typeofrevenue, vtiger_potential.email, vtiger_potentialscf.*
 FROM vtiger_potential
 INNER JOIN vtiger_crmentity as vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_potential.potentialid 
 INNER JOIN vtiger_potentialscf ON vtiger_potentialscf.potentialid = vtiger_potential.potentialid 
 LEFT JOIN vtiger_account ON vtiger_potential.related_to = vtiger_account.accountid 
 LEFT JOIN vtiger_contactdetails ON vtiger_potential.related_to = vtiger_contactdetails.contactid 
 LEFT JOIN vtiger_campaign ON vtiger_campaign.campaignid = vtiger_potential.campaignid 
 LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid 
 LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid 
 WHERE vtiger_crmentity.deleted = 0  and (vtiger_potential.potentialname LIKE '%aff1716%' OR vtiger_potential.potential_no LIKE '%aff1716%' OR vtiger_potential.amount LIKE '%aff1716%' OR vtiger_potential.related_to LIKE '%aff1716%' OR (vtiger_potential.leadsource IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::leadsource" and i18n LIKE "%aff1716%") OR vtiger_potential.leadsource LIKE "%aff1716%") OR vtiger_crmentity.smownerid LIKE '%aff1716%' OR vtiger_crmentity.createdtime LIKE '%aff1716%' OR vtiger_crmentity.modifiedtime LIKE '%aff1716%' OR vtiger_crmentity.modifiedby LIKE '%aff1716%' OR vtiger_crmentity.description LIKE '%aff1716%' OR vtiger_potential.email LIKE '%aff1716%' OR vtiger_potential.isconvertedfromlead LIKE '%aff1716%' OR vtiger_potential.convertedfromlead LIKE '%aff1716%' OR vtiger_crmentity.smcreatorid LIKE '%aff1716%' OR vtiger_potentialscf.cf_1115 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1116 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1119 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1120 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1121 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1122 LIKE '%aff1716%' OR (vtiger_potentialscf.cf_1123 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1123" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1123 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1124 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1124" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1124 LIKE "%aff1716%") OR vtiger_potentialscf.cf_1126 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1160 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1161 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1162 LIKE '%aff1716%' OR (vtiger_potentialscf.cf_1170 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1170" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1170 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1171 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1171" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1171 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1180 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1180" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1180 LIKE "%aff1716%") OR vtiger_potentialscf.cf_1255 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1257 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1259 LIKE '%aff1716%' OR (vtiger_potentialscf.cf_1270 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1270" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1270 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1272 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1272" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1272 LIKE "%aff1716%") OR vtiger_potentialscf.cf_1300 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1301 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1304 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1335 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1336 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1380 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1404 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1408 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1414 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1415 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1417 LIKE '%aff1716%' OR (vtiger_potentialscf.cf_1443 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1443" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1443 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1446 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1446" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1446 LIKE "%aff1716%") OR vtiger_potentialscf.cf_1450 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1453 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1454 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1457 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1458 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1459 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1461 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1462 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1463 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1464 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1468 LIKE '%aff1716%' OR (vtiger_potentialscf.cf_1546 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1546" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1546 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1547 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1547" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1547 LIKE "%aff1716%") OR (vtiger_potentialscf.cf_1548 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1548" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1548 LIKE "%aff1716%") OR vtiger_potentialscf.cf_1550 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1551 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1552 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1554 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1555 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1556 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1557 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1558 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1559 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1560 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1561 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1562 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1564 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1565 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1566 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1567 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1568 LIKE '%aff1716%' OR (vtiger_potentialscf.bardage IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::bardage" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.bardage LIKE "%aff1716%") OR vtiger_potentialscf.commentaire LIKE '%aff1716%' OR vtiger_potentialscf.datederniercommentaire LIKE '%aff1716%' OR (vtiger_potentialscf.menuiserie IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::menuiserie" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.menuiserie LIKE "%aff1716%") OR (vtiger_potentialscf.pnxinterieur IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::pnxinterieur" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.pnxinterieur LIKE "%aff1716%") OR vtiger_potentialscf.connexionappli LIKE '%aff1716%' OR vtiger_potentialscf.moisetannepertedevis LIKE '%aff1716%' OR vtiger_potentialscf.cf_1659 LIKE '%aff1716%' OR vtiger_potentialscf.cf_1664 LIKE '%aff1716%' OR (vtiger_potentialscf.cf_1667 IN (select translation_key from vtiger_cbtranslation
 where locale="fr_fr" and forpicklist="Potentials::cf_1667" and i18n LIKE "%aff1716%") OR vtiger_potentialscf.cf_1667 LIKE "%aff1716%"))\G

That is a mouthful.

So, if you are using only one language, deactivate translation in as many picklists as you can. This is done in the Picklist Editor settings section

The previous query has some more recommendations we can extract. The performance issue is entirely in the WHERE clause. There are two main culprits that are forcing MySQL to perform a massive, inefficient scan:

Leading Wildcards in LIKE: The query uses LIKE '%aff1716%' on dozens of columns. When the wildcard (%) is at the beginning of the search term, MySQL cannot use a standard index. Instead of looking up a specific value, it has to scan every single row of the table to find a match. The only way to find a match is to read and check the data on every single record.

So we MUST optimize our searches if we can. Avoid the "contains" operator, use any of the other operators.

We can also implement Full-Text Search

For searching through text across many columns, full-text search is the correct tool. It's designed to solve exactly this problem.

You would need to create a FULLTEXT index on the columns you want to search. You can combine multiple columns into a single index for your LIKE searches. For example:

ALTER TABLE vtiger_potential ADD FULLTEXT (potentialname, potential_no, amount, ...);

Study the slow queries with custom fields and add indexes. This is important. Really important.

The whole article is full of examples, but I leave one that is clear:

SELECT vtiger_crmentity.*, vtiger_salesorder.*, CASE WHEN (vtiger_users.user_name NOT LIKE '') THEN vtiger_users.ename ELSE vtiger_groups.groupname END AS user_name, vtiger_salesordercf.* FROM vtiger_salesorder INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_salesorder.salesorderid LEFT JOIN vtiger_salesordercf ON vtiger_salesordercf.salesorderid = vtiger_salesorder.salesorderid INNER JOIN vtiger_account ON (vtiger_account.accountid = vtiger_salesorder.accountid or vtiger_account.accountid = vtiger_salesordercf.cf_1956) LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid WHERE vtiger_crmentity.deleted=0 AND vtiger_account.accountid = 10003 LIMIT 0, 20\G

vtiger_account.accountid = vtiger_salesordercf.cf_1956

Add an index to cf_1956: The cf_ columns are often custom fields and might not be indexed by default. Creating an index on vtiger_salesordercf.cf_1956 is essential. ALTER TABLE vtiger_salesordercf ADD INDEX (cf_1956);

CREATE TEMPORARY TABLE vtiger_reptmptbl_1068ba81bcb43126425216770 AS SELECT vtiger_quotes.*,cf_1603,cf_1608,
cf_1611,cf_1612,cf_1614,cf_1615,cf_1618,cf_1619,cf_1620,cf_1624,cf_1625,cf_1626,cf_1627,cf_1628,cf_1629,cf_1630,
cf_1632,cf_1633,cf_1634,cf_1635,cf_1637,cf_1638,cf_1640,cf_1642,cf_1644,cf_1645,cf_1646,cf_1647,cf_1649,cf_1651,
cf_1655,cf_1656,cf_1657,cf_1659,cf_1661,cf_1663,cf_1665,cf_1668,cf_1673,cf_1674,cf_1675,cf_1676,cf_1677,cf_1678,
cf_1679,cf_1680,cf_1681,cf_1682,cf_1683,cf_1684,cf_1685,cf_1967,cf_1968,cf_1969,cf_1970,cf_1974,cf_1975,cf_1976,
cf_1977,cf_1995,cf_1996,cf_2018,cf_2019,cf_2020,cf_2021,cf_2024,cf_2025,cf_2028,cf_2031,cf_2032,cf_2038,cf_2039,
cf_2040,cf_2041,cf_2042,cf_2046,cf_2047,cf_2048,cf_2049,cf_2050,cf_2051,cf_2052,cf_2060,cf_2061,cf_2062,cf_2063,
cf_2064,cf_2075,cf_2110,cf_2146,cf_2150,cf_2185,cf_2186,cf_2187,cf_2188,cf_2215,cf_2259,cf_2260,cf_2335,cf_2469,
cf_2476,cf_2477,cf_2478,cf_2484,cf_2490,cf_2491,cf_2492,cf_2496,cf_2499
 FROM vtiger_quotes
 INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_quotes.quoteid AND vtiger_crmentity.deleted=0
 LEFT JOIN vtiger_quotescf ON vtiger_quotescf.quoteid=vtiger_quotes.quoteid

Probably better to do an export or, even better, copy this information into a reporting tool and get the information from there without straining the production database. Metabase is your friend.

Another example

SELECT vtiger_crmentity.*, vtiger_potential.*, vtiger_users.user_name, vtiger_potentialscf.*
 FROM vtiger_potential
 INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_potential.potentialid
 LEFT JOIN vtiger_potentialscf ON vtiger_potentialscf.potentialid = vtiger_potential.potentialid
 INNER JOIN vtiger_account ON (vtiger_account.accountid = vtiger_potential.related_to
  or vtiger_account.accountid = vtiger_potentialscf.cf_2314
  or vtiger_account.accountid = vtiger_potentialscf.cf_2321
  or vtiger_account.accountid = vtiger_potentialscf.cf_2322
  or vtiger_account.accountid = vtiger_potentialscf.cf_2323
  or vtiger_account.accountid = vtiger_potentialscf.cf_2330)
 LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
 LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
 WHERE vtiger_crmentity.deleted=0 AND vtiger_account.accountid = 533330 LIMIT 0, 35

This query is a complex SELECT statement that's taking too long to execute, with an average time of 228ms and a maximum of 419ms. While this might not seem high, the fact that it's running 104 times and examining over 4 million rows indicates a serious performance bottleneck.

• The Problem: The query is performing multiple JOIN operations on several tables (vtiger_potential, vtiger_crmentity, vtiger_potentialscf, vtiger_account, vtiger_users, and vtiger_groups). The Rows examine count of 40.18k is a per-query metric, which means for each of the 104 executions, the database engine is checking that many rows to satisfy the query.
• The Culprit: The main performance killer is the INNER JOIN on vtiger_account. The OR conditions in the join clause are highly inefficient. OR conditions, especially in a JOIN, prevent MySQL from using a single index effectively. Instead, it may have to perform multiple lookups and a union of results in the background, which is very slow.

Here is the problematic section of your query:

INNER JOIN vtiger_account ON (vtiger_account.accountid = vtiger_potential.related_to or vtiger_account.accountid = vtiger_potentialscf.cf_2314 or ...)

To fix this, you need to simplify the join condition and ensure all columns involved have proper indexes.

1. Add Indexes (Immediate Fix)

The most important and likely missing indexes are on the columns used in the OR condition.

• Add indexes to the cf_ columns: Create a separate index on each of the cf_ columns listed in the JOIN condition: cf_2314, cf_2321, cf_2322, cf_2323, cf_2330.
• Add a compound index on vtiger_crmentity: Ensure there is a compound index on (crmid, deleted) as this is the main WHERE clause.

2. Refactor the Query (Long-term Solution)

The best way to fix the OR condition is to rewrite the query using a different approach. The OR in a JOIN is often a sign of a bad schema design or a flawed query.

This is a dense post, very technical, but the topic we are discussing is a very technical topic. I think the important message is that there are some must-do tasks that have to be done when putting this application into production, at least for large installations or servers with a lot of installations running (a typical scenario).

Also note that these are all mostly micro-optimizations; it is not like any of these are going to make an enormous difference in the overall performance of the application. It will make it faster, and if you have a lot of installations on the same server, you will be saving common resources that will make everything go faster, but it is just another of the many things you must do to make your servers and applications performant.

HTH