5

Is the number of table joins in MariaDB 10 limited to 61 as is the case for MySQL or another number?

(I couldn't find the answer in the MariaDB documentation or by googling).

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
  • Why would it matter? You're not likely to need anything like that number of JOINs in a properly normalized schema! – Strawberry Apr 30 '14 at 13:55
  • Depends on complexity. My previous (£7 million) project hit this problem in a big way and had to use a lot of workarounds. – Steve Chambers Apr 30 '14 at 14:01
  • Has that query finished running yet? – Strawberry Apr 30 '14 at 14:06
  • 1
    Joins don't equal slowness if foreign keys are used. E.g. see http://stackoverflow.com/questions/173726/when-and-why-are-database-joins-expensive#174047 – Steve Chambers Apr 30 '14 at 14:12
  • 2
    If you use multi-table inheritance in Doctrine, this limit is quite easy to hit. Yes, it is usually result of a bad design decision, but you may realize that too late to change that. – VaclavSir Nov 04 '14 at 14:52
  • Not necessarily bad design - the limit could potentially be hit in any relational data model that contains a lot of entities given the JPA 2.0 default for `@ManyToOne` mappings is eager loading. The project I was working on ended up changing all `@ManyToOne`s to use lazy loading. – Steve Chambers May 11 '17 at 14:36

1 Answers1

5

MariaDB has the same maximum number of 61 tables in a join.

This query

CREATE TABLE t (i INT(10) NOT NULL);
select *
from t a01 join t a02 join t a03 join t a04 join t a05 join t a06 join t a07 join t a08 join t a09 join t a10
join t a11 join t a12 join t a13 join t a14 join t a15 join t a16 join t a17 join t a18 join t a19 join t a20
join t a21 join t a22 join t a23 join t a24 join t a25 join t a26 join t a27 join t a28 join t a29 join t a30
join t a31 join t a32 join t a33 join t a34 join t a35 join t a36 join t a37 join t a38 join t a39 join t a40
join t a41 join t a42 join t a43 join t a44 join t a45 join t a46 join t a47 join t a48 join t a49 join t a50
join t a51 join t a52 join t a53 join t a54 join t a55 join t a56 join t a57 join t a58 join t a59 join t a60
join t a61 join t a62;

yields ERROR 1116 (HY000): Too many tables; MariaDB can only use 61 tables in a join.

Julian Ladisch
  • 1,367
  • 9
  • 10