Optimize queries

[livewire]

Hi

Is there a way to optimize the below queries? i have been getting frequent server time outs. They said te below queries are taking more time which increases the cpu usage and runs out of memory. Im on a VPS with the below config

• 6 CPU Virtual Cores
• 16 GB ECC RAM
• 320 GB SSD Space
• 8 TB Bandwidth

# Time: 2023-05-07T01:08:34.082619Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1543551
# Query_time: 276.817710 Lock_time: 0.000007 Rows_sent: 0 Rows_examined: 2263723
SET timestamp=1683421437;
DELETE FROM `action_history_record` WHERE DATE(created_at) < '2023-04-22';
# Time: 2023-05-07T01:28:33.926389Z
use espo_crm;
SET timestamp=1683423476;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `note`;
# Time: 2023-05-07T01:38:04.646189Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557506
# Query_time: 305.196827 Lock_time: 0.005293 Rows_sent: 0 Rows_examined: 0
use espo_crm;
SET timestamp=1683461897;
SELECT notification.id AS `id`, notification.deleted AS `deleted`, notification.number AS `number`, notification.data AS `data`, notification.type AS `type`, notification.read AS `read`, notification.email_is_processed AS `emailIsProcessed`, notification.created_at AS `createdAt`, notification.message AS `message`, notification.user_id AS `userId`, NULLIF(TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))), '') AS `userName`, notification.related_id AS `relatedId`, notification.related_type AS `relatedType`, notification.related_parent_id AS `relatedParentId`, notification.related_parent_type AS `relatedParentType` FROM `notification` LEFT JOIN `user` AS `user` ON notification.user_id = user.id JOIN note ON notification.related_id = note.id WHERE notification.type = 'Note' AND notification.related_type = 'Note' AND note.type IN ('Post','Status','EmailReceived') AND (((notification.related_parent_type IN ('Case')) OR (notification.related_parent_type IS NULL))) AND user.type = 'portal' AND (notification.created_at > '2023-05-07 07:18:17' AND notification.read = '' AND notification.email_is_processed = '' AND (notification.created_at < '2023-05-07 12:17:47')) AND notification.deleted = '0' ORDER BY number LIMIT 0, 200;
# Time: 2023-05-07T12:23:33.507764Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557506
# Query_time: 9.004160 Lock_time: 0.353089 Rows_sent: 0 Rows_examined: 0
SET timestamp=1683462204;
INSERT INTO `scheduled_job_log_record` (`id`, `name`, `deleted`, `status`, `execution_time`, `created_at`, `scheduled_job_id`, `target_id`, `target_type`) VALUES ('6457983c56dd051f6', 'Send Email Notifications', '0', 'Success', '2023-05-07 12:23:23', '2023-05-07 12:23:24', '5f6868656100dabcc', NULL, NULL);
# Time: 2023-05-07T12:23:43.778621Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557506
# Query_time: 194.672569 Lock_time: 5.047832 Rows_sent: 0 Rows_examined: 0
use espo_crm;
SET timestamp=1683462239;
LOCK TABLES `job` WRITE;
# Time: 2023-05-07T12:29:48.783078Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557506
# Query_time: 119.754330 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1683462469;
SELECT job.id AS `id` FROM `job` WHERE job.id = '64579591d16279dd7' AND job.status = 'Pending' AND job.deleted = '0' LIMIT 0, 1;
# Time: 2023-05-07T12:31:51.698311Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557506
# Query_time: 87.755534 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1683462623;
SELECT job.id AS `id` FROM `job` WHERE job.scheduled_job_id = '5f686865620698357' AND job.status IN ('Running','Ready') AND job.deleted = '0' LIMIT 0, 1;
# Time: 2023-05-07T12:32:09.429514Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557572
# Query_time: 152.897545 Lock_time: 1.017760 Rows_sent: 1 Rows_examined: 11
use espo_crm;
SET timestamp=1683464257;
SELECT COUNT(job.id) AS AggregateValue FROM `job` WHERE job.scheduled_job_id = '5f6867ff81cf8e13c' AND job.status = 'Pending' AND job.deleted = '0';
# Time: 2023-05-07T13:00:28.247985Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557571
# Query_time: 170.986130 Lock_time: 1.017668 Rows_sent: 1 Rows_examined: 11
SET timestamp=1683464257;
SELECT COUNT(job.id) AS AggregateValue FROM `job` WHERE job.scheduled_job_id = '5f6867ff81cf8e13c' AND job.status = 'Pending' AND job.deleted = '0';
# Time: 2023-05-07T13:01:34.238181Z
# User@Host: espo_user[espo_user] @ localhost [] Id: 1557609
# Query_time: 19.802991 Lock_time: 0.008910 Rows_sent: 1 Rows_examined: 0
use espo_crm;
SET timestamp=1683465248;
SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) AS `name`, user.deleted AS `deleted`, user.is_admin AS `isAdmin`, user.user_name AS `userName`, user.type AS `type`, user.password AS `password`, user.auth_method AS `authMethod`, user.api_key AS `apiKey`, user.salutation_name AS `salutationName`, user.first_name AS `firstName`, user.last_name AS `lastName`, user.is_active AS `isActive`, user.is_portal_user AS `isPortalUser`, user.is_super_admin AS `isSuperAdmin`, user.title AS `title`, emailAddresses.name AS `emailAddress`, phoneNumbers.name AS `phoneNumber`, user.gender​

what would be the best way to optimize the above queries to reduce the execution time.

Thanks

[yuri]

SELECT job.id AS `id` FROM `job` WHERE job.id = '64579591d16279dd7' AND job.status = 'Pending' AND job.deleted = '0' LIMIT 0, 1;

This one can't be optimized more as ID is a primary key. If this executes slow might be database config tweaking is needed. Make sure cleanup job deletes old jobs. You can decrease cleanup period for jobs.

[livewire]

can u let me know database tweaks i should do please?

[yuri]

Check https://docs.espocrm.com/administrat.../#mysql-params

As your database is pretty big, I recommend to do some research on the database tweaking topic.