Menu
SuiteCRM dedicated support by SalesAgility
Welcome, Guest
Username: Password: Remember me

TOPIC: Contact Detail View takes long time to load when Cases subpanel is enabled - Works fine when hidden

Contact Detail View takes long time to load when Cases subpanel is enabled - Works fine when hidden 5 days 16 hours ago #74125

  • nishjoseph
  • nishjoseph's Avatar
  • Offline
  • New Member
  • Posts: 2
  • Karma: 0
The detail view takes a good few seconds in the order 15-20 seconds to load when the cases subpanel is enables. When we hide the subpanel under Admin -> Display Modules and Subpanels, the detail view now loads near instantly.

We have a large number of cases and notes associated.

Investigating the log files by isolating to just one use and one record it appears that when the cases subpanel is enabled there are a very large number of queries in the form
Query:SELECT id FROM notes WHERE notes.parent_id = '' AND notes.deleted=0 AND notes.parent_type = 'Cases'
Query Execution Time:0.0037789344787598
Query:SELECT notes.*,notes_cstm.* FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  WHERE notes.id = '10d508d0-58dc-dfb6-64dc-5ad0b87de780' AND notes.deleted=0 LIMIT 0,1
Query Execution Time:0.00026106834411621
Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943'
Query Execution Time:0.00019383430480957

There are more queries and they go to a very large number, none of these queries are executed when the subpanel is hidden.

Whats going on here, how is notes associated to the view of the subpanel for cases??

Edit 1:
It appears that the following pattern of query is repeated for possibly every note we have define
[INFO] Query:SELECT notes.*,notes_cstm.* FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  WHERE notes.id = '77590914-e371-3b6b-be92-5ad0b78a2c1c' AND notes.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00022315979003906
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='ed6b0688-54bf-3769-bc06-5ad0aab95e73'
[INFO] Query Execution Time:0.00015878677368164
[INFO] Query:SELECT ea.email_address FROM email_addresses ea                 LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE ear.bean_module = 'Contacts'                 AND ear.bean_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73'                 AND ear.deleted = 0                 AND ea.invalid_email = 0                 ORDER BY ear.primary_address DESC LIMIT 0,1
[INFO] Query Execution Time:0.0002601146697998
[INFO] Query:SELECT contacts.*,contacts_cstm.* FROM contacts  LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c  WHERE contacts.id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND contacts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00026893615722656
[INFO] Query:SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name 		from contacts 		left join accounts_contacts a_c on a_c.contact_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' and a_c.deleted=0 		left join accounts acc on a_c.account_id = acc.id and acc.deleted=0 		left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id 		where contacts.id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' ORDER BY a_c.date_modified DESC
[INFO] Query Execution Time:0.00038790702819824
[INFO] Query:SELECT user_id id  FROM contacts_users  WHERE contacts_users.contact_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND contacts_users.deleted=0
[INFO] Query Execution Time:0.00016093254089355
[INFO] Query:SELECT cm1_department_contacts_1cm1_department_ida id  FROM cm1_department_contacts_1_c  WHERE cm1_department_contacts_1_c.cm1_department_contacts_1contacts_idb = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73' AND cm1_department_contacts_1_c.deleted=0
[INFO] Query Execution Time:0.00016093254089355
[INFO] Query:SELECT                      ea.email_address,                     ea.email_address_caps,                     ea.invalid_email,                     ea.opt_out,                     ea.confirm_opt_in,                     ea.date_created,                     ea.date_modified,                     ear.id,                     ear.email_address_id,                     ear.bean_id,                     ear.bean_module,                     ear.primary_address,                     ear.reply_to_address,                     ear.deleted                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE                      ear.bean_module = 'Contacts'                     AND ear.bean_id = 'ed6b0688-54bf-3769-bc06-5ad0aab95e73'                     AND ear.deleted = 0                 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:0.00025796890258789
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) as actionee_c , users.created_by owner FROM users WHERE deleted=0 AND id='6b8d8c3d-f257-d14a-ce5d-5ad0a28a4f69'
[INFO] Query Execution Time:4.0054321289062E-5
Last Edit: 5 days 16 hours ago by nishjoseph.
The administrator has disabled public write access.

Contact Detail View takes long time to load when Cases subpanel is enabled - Works fine when hidden 5 days 15 hours ago #74131

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 8246
  • Thank you received: 1265
  • Karma: 298
You probably could use some database maintenance, and clearing up unused data.

A few questions:

1. Which version of SuiteCRM is this?

2. Do you have the Prune database Scheduler job enabled? When did it "last ran successfully"?

3. Which columns do you have in the subpanel, exactly? You seem to be querying 3 other modules (Contacts, Accounts, Email addresses, and a custom cm1_department_contacts_1_c).

4. What are your results when running the query at the top of this post?

pgorod.github.io/Database-tables-size/

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Contact Detail View takes long time to load when Cases subpanel is enabled - Works fine when hidden 5 days 3 hours ago #74143

  • nishjoseph
  • nishjoseph's Avatar
  • Offline
  • New Member
  • Posts: 2
  • Karma: 0
You probably could use some database maintenance, and clearing up unused data.
We migrated from an in house setup, we unfortunately need to maintain 15+ years of data. There are only a few deleted records as the ability to delete is significantly restricted.
1. Which version of SuiteCRM is this?

We are now at 7.10.10
2. Do you have the Prune database Scheduler job enabled? When did it "last ran successfully"?
No, this is disabled.
3. Which columns do you have in the subpanel, exactly? You seem to be querying 3 other modules (Contacts, Accounts, Email addresses, and a custom cm1_department_contacts_1_c).
The cases subpanel displays the following information.
| Case Num. 	| Subject  |	Account Name |	Status |	Date Created |	Assigned User | 
4. What are your results when running the query at the top of this post?
  • The first returns a list of note ids that contains 1057 rows.
  • The second return a single note for the first result from (1)
  • The third returns the contact information of the note's contact
  • This is followed by email address information of the contact
  • This is followed by contact account information
  • This is followed by contact account user id (i am assuming portal id) as this is always an Empty set
  • This is followed by contact's department (this is a custom module) relation
  • If department is not empty, retrieve departments related account information
  • If department is not empty, retrieve related account email address
  • Retrieve notes modified used information in a series of queries (form users and email table)
  • Repeat the process for the next note id that was obtained from the first querry

Please note the above description was obtained by trying out each query from two types of patterns. One that, what appears to be, a dependency on the notes's contacts association to a department.

Example pattern if a contact has a department:
[INFO] Query:SELECT notes.*,notes_cstm.* FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  WHERE notes.id = '10d508d0-58dc-dfb6-64dc-5ad0b87de780' AND notes.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00026106834411621
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943'
[INFO] Query Execution Time:0.00019383430480957
[INFO] Query:SELECT ea.email_address FROM email_addresses ea                 LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE ear.bean_module = 'Contacts'                 AND ear.bean_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943'                 AND ear.deleted = 0                 AND ea.invalid_email = 0                 ORDER BY ear.primary_address DESC LIMIT 0,1
[INFO] Query Execution Time:0.00029706954956055
[INFO] Query:SELECT contacts.*,contacts_cstm.* FROM contacts  LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c  WHERE contacts.id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND contacts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00026416778564453
[INFO] Query:SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name 		from contacts 		left join accounts_contacts a_c on a_c.contact_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' and a_c.deleted=0 		left join accounts acc on a_c.account_id = acc.id and acc.deleted=0 		left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id 		where contacts.id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' ORDER BY a_c.date_modified DESC
[INFO] Query Execution Time:0.0004119873046875
[INFO] Query:SELECT user_id id  FROM contacts_users  WHERE contacts_users.contact_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND contacts_users.deleted=0
[INFO] Query Execution Time:0.00015807151794434
[INFO] Query:SELECT cm1_department_contacts_1cm1_department_ida id  FROM cm1_department_contacts_1_c  WHERE cm1_department_contacts_1_c.cm1_department_contacts_1contacts_idb = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943' AND cm1_department_contacts_1_c.deleted=0
[INFO] Query Execution Time:0.00019407272338867
[INFO] Query:SELECT cm1_department.*,cm1_department_cstm.* FROM cm1_department  LEFT JOIN cm1_department_cstm ON cm1_department.id = cm1_department_cstm.id_c  WHERE cm1_department.id = '91d1ff8c-acbf-9f55-a41f-5ad0a73b4600' AND cm1_department.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.2928924560547E-5
[INFO] Query:SELECT accounts_cm1_department_1accounts_ida id  FROM accounts_cm1_department_1_c  WHERE accounts_cm1_department_1_c.accounts_cm1_department_1cm1_department_idb = '91d1ff8c-acbf-9f55-a41f-5ad0a73b4600' AND accounts_cm1_department_1_c.deleted=0
[INFO] Query Execution Time:5.1021575927734E-5
[INFO] Query:SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE accounts.id = '363922dd-8835-e368-4a71-5ad0a32f5229' AND accounts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:6.413459777832E-5
[INFO] Query:SELECT a1.name from accounts a1, accounts a2 where a1.id = a2.parent_id and a2.id = '363922dd-8835-e368-4a71-5ad0a32f5229' and a1.deleted=0
[INFO] Query Execution Time:4.1961669921875E-5
[INFO] Query:SELECT                      ea.email_address,                     ea.email_address_caps,                     ea.invalid_email,                     ea.opt_out,                     ea.confirm_opt_in,                     ea.date_created,                     ea.date_modified,                     ear.id,                     ear.email_address_id,                     ear.bean_id,                     ear.bean_module,                     ear.primary_address,                     ear.reply_to_address,                     ear.deleted                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE                      ear.bean_module = 'Accounts'                     AND ear.bean_id = '363922dd-8835-e368-4a71-5ad0a32f5229'                     AND ear.deleted = 0                 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:5.1021575927734E-5
[INFO] Query:SELECT id ,accounts.name as accounts_cm1_department_1_name , accounts.assigned_user_id owner FROM accounts WHERE deleted=0 AND id='363922dd-8835-e368-4a71-5ad0a32f5229'
[INFO] Query Execution Time:5.6028366088867E-5
[INFO] Query:SELECT id ,cm1_department.name as cm1_department_contacts_1_name , cm1_department.assigned_user_id owner FROM cm1_department WHERE deleted=0 AND id='91d1ff8c-acbf-9f55-a41f-5ad0a73b4600'
[INFO] Query Execution Time:3.814697265625E-5
[INFO] Query:SELECT                      ea.email_address,                     ea.email_address_caps,                     ea.invalid_email,                     ea.opt_out,                     ea.confirm_opt_in,                     ea.date_created,                     ea.date_modified,                     ear.id,                     ear.email_address_id,                     ear.bean_id,                     ear.bean_module,                     ear.primary_address,                     ear.reply_to_address,                     ear.deleted                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE                      ear.bean_module = 'Contacts'                     AND ear.bean_id = '11aec7bb-8cfc-bbed-8a5c-5ad0aa14f943'                     AND ear.deleted = 0                 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:0.00030398368835449
[INFO] Query:SELECT users.*,users_cstm.* FROM users  LEFT JOIN users_cstm ON users.id = users_cstm.id_c  WHERE users.id = '74d46d87-fa51-33d2-f1b0-5ad0a276eb5c' AND users.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.9843063354492E-5
[INFO] Query:SELECT u1.first_name, u1.last_name from users  u1, users  u2 where u1.id = u2.reports_to_id AND u2.id = '74d46d87-fa51-33d2-f1b0-5ad0a276eb5c' and u1.deleted=0
[INFO] Query Execution Time:4.1007995605469E-5
[INFO] Query:SELECT                      ea.email_address,                     ea.email_address_caps,                     ea.invalid_email,                     ea.opt_out,                     ea.confirm_opt_in,                     ea.date_created,                     ea.date_modified,                     ear.id,                     ear.email_address_id,                     ear.bean_id,                     ear.bean_module,                     ear.primary_address,                     ear.reply_to_address,                     ear.deleted                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE                      ear.bean_module = 'Users'                     AND ear.bean_id = '74d46d87-fa51-33d2-f1b0-5ad0a276eb5c'                     AND ear.deleted = 0                 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:4.887580871582E-5
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) as actionee_c , users.created_by owner FROM users WHERE deleted=0 AND id='74d46d87-fa51-33d2-f1b0-5ad0a276eb5c'
[INFO] Query Execution Time:4.1961669921875E-5

Example where notes' contact does not have a department associated
[INFO] Query:SELECT notes.*,notes_cstm.* FROM notes  LEFT JOIN notes_cstm ON notes.id = notes_cstm.id_c  WHERE notes.id = '11350d4f-ab53-2267-c8a3-5ad0b81a2c66' AND notes.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:0.00018906593322754
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as contact_name ,contacts.phone_work as contact_phone , contacts.assigned_user_id owner FROM contacts WHERE deleted=0 AND id='d789902d-292f-a0eb-77cc-5ad0aa992276'
[INFO] Query Execution Time:4.4822692871094E-5
[INFO] Query:SELECT ea.email_address FROM email_addresses ea                 LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE ear.bean_module = 'Contacts'                 AND ear.bean_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276'                 AND ear.deleted = 0                 AND ea.invalid_email = 0                 ORDER BY ear.primary_address DESC LIMIT 0,1
[INFO] Query Execution Time:3.7908554077148E-5
[INFO] Query:SELECT contacts.*,contacts_cstm.* FROM contacts  LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c  WHERE contacts.id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND contacts.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.6028366088867E-5
[INFO] Query:SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name 		from contacts 		left join accounts_contacts a_c on a_c.contact_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' and a_c.deleted=0 		left join accounts acc on a_c.account_id = acc.id and acc.deleted=0 		left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id 		where contacts.id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' ORDER BY a_c.date_modified DESC
[INFO] Query Execution Time:5.3882598876953E-5
[INFO] Query:SELECT user_id id  FROM contacts_users  WHERE contacts_users.contact_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND contacts_users.deleted=0
[INFO] Query Execution Time:4.0054321289062E-5
[INFO] Query:SELECT cm1_department_contacts_1cm1_department_ida id  FROM cm1_department_contacts_1_c  WHERE cm1_department_contacts_1_c.cm1_department_contacts_1contacts_idb = 'd789902d-292f-a0eb-77cc-5ad0aa992276' AND cm1_department_contacts_1_c.deleted=0
[INFO] Query Execution Time:6.8902969360352E-5
[INFO] Query:SELECT                      ea.email_address,                     ea.email_address_caps,                     ea.invalid_email,                     ea.opt_out,                     ea.confirm_opt_in,                     ea.date_created,                     ea.date_modified,                     ear.id,                     ear.email_address_id,                     ear.bean_id,                     ear.bean_module,                     ear.primary_address,                     ear.reply_to_address,                     ear.deleted                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE                      ear.bean_module = 'Contacts'                     AND ear.bean_id = 'd789902d-292f-a0eb-77cc-5ad0aa992276'                     AND ear.deleted = 0                 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:4.4107437133789E-5
[INFO] Query:SELECT users.*,users_cstm.* FROM users  LEFT JOIN users_cstm ON users.id = users_cstm.id_c  WHERE users.id = '75031e6d-aefe-067c-8e1e-5ad0a212fe63' AND users.deleted=0 LIMIT 0,1
[INFO] Query Execution Time:5.1021575927734E-5
[INFO] Query:SELECT u1.first_name, u1.last_name from users  u1, users  u2 where u1.id = u2.reports_to_id AND u2.id = '75031e6d-aefe-067c-8e1e-5ad0a212fe63' and u1.deleted=0
[INFO] Query Execution Time:3.8862228393555E-5
[INFO] Query:SELECT                      ea.email_address,                     ea.email_address_caps,                     ea.invalid_email,                     ea.opt_out,                     ea.confirm_opt_in,                     ea.date_created,                     ea.date_modified,                     ear.id,                     ear.email_address_id,                     ear.bean_id,                     ear.bean_module,                     ear.primary_address,                     ear.reply_to_address,                     ear.deleted                 FROM email_addresses ea LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id                 WHERE                      ear.bean_module = 'Users'                     AND ear.bean_id = '75031e6d-aefe-067c-8e1e-5ad0a212fe63'                     AND ear.deleted = 0                 ORDER BY ear.reply_to_address, ear.primary_address DESC
[INFO] Query Execution Time:4.6014785766602E-5
[INFO] Query:SELECT id ,LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,'')))) as actionee_c , users.created_by owner FROM users WHERE deleted=0 AND id='75031e6d-aefe-067c-8e1e-5ad0a212fe63'
[INFO] Query Execution Time:3.7908554077148E-5

I can confirm that the pattern is repeated for all 1057 notes. None of that information is actually needed.

I tried editing the Cases module subpanel/default.php to only show case number thinking that the assigned to and account is somehow triggering all those queries. Tt does not have any affect and these, for this purposes is unnecessary, queries are being executed.

The history panel does not seem to be affected in anyway and the notes entries there are not affected irrespective of the having that Cases subpanel enabled or hidden.
Last Edit: 5 days 3 hours ago by nishjoseph. Reason: Typos.
The administrator has disabled public write access.

Contact Detail View takes long time to load when Cases subpanel is enabled - Works fine when hidden 4 days 14 hours ago #74163

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 8246
  • Thank you received: 1265
  • Karma: 298
4. What are your results when running the query at the top of this post?

pgorod.github.io/Database-tables-size/

You were supposed to go get an SQL query from that link and execute it, I don't think you saw that :-)

If you don't "prune" regularly and automatically, you should prune occasionally and manually. You can start by running queries on all your larger tables like this:
SELECT YEAR(date_modified), COUNT(*) FROM `leads`
WHERE deleted=1
GROUP BY YEAR(date_modified)

You can then turn those into DELETE queries, deleting everything, or everything except the current month, for example.

Then you should examine orphaned records. For example, when you delete a Contact, there could still be an entry for it in contacts_cstm, or security_groups_records, for example.

Then clear up tracking tables like job_queue or aow_processed. These cleanups require some care - delete only finished jobs, and delete only AOW information that is not relevant to any Workflows you are using which have "Repeated runs" unchecked (these need to keep track of past actions in order to function well).

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Contact Detail View takes long time to load when Cases subpanel is enabled - Works fine when hidden 4 days 14 hours ago #74164

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 8246
  • Thank you received: 1265
  • Karma: 298
Here's some more SQL scraps I had stored here somewhere, these will save you some time

Delete orphaned records from contacts_cstm:
SELECT * -- DELETE ChildTable
FROM contacts_cstm ChildTable    
LEFT JOIN contacts ParentTable 
ON ChildTable.id_c = ParentTable.id
WHERE ParentTable.id IS NULL

Delete orphaned records from securitygroups_records:
SELECT record_id, module, s.deleted, c.last_name, c.deleted -- DELETE
FROM securitygroups_records s    
LEFT JOIN contacts c 
ON s.record_id = c.id
WHERE c.id IS NULL  
AND s.module='Contacts' 

These are phrased as SELECTS, but if you delete the SELECT keyword and use the DELETE that is after "--" (which marks an SQL comment), you will delete rows.

*** --> USE CAREFULLY, AT YOUR OWN RISK, AND BACKUP ENTIRE DATABASE FIRST <-- ***

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.
Time to create page: 0.094 seconds
Powered by Kunena Forum