During this slow, hot, and fire-lit vacation month, we dedicated time to database optimizations and the regular set of maintenance tasks.

This month I learned about optimizing database queries using generative AI.

Next, I am going to share some of the insights that I gained for EvolutivoFW and some of the indexes that we added due to the study.

update cron status

This very fast query appears in the study because it spends a lot of time in a blocked state. This happens when the cron script launch overlaps. The sequence is: the cron launches, does the update of the cron status row, and starts the execution. While the task is executing, a new launch of the script happens, reaches the row update, and is blocked until the first execution finishes.

This is a desired functionality, so it is a false positive in the slow query analysis.

DART

There were two slow queries in the DART extension. One was due to a missing index, and the other was because the query was applying a function to a field, which makes it impossible for MySQL to use the index. The query looked like this

SELECT setype, crmid, smownerid, modifiedby FROM vtiger_crmentity WHERE DATE(modifiedtime) = $date

That DATE() directly in the SQL makes it impossible to use the index that exists in the crmentity table, forcing a full table scan of one of the biggest tables we have in the application. The change is simple:

SELECT setype, crmid, smownerid, modifiedby FROM vtiger_crmentity WHERE modifiedtime between '$date 00:00:00' and '$date 23:59:59'

This makes the query extremely fast.

I think the most important part of this fix is the fact that when we study and optimize an EvolutivoFW installation we rarely take into consideration the extensions we build, leaving behind essential optimizations because, if you stop to think about it, these extensions are added or created for a client to support their business logic, they are going to be the modules that they mostly use.

Custom fields

Following the logic of the last paragraph, if custom modules need to be optimized, custom fields need that even more. If someone adds a custom field, the most normal scenario will be because it is an important field in their business logic, and we start creating filters and reports on those fields. They will most certainly require indexes.

Let me share two of the slow queries that appeared.

Slow custom field query 1

SELECT count(*) AS count
FROM vtiger_contactdetails ...
WHERE vtiger_accountscfaccount_id.cf_569 LIKE '%Aliments Dietetiques%'
   or vtiger_accountscfaccount_id.cf_570 LIKE '%Aliments Dietetiques%'
   or vtiger_accountscfaccount_id.cf_571 LIKE '%Aliments Dietetiques%'
   or vtiger_accountscfaccount_id.cf_683 LIKE '%Aliments Dietetiques%'`

Recommendation: This type of search (LIKE '%string%') is a classic example of a task for which a standard index is not suitable. To solve this, you need to use a specialized indexing approach for text-based searches.

Add a FULLTEXT Index: The most effective solution is to add a FULLTEXT index on the columns involved in your search (cf_569, cf_570, cf_571, and cf_683). A FULLTEXT index is specifically designed to handle this kind of pattern and will dramatically speed up your query.

ALTER TABLE vtiger_accountscf ADD FULLTEXT INDEX `idx_fulltext_cf` (`cf_569`, `cf_570`, `cf_571`, `cf_683`);

Slow custom field query 2

SELECT fields
FROM vtiger_activity
 ...
INNER JOIN vtiger_quotes on vtiger_quotes.quoteid = vtiger_activity.cf_2065
 ...
WHERE ...

Recommendation: add an index on cf_2065 to support the join with vtiger_quotes.

Calendar reminder query

During this process, I learned a bunch of tricks, one of the most important ones was that MySQL cannot use indexes when we put a function on a field. It makes sense, but it never dawned on me.

As in the DART example above, we do this in some parts of the filtering code in the application. I had a look but couldn't find any easy wins. Every place we do that seems delicate and error-prone, so we will have to leave it for a better time to invest in that.

One query that came up in the study was optimized: the calendar reminder query. It looked like this

SELECT vtiger_activity_reminder_popup.*,...
WHERE ... and
(
  (DATE_FORMAT(vtiger_activity_reminder_popup.date_start,'%Y-%m-%d') < '$date'
  AND DATE_FORMAT(vtiger_activity_reminder_popup.date_start,'%Y-%m-%d') >= '$date_inpast')
  OR (
  DATE_FORMAT(vtiger_activity_reminder_popup.date_start,'%Y-%m-%d') = '$date'
  AND TIME_FORMAT(vtiger_activity_reminder_popup.time_start,'%H:%i') <= '$time'
  )
)

That DATE_FORMAT() and TIME_FORMAT() make it impossible to use the index, so I changed the SQL to

SELECT vtiger_activity_reminder_popup.*,...
WHERE ... and
(
  (vtiger_activity_reminder_popup.date_start < '$date'
  AND vtiger_activity_reminder_popup.date_start >= '$date_inpast')
  OR (
  vtiger_activity_reminder_popup.date_start = '$date'
  AND vtiger_activity_reminder_popup.time_start <= '$time'
  )
)

Local cache

Three queries appeared many times, not because they were slow but because they were called many times. They are small configuration tables that hold metadata about the installation. Since the tables are small (one had only 7 rows), we don't bother thinking much about them; we add the basic index and move on. It turns out that the information is retrieved so many times that it becomes a pain point. So I added a local cache to the retrieval function. That way, we only read them once. Another of the thousands of microoptimizations we do constantly.

Other recommended indexes

We created a changeset to add over 20 recommended indexes. Some are rather obvious once you see them, others not so much. This isn't going to make the application notably faster, but it will be faster.

• database optimizations
• add isDateString helper function for date fields

• EvolutivoFW Standard Formatting
• Documentation: code and wiki.
  • cron update blocking
• Refactor and Optimize code
  • SonarQube recommendations.
  • eliminate warning in Colorizer
  • add database indexes recommended by MySQL slow queries analysis
  • eliminate unnecessary functions from the calendar query
  • use empty instead of count for array check
  • reduce SQL columns
  • add local cache to configuration tables
• Unit Tests: keeps getting more and more assertions.

• recover Delete Custom Field operation
• skip inactive fields in HelpDesk email content
• pass the correct modulename instead of label to the ApplicatonMenu map
• add skip_merge_on_upsert context variable to Upsert if merge of data is not needed
• Translations
  • Account/Project pt_br

August	September

Thanks for reading.