Optimising mysql query - select inner join and exists
I have the following mysql query, the query takes a long time(4 secs) to execute. Is there any other way I could write it so that it takes quicker to execute. The dataset is fairly small (10 000 rows)
SELECT x.pid, x.rank, x.description, x.price, x.ifile
FROM (SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
) as x
WHERE x.description LIKE '%a%'
ORDER BY x.rank ASC
LIMIT 0,10
I have also tried the following with similar results:
SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
AND product.description LIKE '%a%'
ORDER BY product.rank ASC
LIMIT 0,10
mysql
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
add a comment |
I have the following mysql query, the query takes a long time(4 secs) to execute. Is there any other way I could write it so that it takes quicker to execute. The dataset is fairly small (10 000 rows)
SELECT x.pid, x.rank, x.description, x.price, x.ifile
FROM (SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
) as x
WHERE x.description LIKE '%a%'
ORDER BY x.rank ASC
LIMIT 0,10
I have also tried the following with similar results:
SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
AND product.description LIKE '%a%'
ORDER BY product.rank ASC
LIMIT 0,10
mysql
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
First identify the slowdown culprit - is it theLIKEin your where clause?
– WillardSolutions
Nov 20 '18 at 20:35
thanks good point. It seems to be WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
– the_big_blackbox
Nov 20 '18 at 20:38
add a comment |
I have the following mysql query, the query takes a long time(4 secs) to execute. Is there any other way I could write it so that it takes quicker to execute. The dataset is fairly small (10 000 rows)
SELECT x.pid, x.rank, x.description, x.price, x.ifile
FROM (SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
) as x
WHERE x.description LIKE '%a%'
ORDER BY x.rank ASC
LIMIT 0,10
I have also tried the following with similar results:
SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
AND product.description LIKE '%a%'
ORDER BY product.rank ASC
LIMIT 0,10
mysql
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
I have the following mysql query, the query takes a long time(4 secs) to execute. Is there any other way I could write it so that it takes quicker to execute. The dataset is fairly small (10 000 rows)
SELECT x.pid, x.rank, x.description, x.price, x.ifile
FROM (SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
) as x
WHERE x.description LIKE '%a%'
ORDER BY x.rank ASC
LIMIT 0,10
I have also tried the following with similar results:
SELECT product.pid, product.rank, product.description, product.price, images.ifile
FROM product INNER JOIN images ON product.pid=images.pid
WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
AND product.description LIKE '%a%'
ORDER BY product.rank ASC
LIMIT 0,10
mysql
mysql
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
edited Nov 20 '18 at 20:44
Uueerdo
11.7k1816
11.7k1816
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
asked Nov 20 '18 at 20:34
the_big_blackbox
307520
307520
First identify the slowdown culprit - is it theLIKEin your where clause?
– WillardSolutions
Nov 20 '18 at 20:35
thanks good point. It seems to be WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
– the_big_blackbox
Nov 20 '18 at 20:38
add a comment |
First identify the slowdown culprit - is it theLIKEin your where clause?
– WillardSolutions
Nov 20 '18 at 20:35
thanks good point. It seems to be WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
– the_big_blackbox
Nov 20 '18 at 20:38
First identify the slowdown culprit - is it the
LIKE in your where clause?– WillardSolutions
Nov 20 '18 at 20:35
First identify the slowdown culprit - is it the
LIKE in your where clause?– WillardSolutions
Nov 20 '18 at 20:35
thanks good point. It seems to be WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
– the_big_blackbox
Nov 20 '18 at 20:38
thanks good point. It seems to be WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
– the_big_blackbox
Nov 20 '18 at 20:38
add a comment |
2 Answers
2
active
oldest
votes
Correlated subqueries can be relatively expensive, instead of EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid) executing a query for every row from the outer query, it would probably be better to try product.pid IN (SELECT pid FROM links) or product.pid IN (SELECT DISTINCT pid FROM links).
If links were a very large table, and product relatively small, a correlated subquery could still be better.
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
add a comment |
It seems you already have your answer, but just curious, can you try this query?
select p.pid, p.rank, p.description, p.price, i.ifile
from product p
inner join images i on i.pid = p.pid
inner join links l on l.pid = p.pid
where p.description like '%a%'
order by p.rank asc limit 0,10;
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
@the_big_blackbox a join could be faster than the query I provided in my answer, iflinks.pidis indexed, but must presumepidis unique inlinks.
– Uueerdo
Nov 20 '18 at 22:00
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%2f53401092%2foptimising-mysql-query-select-inner-join-and-exists%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
Correlated subqueries can be relatively expensive, instead of EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid) executing a query for every row from the outer query, it would probably be better to try product.pid IN (SELECT pid FROM links) or product.pid IN (SELECT DISTINCT pid FROM links).
If links were a very large table, and product relatively small, a correlated subquery could still be better.
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
add a comment |
Correlated subqueries can be relatively expensive, instead of EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid) executing a query for every row from the outer query, it would probably be better to try product.pid IN (SELECT pid FROM links) or product.pid IN (SELECT DISTINCT pid FROM links).
If links were a very large table, and product relatively small, a correlated subquery could still be better.
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
add a comment |
Correlated subqueries can be relatively expensive, instead of EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid) executing a query for every row from the outer query, it would probably be better to try product.pid IN (SELECT pid FROM links) or product.pid IN (SELECT DISTINCT pid FROM links).
If links were a very large table, and product relatively small, a correlated subquery could still be better.
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
Correlated subqueries can be relatively expensive, instead of EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid) executing a query for every row from the outer query, it would probably be better to try product.pid IN (SELECT pid FROM links) or product.pid IN (SELECT DISTINCT pid FROM links).
If links were a very large table, and product relatively small, a correlated subquery could still be better.
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
answered Nov 20 '18 at 20:40
Uueerdo
11.7k1816
11.7k1816
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
add a comment |
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
thanks it worked. Your second option product.pid IN (SELECT DISTINCT pid FROM links) got it down to 0.8 seconds
– the_big_blackbox
Nov 20 '18 at 20:53
add a comment |
It seems you already have your answer, but just curious, can you try this query?
select p.pid, p.rank, p.description, p.price, i.ifile
from product p
inner join images i on i.pid = p.pid
inner join links l on l.pid = p.pid
where p.description like '%a%'
order by p.rank asc limit 0,10;
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
@the_big_blackbox a join could be faster than the query I provided in my answer, iflinks.pidis indexed, but must presumepidis unique inlinks.
– Uueerdo
Nov 20 '18 at 22:00
add a comment |
It seems you already have your answer, but just curious, can you try this query?
select p.pid, p.rank, p.description, p.price, i.ifile
from product p
inner join images i on i.pid = p.pid
inner join links l on l.pid = p.pid
where p.description like '%a%'
order by p.rank asc limit 0,10;
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
@the_big_blackbox a join could be faster than the query I provided in my answer, iflinks.pidis indexed, but must presumepidis unique inlinks.
– Uueerdo
Nov 20 '18 at 22:00
add a comment |
It seems you already have your answer, but just curious, can you try this query?
select p.pid, p.rank, p.description, p.price, i.ifile
from product p
inner join images i on i.pid = p.pid
inner join links l on l.pid = p.pid
where p.description like '%a%'
order by p.rank asc limit 0,10;
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
It seems you already have your answer, but just curious, can you try this query?
select p.pid, p.rank, p.description, p.price, i.ifile
from product p
inner join images i on i.pid = p.pid
inner join links l on l.pid = p.pid
where p.description like '%a%'
order by p.rank asc limit 0,10;
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
answered Nov 20 '18 at 20:56
Julio Daniel Reyes
2,082815
2,082815
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
@the_big_blackbox a join could be faster than the query I provided in my answer, iflinks.pidis indexed, but must presumepidis unique inlinks.
– Uueerdo
Nov 20 '18 at 22:00
add a comment |
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
@the_big_blackbox a join could be faster than the query I provided in my answer, iflinks.pidis indexed, but must presumepidis unique inlinks.
– Uueerdo
Nov 20 '18 at 22:00
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
Thanks, it took longer - 2.07 seconds to execute. The other issue it joined the links table, I only needed to know if the link existed.
– the_big_blackbox
Nov 20 '18 at 20:59
@the_big_blackbox a join could be faster than the query I provided in my answer, if
links.pid is indexed, but must presume pid is unique in links.– Uueerdo
Nov 20 '18 at 22:00
@the_big_blackbox a join could be faster than the query I provided in my answer, if
links.pid is indexed, but must presume pid is unique in links.– Uueerdo
Nov 20 '18 at 22:00
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.
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%2fstackoverflow.com%2fquestions%2f53401092%2foptimising-mysql-query-select-inner-join-and-exists%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
First identify the slowdown culprit - is it the
LIKEin your where clause?– WillardSolutions
Nov 20 '18 at 20:35
thanks good point. It seems to be WHERE EXISTS (SELECT 1 FROM links WHERE product.pid=links.pid)
– the_big_blackbox
Nov 20 '18 at 20:38