Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in...
Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN
Can somebody clear this up for me? What's the "(materialized)" mean if materialized views aren't a thing in MySQL? Using MySQL Server 5.6, Workbench 6.3.
mysql mysql-5.6 materialized-view
asked 2 hours ago
bjk116bjk116
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN
Can somebody clear this up for me? What's the "(materialized)" mean if materialized views aren't a thing in MySQL? Using MySQL Server 5.6, Workbench 6.3.
mysql mysql-5.6 materialized-view
asked 2 hours ago
bjk116bjk116
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN
Can somebody clear this up for me? What's the "(materialized)" mean if materialized views aren't a thing in MySQL? Using MySQL Server 5.6, Workbench 6.3.
mysql mysql-5.6 materialized-view
asked 2 hours ago
bjk116bjk116
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN
Can somebody clear this up for me? What's the "(materialized)" mean if materialized views aren't a thing in MySQL? Using MySQL Server 5.6, Workbench 6.3.
mysql mysql-5.6 materialized-view
mysql mysql-5.6 materialized-view
asked 2 hours ago
bjk116bjk116
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
bjk116bjk116
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
bjk116bjk116
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 2 hours ago
bjk116bjk116
153
asked 2 hours ago
bjk116bjk116
153
153
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
bjk116 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.
In MySQL, it has a rather limited meaning. It refers to certain "derived" tables (eg, FROM ( SELECT ... )). The Optimizer has multiple ways to deal with such constructs.
- Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
- Before 5.6, for multiple derived tables (for
FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table. - 5.6 introduced the automatic ability to create a temp table for one of the derived tables. Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it. (A side note: A suitable rewrite of the entire query can usually perform better.)
There is essentially nothing that you, the programmer, can do to control this version of "materialized view". (Well, rewrite to avoid the need.)
5.7 and 8.0 have even more performance improvements. But still no "materialized view" as the outside world knows.
answered 53 mins ago
Rick JamesRick James
41.1k22258
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present aSELECTandSHOW CREATE TABLE(in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable.GROUP BY a ORDER BY boften requires two temp tables. But note: a temp table does not necessarily touch disk.
– Rick James
32 mins ago
add a comment |
Extended EXPLAIN Output Format
- <materialize>(query fragment)
Subquery materialization is used.
answered 1 hour ago
AkinaAkina
3,5001311
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Subquery != View
– Akina
1 hour ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
bjk116 is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f226699%2fwhy-are-some-views-listed-as-materialized-in-mysql-explain-statements-if-materia%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
To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.
In MySQL, it has a rather limited meaning. It refers to certain "derived" tables (eg, FROM ( SELECT ... )). The Optimizer has multiple ways to deal with such constructs.
- Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
- Before 5.6, for multiple derived tables (for
FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table. - 5.6 introduced the automatic ability to create a temp table for one of the derived tables. Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it. (A side note: A suitable rewrite of the entire query can usually perform better.)
There is essentially nothing that you, the programmer, can do to control this version of "materialized view". (Well, rewrite to avoid the need.)
5.7 and 8.0 have even more performance improvements. But still no "materialized view" as the outside world knows.
answered 53 mins ago
Rick JamesRick James
41.1k22258
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present aSELECTandSHOW CREATE TABLE(in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable.GROUP BY a ORDER BY boften requires two temp tables. But note: a temp table does not necessarily touch disk.
– Rick James
32 mins ago
add a comment |
To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.
In MySQL, it has a rather limited meaning. It refers to certain "derived" tables (eg, FROM ( SELECT ... )). The Optimizer has multiple ways to deal with such constructs.
- Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
- Before 5.6, for multiple derived tables (for
FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table. - 5.6 introduced the automatic ability to create a temp table for one of the derived tables. Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it. (A side note: A suitable rewrite of the entire query can usually perform better.)
There is essentially nothing that you, the programmer, can do to control this version of "materialized view". (Well, rewrite to avoid the need.)
5.7 and 8.0 have even more performance improvements. But still no "materialized view" as the outside world knows.
answered 53 mins ago
Rick JamesRick James
41.1k22258
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present aSELECTandSHOW CREATE TABLE(in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable.GROUP BY a ORDER BY boften requires two temp tables. But note: a temp table does not necessarily touch disk.
– Rick James
32 mins ago
add a comment |
To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.
In MySQL, it has a rather limited meaning. It refers to certain "derived" tables (eg, FROM ( SELECT ... )). The Optimizer has multiple ways to deal with such constructs.
- Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
- Before 5.6, for multiple derived tables (for
FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table. - 5.6 introduced the automatic ability to create a temp table for one of the derived tables. Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it. (A side note: A suitable rewrite of the entire query can usually perform better.)
There is essentially nothing that you, the programmer, can do to control this version of "materialized view". (Well, rewrite to avoid the need.)
5.7 and 8.0 have even more performance improvements. But still no "materialized view" as the outside world knows.
answered 53 mins ago
Rick JamesRick James
41.1k22258
To the outside world (if I am not mistaken), a "materialized view" is a permanent table that is automatically inserted/updated in based on data flowing into some other table.
In MySQL, it has a rather limited meaning. It refers to certain "derived" tables (eg, FROM ( SELECT ... )). The Optimizer has multiple ways to deal with such constructs.
- Before 5.6, for a single such construct, the Optimizer would simply use that as the first "table" without having to "materialize" anything.
- Before 5.6, for multiple derived tables (for
FROM (SELECT...) JOIN (SELECT...)) the Optimizer would have to repeatedly re-execute all but the first derived table. - 5.6 introduced the automatic ability to create a temp table for one of the derived tables. Furthermore, the Optimizer became smart enough to discover the optimal index for this temp table and create it. (A side note: A suitable rewrite of the entire query can usually perform better.)
There is essentially nothing that you, the programmer, can do to control this version of "materialized view". (Well, rewrite to avoid the need.)
5.7 and 8.0 have even more performance improvements. But still no "materialized view" as the outside world knows.
answered 53 mins ago
Rick JamesRick James
41.1k22258
answered 53 mins ago
Rick JamesRick James
41.1k22258
answered 53 mins ago
Rick JamesRick James
41.1k22258
answered 53 mins ago
Rick JamesRick James
41.1k22258
41.1k22258
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present aSELECTandSHOW CREATE TABLE(in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable.GROUP BY a ORDER BY boften requires two temp tables. But note: a temp table does not necessarily touch disk.
– Rick James
32 mins ago
add a comment |
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present aSELECTandSHOW CREATE TABLE(in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable.GROUP BY a ORDER BY boften requires two temp tables. But note: a temp table does not necessarily touch disk.
– Rick James
32 mins ago
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
Thank you for the answer. So if I'm seeing 'materialized' views, odds are I can optimize the query to avoid the need for these temporary tables.
– bjk116
39 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present a
SELECT and SHOW CREATE TABLE (in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable. GROUP BY a ORDER BY b often requires two temp tables. But note: a temp table does not necessarily touch disk.– Rick James
32 mins ago
@bjk116 - Not necessarily. Some can be avoided, some cannot. I don't know a general pattern. If you would like to present a
SELECT and SHOW CREATE TABLE (in a new Question), we could discuss optimizations. On a related topic, there are cases where temp tables are unavoidable. GROUP BY a ORDER BY b often requires two temp tables. But note: a temp table does not necessarily touch disk.– Rick James
32 mins ago
add a comment |
Extended EXPLAIN Output Format
- <materialize>(query fragment)
Subquery materialization is used.
answered 1 hour ago
AkinaAkina
3,5001311
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Subquery != View
– Akina
1 hour ago
add a comment |
Extended EXPLAIN Output Format
- <materialize>(query fragment)
Subquery materialization is used.
answered 1 hour ago
AkinaAkina
3,5001311
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Subquery != View
– Akina
1 hour ago
add a comment |
Extended EXPLAIN Output Format
- <materialize>(query fragment)
Subquery materialization is used.
answered 1 hour ago
AkinaAkina
3,5001311
Extended EXPLAIN Output Format
- <materialize>(query fragment)
Subquery materialization is used.
answered 1 hour ago
AkinaAkina
3,5001311
answered 1 hour ago
AkinaAkina
3,5001311
answered 1 hour ago
AkinaAkina
3,5001311
answered 1 hour ago
AkinaAkina
3,5001311
3,5001311
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Subquery != View
– Akina
1 hour ago
add a comment |
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Subquery != View
– Akina
1 hour ago
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Ok so after searching that term, it just means it creates a temporary table correct? And if that's the case, how come some of my other views do NOT do this? Is this a case of MySQL making the call to make a view materialized or not, or is this something another developer specified in my database?
– bjk116
1 hour ago
Subquery != View
– Akina
1 hour ago
Subquery != View
– Akina
1 hour ago
add a comment |
bjk116 is a new contributor. Be nice, and check out our Code of Conduct.
bjk116 is a new contributor. Be nice, and check out our Code of Conduct.
bjk116 is a new contributor. Be nice, and check out our Code of Conduct.
bjk116 is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fdba.stackexchange.com%2fquestions%2f226699%2fwhy-are-some-views-listed-as-materialized-in-mysql-explain-statements-if-materia%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
