Timeout with inner join on two MySQL tables

Question

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)

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
@SalmanA Thank you! "Are correctly joining id_order_renew with id_order? " What do you mean? — rap-2-h, Nov 22 '18 at 13:31
Check whether some other process has taken a lock on one of the tables *(or similar)* which is preventing your process from reading them. https://stackoverflow.com/questions/2499976/detecting-locked-tables-locked-by-lock-table — MatBailie, Nov 22 '18 at 13:32
id_order in ps_product_access defaults to 0, maybe you need to check how many rows you have with id_order = 0. — Joakim Danielson, Nov 22 '18 at 14:27
@JoakimDanielson That was that! There are 40k rows in the first table and 10k row in the second with 0... So 40 millions rows after join. You could post your comment as an answer! — rap-2-h, Nov 22 '18 at 15:08

score 1 · Accepted Answer · answered Nov 22 '18 at 15:12

1

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 Danielson

43,251
5
22
52

Timeout with inner join on two MySQL tables

1 Answers1