Track database name in anemometer queries
Hello i have installed anemometer on our server in order to see the queries that need optimization.
From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is affecting the most in the query bellow:
select wp_posts.*
from wp_posts
inner join wp_postmeta on ( wp_posts.id = wp_postmeta.post_id )
where ?=?
and ( ( wp_postmeta.meta_key = ?
and cast(wp_postmeta.meta_value as char) = ? )
)
and wp_posts.post_type = ?
and ((wp_posts.post_status = ?))
group by wp_posts.id
order by wp_posts.post_date desc
Is there a way to track the database as well in anemometer?
mysql query-performance anemometer
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Hello i have installed anemometer on our server in order to see the queries that need optimization.
From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is affecting the most in the query bellow:
select wp_posts.*
from wp_posts
inner join wp_postmeta on ( wp_posts.id = wp_postmeta.post_id )
where ?=?
and ( ( wp_postmeta.meta_key = ?
and cast(wp_postmeta.meta_value as char) = ? )
)
and wp_posts.post_type = ?
and ((wp_posts.post_status = ?))
group by wp_posts.id
order by wp_posts.post_date desc
Is there a way to track the database as well in anemometer?
mysql query-performance anemometer
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Why do you needCAST()? What is?=?? Which table(s) might that reference? Please provideSHOW CREATE TABLEfor both tables.
– Rick James
Dec 11 '15 at 22:51
The query is from a wordpress blog. So the query is not build by me :)
– Gabriel Solomon
Dec 12 '15 at 8:30
Any idea what will be substituted there? It could make a big difference in optimizing the query.
– Rick James
Dec 12 '15 at 19:33
add a comment |
Hello i have installed anemometer on our server in order to see the queries that need optimization.
From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is affecting the most in the query bellow:
select wp_posts.*
from wp_posts
inner join wp_postmeta on ( wp_posts.id = wp_postmeta.post_id )
where ?=?
and ( ( wp_postmeta.meta_key = ?
and cast(wp_postmeta.meta_value as char) = ? )
)
and wp_posts.post_type = ?
and ((wp_posts.post_status = ?))
group by wp_posts.id
order by wp_posts.post_date desc
Is there a way to track the database as well in anemometer?
mysql query-performance anemometer
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
Hello i have installed anemometer on our server in order to see the queries that need optimization.
From the website hosted a few have a wordpress blog on them so it is hard to guess witch one is affecting the most in the query bellow:
select wp_posts.*
from wp_posts
inner join wp_postmeta on ( wp_posts.id = wp_postmeta.post_id )
where ?=?
and ( ( wp_postmeta.meta_key = ?
and cast(wp_postmeta.meta_value as char) = ? )
)
and wp_posts.post_type = ?
and ((wp_posts.post_status = ?))
group by wp_posts.id
order by wp_posts.post_date desc
Is there a way to track the database as well in anemometer?
mysql query-performance anemometer
mysql query-performance anemometer
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
edited Sep 30 '16 at 19:24
Paul White♦
53.2k14284457
53.2k14284457
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
asked Dec 8 '15 at 17:37
Gabriel SolomonGabriel Solomon
4801713
4801713
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Why do you needCAST()? What is?=?? Which table(s) might that reference? Please provideSHOW CREATE TABLEfor both tables.
– Rick James
Dec 11 '15 at 22:51
The query is from a wordpress blog. So the query is not build by me :)
– Gabriel Solomon
Dec 12 '15 at 8:30
Any idea what will be substituted there? It could make a big difference in optimizing the query.
– Rick James
Dec 12 '15 at 19:33
add a comment |
1
Why do you needCAST()? What is?=?? Which table(s) might that reference? Please provideSHOW CREATE TABLEfor both tables.
– Rick James
Dec 11 '15 at 22:51
The query is from a wordpress blog. So the query is not build by me :)
– Gabriel Solomon
Dec 12 '15 at 8:30
Any idea what will be substituted there? It could make a big difference in optimizing the query.
– Rick James
Dec 12 '15 at 19:33
1
1
Why do you need
CAST()? What is ?=?? Which table(s) might that reference? Please provide SHOW CREATE TABLE for both tables.– Rick James
Dec 11 '15 at 22:51
Why do you need
CAST()? What is ?=?? Which table(s) might that reference? Please provide SHOW CREATE TABLE for both tables.– Rick James
Dec 11 '15 at 22:51
The query is from a wordpress blog. So the query is not build by me :)
– Gabriel Solomon
Dec 12 '15 at 8:30
The query is from a wordpress blog. So the query is not build by me :)
– Gabriel Solomon
Dec 12 '15 at 8:30
Any idea what will be substituted there? It could make a big difference in optimizing the query.
– Rick James
Dec 12 '15 at 19:33
Any idea what will be substituted there? It could make a big difference in optimizing the query.
– Rick James
Dec 12 '15 at 19:33
add a comment |
1 Answer
1
active
oldest
votes
(Until I get answers to my comment, here is all I can suggest...)
1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.
Get rid of
wp_postmetaall together. And get rid of theGROUP BY, which was probably a byproduct of theJOIN.INDEX(post_type, post_status, post_date)
That leaves you with just
select *
from wp_posts
where post_type = ?
and post_status = ?
order by post_date desc
Does that give you the right answer?
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
@RickJames where did you seeLEFTin the query?
– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@no worries. I only looked at this after another[anemometer]question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.
– ypercubeᵀᴹ
Sep 30 '16 at 21:58
|
show 1 more 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
});
}
});
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%2f123219%2ftrack-database-name-in-anemometer-queries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
(Until I get answers to my comment, here is all I can suggest...)
1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.
Get rid of
wp_postmetaall together. And get rid of theGROUP BY, which was probably a byproduct of theJOIN.INDEX(post_type, post_status, post_date)
That leaves you with just
select *
from wp_posts
where post_type = ?
and post_status = ?
order by post_date desc
Does that give you the right answer?
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
@RickJames where did you seeLEFTin the query?
– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@no worries. I only looked at this after another[anemometer]question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.
– ypercubeᵀᴹ
Sep 30 '16 at 21:58
|
show 1 more comment
(Until I get answers to my comment, here is all I can suggest...)
1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.
Get rid of
wp_postmetaall together. And get rid of theGROUP BY, which was probably a byproduct of theJOIN.INDEX(post_type, post_status, post_date)
That leaves you with just
select *
from wp_posts
where post_type = ?
and post_status = ?
order by post_date desc
Does that give you the right answer?
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
@RickJames where did you seeLEFTin the query?
– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@no worries. I only looked at this after another[anemometer]question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.
– ypercubeᵀᴹ
Sep 30 '16 at 21:58
|
show 1 more comment
(Until I get answers to my comment, here is all I can suggest...)
1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.
Get rid of
wp_postmetaall together. And get rid of theGROUP BY, which was probably a byproduct of theJOIN.INDEX(post_type, post_status, post_date)
That leaves you with just
select *
from wp_posts
where post_type = ?
and post_status = ?
order by post_date desc
Does that give you the right answer?
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
(Until I get answers to my comment, here is all I can suggest...)
1. LEFT and the two tests on wp_postmeta have no impact on the outcome; get rid of them.
Get rid of
wp_postmetaall together. And get rid of theGROUP BY, which was probably a byproduct of theJOIN.INDEX(post_type, post_status, post_date)
That leaves you with just
select *
from wp_posts
where post_type = ?
and post_status = ?
order by post_date desc
Does that give you the right answer?
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
edited Sep 30 '16 at 21:53
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
answered Dec 11 '15 at 22:55
Rick JamesRick James
43.6k22259
43.6k22259
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
@RickJames where did you seeLEFTin the query?
– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@no worries. I only looked at this after another[anemometer]question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.
– ypercubeᵀᴹ
Sep 30 '16 at 21:58
|
show 1 more comment
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
@RickJames where did you seeLEFTin the query?
– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@no worries. I only looked at this after another[anemometer]question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.
– ypercubeᵀᴹ
Sep 30 '16 at 21:58
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Since this is a wordpress installation i cannot go alter the code and queries. I was interested to find out to which blog has the slow queries. Since all of them have the same database structure and queries i could do that through the database name. This is my question, how can i track the database name as well as the query
– Gabriel Solomon
Dec 12 '15 at 8:33
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
Turn on the slowlog, lower long_query_time, and wait for something to show up in the slowlog.
– Rick James
Dec 12 '15 at 19:32
@RickJames where did you see
LEFT in the query?– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@RickJames where did you see
LEFT in the query?– ypercubeᵀᴹ
Sep 30 '16 at 21:37
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@ypercubeᵀᴹ -- I do make mistakes. :(
– Rick James
Sep 30 '16 at 21:53
@no worries. I only looked at this after another
[anemometer] question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.– ypercubeᵀᴹ
Sep 30 '16 at 21:58
@no worries. I only looked at this after another
[anemometer] question appeared (and was curious what this anemometer is). Didn't realize that this one was a year old, until I sent you the message.– ypercubeᵀᴹ
Sep 30 '16 at 21:58
|
show 1 more comment
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.
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%2f123219%2ftrack-database-name-in-anemometer-queries%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
Why do you need
CAST()? What is?=?? Which table(s) might that reference? Please provideSHOW CREATE TABLEfor both tables.– Rick James
Dec 11 '15 at 22:51
The query is from a wordpress blog. So the query is not build by me :)
– Gabriel Solomon
Dec 12 '15 at 8:30
Any idea what will be substituted there? It could make a big difference in optimizing the query.
– Rick James
Dec 12 '15 at 19:33