Database server:
Web server:
CPU usage at 10-15% and Memory usage at 30-40%
I am hoping to receive some guidance on how to improve or optimize a specific query. In parallel to the select queries I have a dozen load data queries that run every 10 and 30 seconds for real time display board. All the other select queries seem to run fine and stay under 2 seconds except for this one query which is okay for a while then I start to see it delay over time then eventually timeout completely.
SELECT
*
FROM
`sfdc_chat_csat`
LEFT JOIN
`sfdc_chat_review`
ON `sfdc_chat_csat`.`ChatKey__c` = `sfdc_chat_review`.`ChatKey`
WHERE
`sfdc_chat_csat`.`ChatRating__c` != ''
AND `sfdc_chat_csat`.`ChatRating__c` <= 6
AND `sfdc_chat_review`.`manager_review` IS NULL
AND
(
`sfdc_chat_csat`.`Id` IN
(
'a0b4z00000W48UyAAJ',
'a0b4z00000W48V8AAJ',
'a0b4z00000W4CGvAAN',
'a0b4z00000W4CMAAA3',
'a0b4z00000W4CRjAAN',
'a0b4z00000W4CUTAA3',
'a0b4z00000W4CW5AAN',
'a0b4z00000W4DAoAAN',
'a0b4z00000W4CEpAAN',
'a0b4z00000W4CTaAAN'
)
)
AND MONTH(`CreatedDate`) >= MONTH(now());
Here are the results from EXPLAIN query
+-----+-------------+-------------------+----------+-----------------------+---------+
| id | select_type | table | type | possible_keys | key |
+-----+-------------+-------------------+----------+-----------------------+---------+
| 1 | SIMPLE | sfdc_chat_csat | range | PRIMARY,ChatRating__c | PRIMARY |
| 1 | SIMPLE | sfdc_chat_review | eq_ref | PRIMARY | PRIMARY |
+-----+-------------+-------------------+----------+-----------------------+---------+
continued
+---------+--------------------------------------+------+-------------------------+
| key_len | ref | rows | Extra |
+---------+--------------------------------------+------+-------------------------+
| 122 | NULL | 10 | Using where |
| 122 | dashboard.sfdc_chat_csat.ChatKey__c | 1 | Using where; Not exists |
+---------+--------------------------------------+------+-------------------------+
Here is the table structure for table sfdc_chat_csat (10k rows of data)
+---------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| Id | varchar(40) | NO | PRI | NULL | |
| IsDeleted | tinyint(1) | NO | | NULL | |
| ChatKey__c | varchar(40) | NO | UNI | NULL | |
| ChatRating__c | varchar(40) | NO | MUL | NULL | |
| Chat_ButtonId | varchar(40) | NO | MUL | NULL | |
| Chat_Button__c | varchar(40) | NO | | NULL | |
| Chat_Transcript_Number__c | varchar(40) | NO | | NULL | |
| Chat_Transcript_Owner__c | varchar(40) | NO | | NULL | |
| Comments_del__c | varchar(255) | NO | | NULL | |
| CreatedDate | datetime | NO | MUL | NULL | |
| Customer_Email_Address__c | varchar(40) | NO | | NULL | |
| Customer_First_Name__c | varchar(40) | NO | | NULL | |
| Customer_Last_Name__c | varchar(40) | NO | | NULL | |
| NPS_Score__c | varchar(40) | NO | | NULL | |
+---------------------------+--------------+------+-----+---------+-------+
Here is the table structure for sfdc_chat_review (100 rows of data)
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| ChatKey | varchar(40) | NO | PRI | NULL | |
| chatTranscriptId | varchar(30) | NO | | NULL | |
| manager | varchar(30) | NO | | NULL | |
| manager_review | tinyint(1) | NO | MUL | NULL | |
| manager_verdict | varchar(30) | NO | | NULL | |
| false_reason_code | varchar(30) | NO | | NULL | |
| manager_comments | varchar(500) | NO | | NULL | |
+-------------------+--------------+------+-----+---------+-------+
When I run SHOW FULL PROCESSLIST; i see multiple instances of this query in a "Sending data" state. It seems when the Time reaches 20 (20 seconds?) then they drop off the Process list.
Not sure how i should proceed further.
There's a good chance this is causing performance problems:
MONTH(`CreatedDate`) >= MONTH(now())
because MONTH() will be executed for every row and an index on CreatedDate will not be used.
Instead, put a condition on the raw value of CreatedDate that is logically equivalent:
CreatedDate >= DATE_SUB(now(), INTERVAL DAYOFMONTH(now())-1 DAY)
Create an index on CreatedDate.
You may find that using BETWEEN gives better performance than >=:
CreatedDate BETWEEN DATE_SUB(now(), INTERVAL DAYOFMONTH(now())-1 DAY) AND now()
because although it's logically the same (there will not be data for future times), the optimiser will see it as a closed range rather than an open-ended range.