How to improve select range of values if one one of them have specific variable in Mysql
My question is how can i speed this up. There should be more elegant way to handle it.
The inner select is done in 0,038 sec but this one is done in 6,007 sec i dont know how can i improve this performace
select * FROM table
where number1 in (
SELECT number1
FROM table
WHERE `date` = 'yyyy-mm-dd'
AND value1 = 'variable1'
AND value2 = 1
)
the thing is that i need range of values from the same table if one contains in the value2 the variable 1
so from table like that
id|number1| value1 | value2
1 | 11403 | exempl1 | null
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
4 | 11405 | exempl1 | null
5 | 11405 | exempl1 | null
i get only this
id|number1| value1 | value2
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
mysql sql performance select
asked Nov 20 '18 at 8:13
arudielarudiel
84
add a comment |
My question is how can i speed this up. There should be more elegant way to handle it.
The inner select is done in 0,038 sec but this one is done in 6,007 sec i dont know how can i improve this performace
select * FROM table
where number1 in (
SELECT number1
FROM table
WHERE `date` = 'yyyy-mm-dd'
AND value1 = 'variable1'
AND value2 = 1
)
the thing is that i need range of values from the same table if one contains in the value2 the variable 1
so from table like that
id|number1| value1 | value2
1 | 11403 | exempl1 | null
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
4 | 11405 | exempl1 | null
5 | 11405 | exempl1 | null
i get only this
id|number1| value1 | value2
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
mysql sql performance select
asked Nov 20 '18 at 8:13
arudielarudiel
84
And what result do you want?
– jarlh
Nov 20 '18 at 8:18
Improve performance. There should be a way to do that.
– arudiel
Nov 20 '18 at 8:21
add a comment |
My question is how can i speed this up. There should be more elegant way to handle it.
The inner select is done in 0,038 sec but this one is done in 6,007 sec i dont know how can i improve this performace
select * FROM table
where number1 in (
SELECT number1
FROM table
WHERE `date` = 'yyyy-mm-dd'
AND value1 = 'variable1'
AND value2 = 1
)
the thing is that i need range of values from the same table if one contains in the value2 the variable 1
so from table like that
id|number1| value1 | value2
1 | 11403 | exempl1 | null
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
4 | 11405 | exempl1 | null
5 | 11405 | exempl1 | null
i get only this
id|number1| value1 | value2
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
mysql sql performance select
asked Nov 20 '18 at 8:13
arudielarudiel
84
My question is how can i speed this up. There should be more elegant way to handle it.
The inner select is done in 0,038 sec but this one is done in 6,007 sec i dont know how can i improve this performace
select * FROM table
where number1 in (
SELECT number1
FROM table
WHERE `date` = 'yyyy-mm-dd'
AND value1 = 'variable1'
AND value2 = 1
)
the thing is that i need range of values from the same table if one contains in the value2 the variable 1
so from table like that
id|number1| value1 | value2
1 | 11403 | exempl1 | null
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
4 | 11405 | exempl1 | null
5 | 11405 | exempl1 | null
i get only this
id|number1| value1 | value2
2 | 11404 | exempl1 | 1
3 | 11404 | exempl1 | null
mysql sql performance select
mysql sql performance select
asked Nov 20 '18 at 8:13
arudielarudiel
84
asked Nov 20 '18 at 8:13
arudielarudiel
84
edited Nov 20 '18 at 10:28
arudiel
asked Nov 20 '18 at 8:13
arudielarudiel
84
asked Nov 20 '18 at 8:13
arudielarudiel
84
asked Nov 20 '18 at 8:13
arudielarudiel
84
84
And what result do you want?
– jarlh
Nov 20 '18 at 8:18
Improve performance. There should be a way to do that.
– arudiel
Nov 20 '18 at 8:21
add a comment |
And what result do you want?
– jarlh
Nov 20 '18 at 8:18
Improve performance. There should be a way to do that.
– arudiel
Nov 20 '18 at 8:21
And what result do you want?
– jarlh
Nov 20 '18 at 8:18
And what result do you want?
– jarlh
Nov 20 '18 at 8:18
Improve performance. There should be a way to do that.
– arudiel
Nov 20 '18 at 8:21
Improve performance. There should be a way to do that.
– arudiel
Nov 20 '18 at 8:21
add a comment |
1 Answer
1
active
oldest
votes
You can convert it into Correlated Subquery with Exists. MySQL optimizer may be able to use Indexes (if defined) in this case.
SELECT t1.*
FROM table AS t1
WHERE EXISTS( SELECT 1
FROM table AS t2
WHERE t2.number1 = t1.number1 AND
t2.`date` = 'yyyy-mm-dd' AND
t2.value1 = 'variable1' AND
t2.value2 = 1 )
If indexes are not defined, you can define a Composite Index on (number1, date, value1, value2), for better performance.
ALTER TABLE `table`
ADD INDEX comp_idx1(number1, `date`, value1, value2);
Another optimization possibility is to fetch only those columns which are really required in your application code. Avoid using SELECT *. You may read: Why is SELECT * considered harmful?
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
@arudiel check my solution; I have never usedIDat all. I dont know why are you matchingID. It will kill all the purpose
– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel add the Index (alter tablestatement) which I have mentioned in my answer.
– Madhur Bhaiya
Nov 20 '18 at 10:35
|
show 6 more comments
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%2f53388731%2fhow-to-improve-select-range-of-values-if-one-one-of-them-have-specific-variable%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
You can convert it into Correlated Subquery with Exists. MySQL optimizer may be able to use Indexes (if defined) in this case.
SELECT t1.*
FROM table AS t1
WHERE EXISTS( SELECT 1
FROM table AS t2
WHERE t2.number1 = t1.number1 AND
t2.`date` = 'yyyy-mm-dd' AND
t2.value1 = 'variable1' AND
t2.value2 = 1 )
If indexes are not defined, you can define a Composite Index on (number1, date, value1, value2), for better performance.
ALTER TABLE `table`
ADD INDEX comp_idx1(number1, `date`, value1, value2);
Another optimization possibility is to fetch only those columns which are really required in your application code. Avoid using SELECT *. You may read: Why is SELECT * considered harmful?
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
@arudiel check my solution; I have never usedIDat all. I dont know why are you matchingID. It will kill all the purpose
– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel add the Index (alter tablestatement) which I have mentioned in my answer.
– Madhur Bhaiya
Nov 20 '18 at 10:35
|
show 6 more comments
You can convert it into Correlated Subquery with Exists. MySQL optimizer may be able to use Indexes (if defined) in this case.
SELECT t1.*
FROM table AS t1
WHERE EXISTS( SELECT 1
FROM table AS t2
WHERE t2.number1 = t1.number1 AND
t2.`date` = 'yyyy-mm-dd' AND
t2.value1 = 'variable1' AND
t2.value2 = 1 )
If indexes are not defined, you can define a Composite Index on (number1, date, value1, value2), for better performance.
ALTER TABLE `table`
ADD INDEX comp_idx1(number1, `date`, value1, value2);
Another optimization possibility is to fetch only those columns which are really required in your application code. Avoid using SELECT *. You may read: Why is SELECT * considered harmful?
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
@arudiel check my solution; I have never usedIDat all. I dont know why are you matchingID. It will kill all the purpose
– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel add the Index (alter tablestatement) which I have mentioned in my answer.
– Madhur Bhaiya
Nov 20 '18 at 10:35
|
show 6 more comments
You can convert it into Correlated Subquery with Exists. MySQL optimizer may be able to use Indexes (if defined) in this case.
SELECT t1.*
FROM table AS t1
WHERE EXISTS( SELECT 1
FROM table AS t2
WHERE t2.number1 = t1.number1 AND
t2.`date` = 'yyyy-mm-dd' AND
t2.value1 = 'variable1' AND
t2.value2 = 1 )
If indexes are not defined, you can define a Composite Index on (number1, date, value1, value2), for better performance.
ALTER TABLE `table`
ADD INDEX comp_idx1(number1, `date`, value1, value2);
Another optimization possibility is to fetch only those columns which are really required in your application code. Avoid using SELECT *. You may read: Why is SELECT * considered harmful?
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
You can convert it into Correlated Subquery with Exists. MySQL optimizer may be able to use Indexes (if defined) in this case.
SELECT t1.*
FROM table AS t1
WHERE EXISTS( SELECT 1
FROM table AS t2
WHERE t2.number1 = t1.number1 AND
t2.`date` = 'yyyy-mm-dd' AND
t2.value1 = 'variable1' AND
t2.value2 = 1 )
If indexes are not defined, you can define a Composite Index on (number1, date, value1, value2), for better performance.
ALTER TABLE `table`
ADD INDEX comp_idx1(number1, `date`, value1, value2);
Another optimization possibility is to fetch only those columns which are really required in your application code. Avoid using SELECT *. You may read: Why is SELECT * considered harmful?
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
edited Nov 20 '18 at 9:09
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 8:24
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
@arudiel check my solution; I have never usedIDat all. I dont know why are you matchingID. It will kill all the purpose
– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel add the Index (alter tablestatement) which I have mentioned in my answer.
– Madhur Bhaiya
Nov 20 '18 at 10:35
|
show 6 more comments
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
@arudiel check my solution; I have never usedIDat all. I dont know why are you matchingID. It will kill all the purpose
– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel add the Index (alter tablestatement) which I have mentioned in my answer.
– Madhur Bhaiya
Nov 20 '18 at 10:35
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
there is no improvement in performance, it works but still over 6 seconds downtime.
– arudiel
Nov 20 '18 at 9:06
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
@arudiel Do you have the indexing defined on the columns ? Add the composite index as specified in my answer.
– Madhur Bhaiya
Nov 20 '18 at 9:09
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
At first i was thinking it helped but after testing there is the same problem as for the standard select by value. In the solution @Madhur Bhaiya sugested afted adding the id colum only shows the ID that have met the statment values not the whole rows.
– arudiel
Nov 20 '18 at 10:32
@arudiel check my solution; I have never used
ID at all. I dont know why are you matching ID. It will kill all the purpose– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel check my solution; I have never used
ID at all. I dont know why are you matching ID. It will kill all the purpose– Madhur Bhaiya
Nov 20 '18 at 10:33
@arudiel add the Index (
alter table statement) which I have mentioned in my answer.– Madhur Bhaiya
Nov 20 '18 at 10:35
@arudiel add the Index (
alter table statement) which I have mentioned in my answer.– Madhur Bhaiya
Nov 20 '18 at 10:35
|
show 6 more comments
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%2f53388731%2fhow-to-improve-select-range-of-values-if-one-one-of-them-have-specific-variable%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
And what result do you want?
– jarlh
Nov 20 '18 at 8:18
Improve performance. There should be a way to do that.
– arudiel
Nov 20 '18 at 8:21