Very slow query on new custom module

👨‍💻 Development Help
1

Hi, I’m having issues on a new module I created in my suite crm instance.

The module has about 10 fields and a relationship with Opportunities. I populated it with a script, so it has about 10 000 records (same as Opportunities).

SuiteCrm takes is very slow when loading the module’s list view, about 120 seconds, when loading opportunities listview takes only 0.06 seconds.

I tracked slow queries, and I get these :

[FATAL] Slow Query (time:74.79937505722
 SELECT new_module.id , new_module.assigned_user_id  , jt0.name new_module_opportunities_name, jtl0.new_module_opportunitiesopportunities_ida new_module_opportunitiesopportunities_ida, 
 new_module.name , new_module.numero_facture , new_module.amount , new_module.currency_id , new_module.etape , new_module.date_facturation , new_module.date_paiement, 
 jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, new_module.created_by  
 FROM new_module  
 LEFT JOIN new_module_cstm ON new_module.id = new_module_cstm.id_c   
 LEFT JOIN  new_module_opportunities_c jtl0 ON new_module.id=jtl0.trad_etape87d3uration_idb AND jtl0.deleted=0

 LEFT JOIN  opportunities jt0 ON jt0.id=jtl0.new_module_opportunitiesopportunities_ida AND jt0.deleted=0 AND jt0.deleted=0  
 LEFT JOIN  users jt1 ON new_module.assigned_user_id=jt1.id AND jt1.deleted=0 AND jt1.deleted=0 
 where new_module.deleted=0 ORDER BY new_module.date_entered DESC LIMIT 20,21) 

[FATAL] Slow Query (time:51.922508955002
SELECT new_module.*,new_module_cstm.* FROM new_module LEFT JOIN new_module_cstm ON new_module.id = new_module_cstm.id_c WHERE new_module.id = 'e173ba30-3ba1-4323-bfb8-d87754fa122c' AND new_module.deleted=0 LIMIT 0,1)

I’m on SuiteCRM Version 7.14.3, Sugar Version 6.5.25 (Build 344).
Using MariaDB : Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12
Apache 1.4.62
php 8.2.25 with memory limit set to 256M.

I’m wondering if I made mistakes when setting up or populating my new module?

I guess the issue is with my database, so I tried to optimize the tables related to this module, but to no success

MariaDB [suite_crm_2025]> OPTIMIZE TABLE new_module;
+------------------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                                    | Op       | Msg_type | Msg_text                                                          |
+------------------------------------------+----------+----------+-------------------------------------------------------------------+
| suite_crm.new_module | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| suite_crm.new_module | optimize | status   | OK                                                                |
+------------------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0,107 sec)

I think it might be because of the ids I used (uuid4), but the format seems coherent with other tables ids… Does anyone know what kind of ids suitecrm uses ?

I’ll take any kind of help I can get, thanks a lot !

2

Ensure all foreign keys and frequently queried columns are indexed.

CREATE INDEX idx_new_module_assigned_user_id ON new_module (assigned_user_id);
CREATE INDEX idx_new_module_opportunity_id ON new_module_opportunities_c (new_module_id, opportunities_id);
CREATE INDEX idx_opportunities_id ON opportunities (id);

Consider running ANALYZE TABLE to update statistics:

ANALYZE TABLE new_module, new_module_cstm;

PHP Configuration & Memory

memory_limit = 512M

max_execution_time = 300

Restart php service.

3

Thank you for your answer.

I tried what you said :
Created indexes for the main fields of my module, for foreign keys and for the “deleted” field.

I analized all related tables.

And I increased memory-limit and max-execution_time.

I restarted apache2 and mariadb,

It might be a bit quicker, but still more than 100 seconds…
Don’t know what else to do

4

So I deleted from the database some of my “deleted” records on new_module. There was about 3000, that I removed, along with the corresponding records from new_module_opportunities_c pivot table, and new_module_cstm.
I now have 9000 lines in this table, but it still takes a lot of time to load list view (98 seconds on my last try).

Also, to try to analyze my indexes, I ran Explain on my slow query

EXPLAIN SELECT count(*) c FROM new_module  
LEFT JOIN new_module_cstm ON new_module.id = new_module_cstm.id_c   LEFT JOIN  new_module_opportunities_c jtl0 
ON new_module.id=jtl0.new_module_idb AND jtl0.deleted=0
 LEFT JOIN  opportunities jt0 
ON jt0.id=jtl0.new_module_opportunitiesopportunities_ida AND jt0.deleted=0
 AND jt0.deleted=0  
LEFT JOIN  users jt1 ON new_module.assigned_user_id=jt1.id AND jt1.deleted=0
 AND jt1.deleted=0 where new_module.deleted=0;

 +------+-------------+---------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+---------+---------------------------------------------+-------+--------------------------------------------------------+
| id   | select_type | table                     | type  | possible_keys                                                                                                                                                                                              | key                                         | key_len | ref                                         | rows  | Extra                                                  |
+------+-------------+---------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+---------+---------------------------------------------+-------+--------------------------------------------------------+
|    1 | SIMPLE      | new_module                | range | idx_new_module_deleted                                                                                                                                                                                     | idx_new_module_deleted                      | 2       | NULL                                        | 4712  | Using index condition; Using temporary; Using filesort |
|    1 | SIMPLE      | jtl0                      | ref   | new_module_opportunities_alt,idx_new_module_opportunity_id,idx_new_module_opportunity_deleted,idx_new_module_jointure_opportunities_deleted_id                                                             | new_module_opportunities_alt                | 147     | suite_crm_2025.new_module.id                | 1     | Using where                                            |
|    1 | SIMPLE      | jt0                       | ALL   | NULL                                                                                                                                                                                                       | NULL                                        | NULL    | NULL                                        | 14761 | Using where; Using join buffer (flat, BNL join)        |
|    1 | SIMPLE      | jt1                       | ALL   | NULL                                                                                                                                                                                                       | NULL                                        | NULL    | NULL                                        | 62    | Using where; Using join buffer (incremental, BNL join) |
+------+-------------+---------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+---------+---------------------------------------------+-------+--------------------------------------------------------+

From what I understand, I should try to create an index on the third line, on the join between opportunities and new_module.
I have an index on all fields of the pivot table (new_module_opportunities_c), and on the fields id, and deleted from opportunties, but it looks like it doesn’t use them…

Is there something I’m doing wrong ?

If there was a setting to avoid loading the whole list of records, but only the fisrt page (20), it would be a lost faster.

5

Try a higher PHP memory limit, say, 512M

6

I already did… Do you think I should increase it more than that ?

7

Can you see if this query is better

SELECT count(1) c FROM new_module  
JOIN new_module_cstm ON new_module.id = new_module_cstm.id_c   
JOIN  new_module_opportunities_c jtl0 ON new_module.id=jtl0.new_module_idb AND jtl0.deleted=0
JOIN  opportunities jt0 ON jt0.id=jtl0.new_module_opportunitiesopportunities_ida AND jt0.deleted=0 AND jt0.deleted=0  
JOIN  users jt1 ON new_module.assigned_user_id=jt1.id AND jt1.deleted=0
where new_module.deleted=0;
8

Also I found out two things :
First, the ORDER BY is single-handedly increasing execution time (less than 0.01 sec without it), which could be expected, but changing the ordering field does not change anything.

Second thing is, on my test instance, with the same database but on my local computer, I can load my new_module 's list page in 0.34 seconds. I’m kind of lost at that, because it should be a lot slower…

9

This should not have been generated. :slight_smile:

10

Yes its a lot better (only 2.6 seconds)

This should not have been generated. :slight_smile:

And yes I was wondering about that too
Any ida what might cause it ?

There are a few other weird things on this module : in the relationship table, the id names are “new_module_opportunitiesopportunities_ida” and “new87d3ule”, as if there had been some kind of bug when creating the table ?

11

Does it show anything after Quick Repair & Rebuild’ page for SQL changes or error etc.

12

No, it only lists audit tables that are ignored, then “done” and " Database tables are synced with vardefs"

13

Can you please check the result of the following queries to know the memory usage by the mariadb.

  1. SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
  2. SHOW VARIABLES LIKE 'join_buffer_size';

Try increasing a little more for these if that helps

14 
MariaDB [suite_crm_2025]> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0,000 sec)

MariaDB [suite_crm_2025]> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0,000 sec)

So I increased them a bit (to 2G & 32M), but execution time is still the same

15

Thanks for letting us know the result. I thought increasing these two parameters value and restarting the mariadb service would help. Looks like there is something that needs a thorough debugging.

16

Well thanks for your help, I’ll keep you updated

1 Like