Mixing
Need to tune a mysql query
I have a query which is taking more than a day to complete with 1700128 records in the temp_message_split table, so please help in getting this tunned
Create table statement and explain plan has been provided below.
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND nullif(t1.dh_member_id,'') IS NOT NULL;
Here is create table DDL
CREATE TABLE
temp_message_split
(
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
DOB VARCHAR(10),
EMPLOYEE_ID VARCHAR(20),
CES_CUST_NUM VARCHAR(7),
MED_POLICY_NUM VARCHAR(20),
EMAIL_ADDR VARCHAR(50),
DH_MEMBER_ID VARCHAR(9),
ALT_ID VARCHAR(20),
DRSN VARCHAR(2),
SSN VARCHAR(9),
EPIPHANY_MEMBER_ID VARCHAR(18),
PORTAL_ADDRESS VARCHAR(30),
STATEMENT_VENDOR VARCHAR(20),
CONTENT_KEY VARCHAR(18),
EPIPHANY_COMMUNICATION_ID VARCHAR(200),
PRIORITY VARCHAR(4),
DAYS_UNTIL_EXPIRED VARCHAR(4),
CONTENT_DTL_KEY VARCHAR(18),
STATUS VARCHAR(1),
ACTIVATION_MEMBER_KEY bigint,
MESSAGE_BOARD_KEY bigint,
PORTAL_STATEMENT_LOC_KEY bigint,
temp_message_split_KEY bigint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (temp_message_split_KEY),
INDEX EPIPHANY_COMMUNICATION_ID_IDX (EPIPHANY_COMMUNICATION_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX2 (ALT_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX3 (DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX4 (ALT_ID, DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX1 (DH_MEMBER_ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here is its explain plan:
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| 1 | SIMPLE | t2 | NULL | index | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | TEMP_MESSAGE_SPLIT_IDX1 | 30 | NULL | 1619639 | 100.00 | Using index |
| 1 | UPDATE | t1 | NULL | ALL | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | NULL | NULL | NULL | 1619639 | 33.33 | Range checked for each record (index map: 0x5) |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
2 rows in set (0.00 sec)
This query is taking more than a day for processing 1700128 in the temp_message_split table and we need to tune it in such a way that it will take as much min. time as possible.
mysql sql query-optimization mysql-5.7
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
asked Jan 2 at 13:13
ParulParul
24
add a comment |
I have a query which is taking more than a day to complete with 1700128 records in the temp_message_split table, so please help in getting this tunned
Create table statement and explain plan has been provided below.
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND nullif(t1.dh_member_id,'') IS NOT NULL;
Here is create table DDL
CREATE TABLE
temp_message_split
(
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
DOB VARCHAR(10),
EMPLOYEE_ID VARCHAR(20),
CES_CUST_NUM VARCHAR(7),
MED_POLICY_NUM VARCHAR(20),
EMAIL_ADDR VARCHAR(50),
DH_MEMBER_ID VARCHAR(9),
ALT_ID VARCHAR(20),
DRSN VARCHAR(2),
SSN VARCHAR(9),
EPIPHANY_MEMBER_ID VARCHAR(18),
PORTAL_ADDRESS VARCHAR(30),
STATEMENT_VENDOR VARCHAR(20),
CONTENT_KEY VARCHAR(18),
EPIPHANY_COMMUNICATION_ID VARCHAR(200),
PRIORITY VARCHAR(4),
DAYS_UNTIL_EXPIRED VARCHAR(4),
CONTENT_DTL_KEY VARCHAR(18),
STATUS VARCHAR(1),
ACTIVATION_MEMBER_KEY bigint,
MESSAGE_BOARD_KEY bigint,
PORTAL_STATEMENT_LOC_KEY bigint,
temp_message_split_KEY bigint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (temp_message_split_KEY),
INDEX EPIPHANY_COMMUNICATION_ID_IDX (EPIPHANY_COMMUNICATION_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX2 (ALT_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX3 (DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX4 (ALT_ID, DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX1 (DH_MEMBER_ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here is its explain plan:
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| 1 | SIMPLE | t2 | NULL | index | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | TEMP_MESSAGE_SPLIT_IDX1 | 30 | NULL | 1619639 | 100.00 | Using index |
| 1 | UPDATE | t1 | NULL | ALL | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | NULL | NULL | NULL | 1619639 | 33.33 | Range checked for each record (index map: 0x5) |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
2 rows in set (0.00 sec)
This query is taking more than a day for processing 1700128 in the temp_message_split table and we need to tune it in such a way that it will take as much min. time as possible.
mysql sql query-optimization mysql-5.7
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
asked Jan 2 at 13:13
ParulParul
24
1
Please explain what the query is supposed to be doing. The logic is not clear.
– Gordon Linoff
Jan 2 at 13:18
This query is meant for marking all duplicate records as "D" leaving one unique record with unique DH_MEMBER_ID
– Parul
Jan 2 at 13:29
add a comment |
I have a query which is taking more than a day to complete with 1700128 records in the temp_message_split table, so please help in getting this tunned
Create table statement and explain plan has been provided below.
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND nullif(t1.dh_member_id,'') IS NOT NULL;
Here is create table DDL
CREATE TABLE
temp_message_split
(
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
DOB VARCHAR(10),
EMPLOYEE_ID VARCHAR(20),
CES_CUST_NUM VARCHAR(7),
MED_POLICY_NUM VARCHAR(20),
EMAIL_ADDR VARCHAR(50),
DH_MEMBER_ID VARCHAR(9),
ALT_ID VARCHAR(20),
DRSN VARCHAR(2),
SSN VARCHAR(9),
EPIPHANY_MEMBER_ID VARCHAR(18),
PORTAL_ADDRESS VARCHAR(30),
STATEMENT_VENDOR VARCHAR(20),
CONTENT_KEY VARCHAR(18),
EPIPHANY_COMMUNICATION_ID VARCHAR(200),
PRIORITY VARCHAR(4),
DAYS_UNTIL_EXPIRED VARCHAR(4),
CONTENT_DTL_KEY VARCHAR(18),
STATUS VARCHAR(1),
ACTIVATION_MEMBER_KEY bigint,
MESSAGE_BOARD_KEY bigint,
PORTAL_STATEMENT_LOC_KEY bigint,
temp_message_split_KEY bigint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (temp_message_split_KEY),
INDEX EPIPHANY_COMMUNICATION_ID_IDX (EPIPHANY_COMMUNICATION_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX2 (ALT_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX3 (DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX4 (ALT_ID, DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX1 (DH_MEMBER_ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here is its explain plan:
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| 1 | SIMPLE | t2 | NULL | index | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | TEMP_MESSAGE_SPLIT_IDX1 | 30 | NULL | 1619639 | 100.00 | Using index |
| 1 | UPDATE | t1 | NULL | ALL | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | NULL | NULL | NULL | 1619639 | 33.33 | Range checked for each record (index map: 0x5) |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
2 rows in set (0.00 sec)
This query is taking more than a day for processing 1700128 in the temp_message_split table and we need to tune it in such a way that it will take as much min. time as possible.
mysql sql query-optimization mysql-5.7
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
asked Jan 2 at 13:13
ParulParul
24
I have a query which is taking more than a day to complete with 1700128 records in the temp_message_split table, so please help in getting this tunned
Create table statement and explain plan has been provided below.
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND nullif(t1.dh_member_id,'') IS NOT NULL;
Here is create table DDL
CREATE TABLE
temp_message_split
(
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(30),
DOB VARCHAR(10),
EMPLOYEE_ID VARCHAR(20),
CES_CUST_NUM VARCHAR(7),
MED_POLICY_NUM VARCHAR(20),
EMAIL_ADDR VARCHAR(50),
DH_MEMBER_ID VARCHAR(9),
ALT_ID VARCHAR(20),
DRSN VARCHAR(2),
SSN VARCHAR(9),
EPIPHANY_MEMBER_ID VARCHAR(18),
PORTAL_ADDRESS VARCHAR(30),
STATEMENT_VENDOR VARCHAR(20),
CONTENT_KEY VARCHAR(18),
EPIPHANY_COMMUNICATION_ID VARCHAR(200),
PRIORITY VARCHAR(4),
DAYS_UNTIL_EXPIRED VARCHAR(4),
CONTENT_DTL_KEY VARCHAR(18),
STATUS VARCHAR(1),
ACTIVATION_MEMBER_KEY bigint,
MESSAGE_BOARD_KEY bigint,
PORTAL_STATEMENT_LOC_KEY bigint,
temp_message_split_KEY bigint NOT NULL AUTO_INCREMENT,
PRIMARY KEY (temp_message_split_KEY),
INDEX EPIPHANY_COMMUNICATION_ID_IDX (EPIPHANY_COMMUNICATION_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX2 (ALT_ID),
INDEX TEMP_MESSAGE_SPLIT_IDX3 (DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX4 (ALT_ID, DRSN),
INDEX TEMP_MESSAGE_SPLIT_IDX1 (DH_MEMBER_ID)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here is its explain plan:
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
| 1 | SIMPLE | t2 | NULL | index | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | TEMP_MESSAGE_SPLIT_IDX1 | 30 | NULL | 1619639 | 100.00 | Using index |
| 1 | UPDATE | t1 | NULL | ALL | PRIMARY,TEMP_MESSAGE_SPLIT_IDX1 | NULL | NULL | NULL | 1619639 | 33.33 | Range checked for each record (index map: 0x5) |
+----+-------------+-------+------------+-------+---------------------------------+-------------------------+---------+------+---------+----------+------------------------------------------------+
2 rows in set (0.00 sec)
This query is taking more than a day for processing 1700128 in the temp_message_split table and we need to tune it in such a way that it will take as much min. time as possible.
mysql sql query-optimization mysql-5.7
mysql sql query-optimization mysql-5.7
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
asked Jan 2 at 13:13
ParulParul
24
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
asked Jan 2 at 13:13
ParulParul
24
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
edited Jan 2 at 13:24
Gordon Linoff
790k35314418
790k35314418
asked Jan 2 at 13:13
ParulParul
24
asked Jan 2 at 13:13
ParulParul
24
asked Jan 2 at 13:13
ParulParul
24
24
1
Please explain what the query is supposed to be doing. The logic is not clear.
– Gordon Linoff
Jan 2 at 13:18
This query is meant for marking all duplicate records as "D" leaving one unique record with unique DH_MEMBER_ID
– Parul
Jan 2 at 13:29
add a comment |
1
Please explain what the query is supposed to be doing. The logic is not clear.
– Gordon Linoff
Jan 2 at 13:18
This query is meant for marking all duplicate records as "D" leaving one unique record with unique DH_MEMBER_ID
– Parul
Jan 2 at 13:29
1
1
Please explain what the query is supposed to be doing. The logic is not clear.
– Gordon Linoff
Jan 2 at 13:18
Please explain what the query is supposed to be doing. The logic is not clear.
– Gordon Linoff
Jan 2 at 13:18
This query is meant for marking all duplicate records as "D" leaving one unique record with unique DH_MEMBER_ID
– Parul
Jan 2 at 13:29
This query is meant for marking all duplicate records as "D" leaving one unique record with unique DH_MEMBER_ID
– Parul
Jan 2 at 13:29
add a comment |
2 Answers
2
active
oldest
votes
My best guess is that you want to set the status to D for all but the highest value of temp_message_split_key for each DH_MEMBER_ID.
The best solution is NOT EXISTS, but MySQL doesn't support NOT EXISTS on the same table in an UPDATE query.
So, another method uses GROUP BY:
UPDATE TEMP_MESSAGE_SPLIT t1 JOIN
(SELECT t2.DH_MEMBER_ID, MAX(t2.temp_message_split_key) as max_temp_message_split_key
FROM TEMP_MESSAGE_SPLIT t2
GROUP BY t2.DH_MEMBER_ID
) t2
ON t1.DH_MEMBER_ID = t2.DH_MEMBER_ID AND
t1.temp_message_split_key < t2.max_temp_message_split_key
SET t1.STATUS = 'D';
An index on (dh_member_id, temp_message_split_key) may help performance.
This will still take a long time, because you are (presumably) updating a lot of rows. If possible, then it is probably simpler just to create a new table with the values you want. That will be much faster (due to logging and locking).
The NULLIF() is probably doing nothing, but it has a minimal affect on the performance of your query. It would be better written as t1.dh_member_id <> ''.
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
add a comment |
Thank you Gordan for your last comment. I have updated the query based on this and now it is getting completed within seconds. Here is my new query:
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND t1.DH_MEMBER_ID <> ''
Would you be able to help me understand how such minor change in query caused such a huge difference in query performance.
answered Jan 2 at 14:06
ParulParul
24
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007022%2fneed-to-tune-a-mysql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
My best guess is that you want to set the status to D for all but the highest value of temp_message_split_key for each DH_MEMBER_ID.
The best solution is NOT EXISTS, but MySQL doesn't support NOT EXISTS on the same table in an UPDATE query.
So, another method uses GROUP BY:
UPDATE TEMP_MESSAGE_SPLIT t1 JOIN
(SELECT t2.DH_MEMBER_ID, MAX(t2.temp_message_split_key) as max_temp_message_split_key
FROM TEMP_MESSAGE_SPLIT t2
GROUP BY t2.DH_MEMBER_ID
) t2
ON t1.DH_MEMBER_ID = t2.DH_MEMBER_ID AND
t1.temp_message_split_key < t2.max_temp_message_split_key
SET t1.STATUS = 'D';
An index on (dh_member_id, temp_message_split_key) may help performance.
This will still take a long time, because you are (presumably) updating a lot of rows. If possible, then it is probably simpler just to create a new table with the values you want. That will be much faster (due to logging and locking).
The NULLIF() is probably doing nothing, but it has a minimal affect on the performance of your query. It would be better written as t1.dh_member_id <> ''.
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
add a comment |
My best guess is that you want to set the status to D for all but the highest value of temp_message_split_key for each DH_MEMBER_ID.
The best solution is NOT EXISTS, but MySQL doesn't support NOT EXISTS on the same table in an UPDATE query.
So, another method uses GROUP BY:
UPDATE TEMP_MESSAGE_SPLIT t1 JOIN
(SELECT t2.DH_MEMBER_ID, MAX(t2.temp_message_split_key) as max_temp_message_split_key
FROM TEMP_MESSAGE_SPLIT t2
GROUP BY t2.DH_MEMBER_ID
) t2
ON t1.DH_MEMBER_ID = t2.DH_MEMBER_ID AND
t1.temp_message_split_key < t2.max_temp_message_split_key
SET t1.STATUS = 'D';
An index on (dh_member_id, temp_message_split_key) may help performance.
This will still take a long time, because you are (presumably) updating a lot of rows. If possible, then it is probably simpler just to create a new table with the values you want. That will be much faster (due to logging and locking).
The NULLIF() is probably doing nothing, but it has a minimal affect on the performance of your query. It would be better written as t1.dh_member_id <> ''.
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
add a comment |
My best guess is that you want to set the status to D for all but the highest value of temp_message_split_key for each DH_MEMBER_ID.
The best solution is NOT EXISTS, but MySQL doesn't support NOT EXISTS on the same table in an UPDATE query.
So, another method uses GROUP BY:
UPDATE TEMP_MESSAGE_SPLIT t1 JOIN
(SELECT t2.DH_MEMBER_ID, MAX(t2.temp_message_split_key) as max_temp_message_split_key
FROM TEMP_MESSAGE_SPLIT t2
GROUP BY t2.DH_MEMBER_ID
) t2
ON t1.DH_MEMBER_ID = t2.DH_MEMBER_ID AND
t1.temp_message_split_key < t2.max_temp_message_split_key
SET t1.STATUS = 'D';
An index on (dh_member_id, temp_message_split_key) may help performance.
This will still take a long time, because you are (presumably) updating a lot of rows. If possible, then it is probably simpler just to create a new table with the values you want. That will be much faster (due to logging and locking).
The NULLIF() is probably doing nothing, but it has a minimal affect on the performance of your query. It would be better written as t1.dh_member_id <> ''.
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
My best guess is that you want to set the status to D for all but the highest value of temp_message_split_key for each DH_MEMBER_ID.
The best solution is NOT EXISTS, but MySQL doesn't support NOT EXISTS on the same table in an UPDATE query.
So, another method uses GROUP BY:
UPDATE TEMP_MESSAGE_SPLIT t1 JOIN
(SELECT t2.DH_MEMBER_ID, MAX(t2.temp_message_split_key) as max_temp_message_split_key
FROM TEMP_MESSAGE_SPLIT t2
GROUP BY t2.DH_MEMBER_ID
) t2
ON t1.DH_MEMBER_ID = t2.DH_MEMBER_ID AND
t1.temp_message_split_key < t2.max_temp_message_split_key
SET t1.STATUS = 'D';
An index on (dh_member_id, temp_message_split_key) may help performance.
This will still take a long time, because you are (presumably) updating a lot of rows. If possible, then it is probably simpler just to create a new table with the values you want. That will be much faster (due to logging and locking).
The NULLIF() is probably doing nothing, but it has a minimal affect on the performance of your query. It would be better written as t1.dh_member_id <> ''.
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
edited Jan 2 at 13:48
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 13:22
Gordon LinoffGordon Linoff
790k35314418
790k35314418
add a comment |
add a comment |
Thank you Gordan for your last comment. I have updated the query based on this and now it is getting completed within seconds. Here is my new query:
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND t1.DH_MEMBER_ID <> ''
Would you be able to help me understand how such minor change in query caused such a huge difference in query performance.
answered Jan 2 at 14:06
ParulParul
24
add a comment |
Thank you Gordan for your last comment. I have updated the query based on this and now it is getting completed within seconds. Here is my new query:
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND t1.DH_MEMBER_ID <> ''
Would you be able to help me understand how such minor change in query caused such a huge difference in query performance.
answered Jan 2 at 14:06
ParulParul
24
add a comment |
Thank you Gordan for your last comment. I have updated the query based on this and now it is getting completed within seconds. Here is my new query:
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND t1.DH_MEMBER_ID <> ''
Would you be able to help me understand how such minor change in query caused such a huge difference in query performance.
answered Jan 2 at 14:06
ParulParul
24
Thank you Gordan for your last comment. I have updated the query based on this and now it is getting completed within seconds. Here is my new query:
UPDATE TEMP_MESSAGE_SPLIT t1 , TEMP_MESSAGE_SPLIT t2
SET t1.STATUS = 'D'
WHERE
(t1.temp_message_split_key < t2.temp_message_split_key AND t1.DH_MEMBER_ID = t2.DH_MEMBER_ID)
AND t1.DH_MEMBER_ID <> ''
Would you be able to help me understand how such minor change in query caused such a huge difference in query performance.
answered Jan 2 at 14:06
ParulParul
24
answered Jan 2 at 14:06
ParulParul
24
answered Jan 2 at 14:06
ParulParul
24
answered Jan 2 at 14:06
ParulParul
24
24
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007022%2fneed-to-tune-a-mysql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Please explain what the query is supposed to be doing. The logic is not clear.
– Gordon Linoff
Jan 2 at 13:18
This query is meant for marking all duplicate records as "D" leaving one unique record with unique DH_MEMBER_ID
– Parul
Jan 2 at 13:29