Inconsistent results with MySQL, query never ends on MariaDB

Question

We have some really strange behaviour on MySQL (8.0.29) that we can't explain.

We have 2 tables + 1 link table in-between, illustrated by this schema :

We run a query whereby we need to fetch the id and organization_id from table1 for all the records that have a linked record in table2. However, if there are 2 linked records in table2, we still only want a single record from table1.

We use Doctrine (PHP) integrated in a complex application so the query is generated for us. The resulting query is :

SELECT
  table1.organization_id,
  table1.id AS id_0
FROM
  table1
  LEFT JOIN table2 ON (
    EXISTS (
      SELECT
        1
      FROM
        link1
      WHERE
        link1.table2_id = table2.uuid
        AND link1.table1_id IN (table1.id)
    )
  )
WHERE
  table1.location_id = 605
  AND table1.status IN ('confirmed')
  and table1.organization_id=1
ORDER BY table1.id DESC
LIMIT
  1000

This query is supposed to return 260 rows, but it returns only 1. Unless we just restarted MySQL, then it returns 0 and will continue to return 0 until we remove either the LIMIT clause or the ORDER BY clause.

On MariaDB it gets worse : the query just uses CPU and we killed it after a few minutes.

If you want to give it a go, the data dump is at https://gist.github.com/wimg/c8af87bd30b036c4de5e386e095f6416

Tried it on MySQL 8.0.29 (currently the most recent version).

Anyone have any idea what's going on here ?

Answer 1

Indexes needed:

table1:  INDEX(location_id, organization_id, status, id)
link1:  PRIMARY KEY(table2_id, table1_id)
link1:  INDEX      (table1_id, table2_id)

LEFT JOIN ... ON EXISTS (...) is a very strange pattern. "the query is generated for us" == Yuck! Please rewrite it. Try changing

    FROM  table1
    LEFT JOIN  table2  ON ( EXISTS 
            (
                SELECT  1
                    FROM  link1
                    WHERE  link1.table2_id = table2.uuid
                      AND  link1.table1_id IN (table1.id) ) 
                          )

to

    FROM  table1
    JOIN  link1  ON link1.table1_id = table1.id
    JOIN  table2 ON table2.uuid = link1.table2_id

(if that is what you meant.)

For further discussion, please provide EXPLAIN SELECT on each server. And add it to the Bug report(s).

A discussion of the indexes for a Many-to-many table.