Timeout with inner join on two MySQL tables
Here are two tables, with only 50K rows in each:
CREATE TABLE `ps_product_access` (
`id_order` int(10) UNSIGNED NOT NULL DEFAULT '0',
`id_product_access` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `ps_product_access`
ADD KEY `id_order` (`id_order`);
CREATE TABLE `ps_orders` (
`id_order` int(10) UNSIGNED NOT NULL,
`id_order_renew` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `ps_orders`
ADD PRIMARY KEY (`id_order`)
ADD KEY `ps_orders__id_order_renew__index` (`id_order_renew`);
The tables are overly simplified with only the relevant fields. There is no foreign key, but I can't add one right now (data is inconsistent in this database).
This query does not work (it means it's an infinite loading):
SELECT pa.`id_product_access`
FROM `ps_product_access` pa
INNER JOIN `ps_orders` o ON pa.id_order = o.id_order_renew;
I can't understand why? It seems pretty simple, just an inner join. I know I can optimize query with WHERE EXISTS but this is not the main question. This query should not run into an infinite loading, since there is almost no data (50k rows). Did I missed something?
side note: I run this query on a fresh install of MySQL 8 (installed via brew on a MacOS). I saw the same problem with the same data on another computer with a totally different config (ubuntu VM on windows, MySQL5)
mysql sql indexing
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
|
show 7 more comments
Here are two tables, with only 50K rows in each:
CREATE TABLE `ps_product_access` (
`id_order` int(10) UNSIGNED NOT NULL DEFAULT '0',
`id_product_access` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `ps_product_access`
ADD KEY `id_order` (`id_order`);
CREATE TABLE `ps_orders` (
`id_order` int(10) UNSIGNED NOT NULL,
`id_order_renew` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `ps_orders`
ADD PRIMARY KEY (`id_order`)
ADD KEY `ps_orders__id_order_renew__index` (`id_order_renew`);
The tables are overly simplified with only the relevant fields. There is no foreign key, but I can't add one right now (data is inconsistent in this database).
This query does not work (it means it's an infinite loading):
SELECT pa.`id_product_access`
FROM `ps_product_access` pa
INNER JOIN `ps_orders` o ON pa.id_order = o.id_order_renew;
I can't understand why? It seems pretty simple, just an inner join. I know I can optimize query with WHERE EXISTS but this is not the main question. This query should not run into an infinite loading, since there is almost no data (50k rows). Did I missed something?
side note: I run this query on a fresh install of MySQL 8 (installed via brew on a MacOS). I saw the same problem with the same data on another computer with a totally different config (ubuntu VM on windows, MySQL5)
mysql sql indexing
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
Probably a typo but there is no column id_product_access in ps_product_access
– Joakim Danielson
Nov 22 '18 at 13:23
Are you running this query from application code ? or using PHPMyadmin, Workbench etc ?
– Madhur Bhaiya
Nov 22 '18 at 13:23
@JoakimDanielson Thank you! I wanted to simplify the table schema with only the required columns, and I removed this one by mistake. I edited my question.
– rap-2-h
Nov 22 '18 at 13:25
Are correctly joining id_order_renew with id_order?
– Salman A
Nov 22 '18 at 13:26
@MadhurBhaiyamysqlconsole from my terminal
– rap-2-h
Nov 22 '18 at 13:26
|
show 7 more comments
Here are two tables, with only 50K rows in each:
CREATE TABLE `ps_product_access` (
`id_order` int(10) UNSIGNED NOT NULL DEFAULT '0',
`id_product_access` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `ps_product_access`
ADD KEY `id_order` (`id_order`);
CREATE TABLE `ps_orders` (
`id_order` int(10) UNSIGNED NOT NULL,
`id_order_renew` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `ps_orders`
ADD PRIMARY KEY (`id_order`)
ADD KEY `ps_orders__id_order_renew__index` (`id_order_renew`);
The tables are overly simplified with only the relevant fields. There is no foreign key, but I can't add one right now (data is inconsistent in this database).
This query does not work (it means it's an infinite loading):
SELECT pa.`id_product_access`
FROM `ps_product_access` pa
INNER JOIN `ps_orders` o ON pa.id_order = o.id_order_renew;
I can't understand why? It seems pretty simple, just an inner join. I know I can optimize query with WHERE EXISTS but this is not the main question. This query should not run into an infinite loading, since there is almost no data (50k rows). Did I missed something?
side note: I run this query on a fresh install of MySQL 8 (installed via brew on a MacOS). I saw the same problem with the same data on another computer with a totally different config (ubuntu VM on windows, MySQL5)
mysql sql indexing
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
Here are two tables, with only 50K rows in each:
CREATE TABLE `ps_product_access` (
`id_order` int(10) UNSIGNED NOT NULL DEFAULT '0',
`id_product_access` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `ps_product_access`
ADD KEY `id_order` (`id_order`);
CREATE TABLE `ps_orders` (
`id_order` int(10) UNSIGNED NOT NULL,
`id_order_renew` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `ps_orders`
ADD PRIMARY KEY (`id_order`)
ADD KEY `ps_orders__id_order_renew__index` (`id_order_renew`);
The tables are overly simplified with only the relevant fields. There is no foreign key, but I can't add one right now (data is inconsistent in this database).
This query does not work (it means it's an infinite loading):
SELECT pa.`id_product_access`
FROM `ps_product_access` pa
INNER JOIN `ps_orders` o ON pa.id_order = o.id_order_renew;
I can't understand why? It seems pretty simple, just an inner join. I know I can optimize query with WHERE EXISTS but this is not the main question. This query should not run into an infinite loading, since there is almost no data (50k rows). Did I missed something?
side note: I run this query on a fresh install of MySQL 8 (installed via brew on a MacOS). I saw the same problem with the same data on another computer with a totally different config (ubuntu VM on windows, MySQL5)
mysql sql indexing
mysql sql indexing
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
edited Nov 22 '18 at 13:24
rap-2-h
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
asked Nov 22 '18 at 13:18
rap-2-hrap-2-h
9,684769128
9,684769128
Probably a typo but there is no column id_product_access in ps_product_access
– Joakim Danielson
Nov 22 '18 at 13:23
Are you running this query from application code ? or using PHPMyadmin, Workbench etc ?
– Madhur Bhaiya
Nov 22 '18 at 13:23
@JoakimDanielson Thank you! I wanted to simplify the table schema with only the required columns, and I removed this one by mistake. I edited my question.
– rap-2-h
Nov 22 '18 at 13:25
Are correctly joining id_order_renew with id_order?
– Salman A
Nov 22 '18 at 13:26
@MadhurBhaiyamysqlconsole from my terminal
– rap-2-h
Nov 22 '18 at 13:26
|
show 7 more comments
Probably a typo but there is no column id_product_access in ps_product_access
– Joakim Danielson
Nov 22 '18 at 13:23
Are you running this query from application code ? or using PHPMyadmin, Workbench etc ?
– Madhur Bhaiya
Nov 22 '18 at 13:23
@JoakimDanielson Thank you! I wanted to simplify the table schema with only the required columns, and I removed this one by mistake. I edited my question.
– rap-2-h
Nov 22 '18 at 13:25
Are correctly joining id_order_renew with id_order?
– Salman A
Nov 22 '18 at 13:26
@MadhurBhaiyamysqlconsole from my terminal
– rap-2-h
Nov 22 '18 at 13:26
Probably a typo but there is no column id_product_access in ps_product_access
– Joakim Danielson
Nov 22 '18 at 13:23
Probably a typo but there is no column id_product_access in ps_product_access
– Joakim Danielson
Nov 22 '18 at 13:23
Are you running this query from application code ? or using PHPMyadmin, Workbench etc ?
– Madhur Bhaiya
Nov 22 '18 at 13:23
Are you running this query from application code ? or using PHPMyadmin, Workbench etc ?
– Madhur Bhaiya
Nov 22 '18 at 13:23
@JoakimDanielson Thank you! I wanted to simplify the table schema with only the required columns, and I removed this one by mistake. I edited my question.
– rap-2-h
Nov 22 '18 at 13:25
@JoakimDanielson Thank you! I wanted to simplify the table schema with only the required columns, and I removed this one by mistake. I edited my question.
– rap-2-h
Nov 22 '18 at 13:25
Are correctly joining id_order_renew with id_order?
– Salman A
Nov 22 '18 at 13:26
Are correctly joining id_order_renew with id_order?
– Salman A
Nov 22 '18 at 13:26
@MadhurBhaiya
mysql console from my terminal– rap-2-h
Nov 22 '18 at 13:26
@MadhurBhaiya
mysql console from my terminal– rap-2-h
Nov 22 '18 at 13:26
|
show 7 more comments
1 Answer
1
active
oldest
votes
The column id_order in ps_product_access defaults to 0, maybe you need to check how many rows you have with id_order = 0
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
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%2f53431892%2ftimeout-with-inner-join-on-two-mysql-tables%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
The column id_order in ps_product_access defaults to 0, maybe you need to check how many rows you have with id_order = 0
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
add a comment |
The column id_order in ps_product_access defaults to 0, maybe you need to check how many rows you have with id_order = 0
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
add a comment |
The column id_order in ps_product_access defaults to 0, maybe you need to check how many rows you have with id_order = 0
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
The column id_order in ps_product_access defaults to 0, maybe you need to check how many rows you have with id_order = 0
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
answered Nov 22 '18 at 15:12
Joakim DanielsonJoakim Danielson
8,2173724
8,2173724
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
add a comment |
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
Thank you! Accepted, +1
– rap-2-h
Nov 22 '18 at 15:46
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%2f53431892%2ftimeout-with-inner-join-on-two-mysql-tables%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
Probably a typo but there is no column id_product_access in ps_product_access
– Joakim Danielson
Nov 22 '18 at 13:23
Are you running this query from application code ? or using PHPMyadmin, Workbench etc ?
– Madhur Bhaiya
Nov 22 '18 at 13:23
@JoakimDanielson Thank you! I wanted to simplify the table schema with only the required columns, and I removed this one by mistake. I edited my question.
– rap-2-h
Nov 22 '18 at 13:25
Are correctly joining id_order_renew with id_order?
– Salman A
Nov 22 '18 at 13:26
@MadhurBhaiya
mysqlconsole from my terminal– rap-2-h
Nov 22 '18 at 13:26