Using sub-selectors will fail if too many pages are chosen

[jlahijani]

Let's say I have this simple selector to choose all the orders in my system (I'm using findMany):

parent=/orders/,template=order

That works fine, but I want it to be more precise by finding only the pages based on checking something in a repeater that is part of the order template (called "timings", which is where I'm storing when an order should proceed to production, when it should be redacted, when it should be entirely deleted).

parent=/orders/,template=order,timings=[timing_type.name=proceed-after,datetime!='',datetime<{$time}]

That works fine too, but in my situation if there exceeds a certain amount of 'order' pages for which that potentially matches, MariaDB fails with this:

SQLSTATE[0A000]: Feature not supported: 140 Aria table '#sql-temptable-236b0e-c5f-14db' has too many columns and/or indexes and/or unique constraints.

That seems to be an incredibly rare issue since Googling that error it brings up 1-2 pages if searching it like so:
https://www.google.com/search?q=%22has+too+many+columns+and%2For+indexes+and%2For+unique+constraints%22

I investigated and tried changing $config->lazyPageChunkSize to something much lower than the default of 250 would help, but it didn't.

I then asked ChatGPT about the error and it said:

---

Details

The error you're seeing:

SQLSTATE[0A000]: Feature not supported: 140 Aria table '#sql-temptable-236b0e-c5f-14db' has too many columns and/or indexes and/or unique constraints.

means MariaDB/MySQL attempted to create a temporary table using the Aria storage engine, but the number of columns, indexes, or unique constraints exceeded a limit that Aria supports.

What's happening:

• MySQL/MariaDB sometimes creates internal temporary tables (e.g. for GROUP BY, ORDER BY, UNION, complex joins).
• If the result set is small or simple, it uses in-memory tables.
• If not, it spills to disk using Aria (formerly MyISAM) for the temporary table.
• Aria has stricter limits than InnoDB (e.g. max 1,017 columns per table).

Common causes:

• A SELECT with many columns
• A GROUP BY or ORDER BY on many fields
• A subquery or derived table with excessive complexity

Solutions:

✅ Preferred: Reduce result complexity

• SELECT fewer columns — only what you need
• Avoid SELECT * on tables with lots of fields
• Simplify complex JOINs or UNIONs
• Avoid over-indexing temporary derived tables

⚙️ Optional: Force different temporary storage

You can change how temporary tables are handled:

SET SESSION internal_tmp_mem_storage_engine = 'TempTable';

Or if needed (but not recommended long-term):

SET SESSION internal_tmp_disk_storage_engine = 'InnoDB';

This forces MySQL/MariaDB to use InnoDB for disk-based temp tables, which supports more columns and features than Aria.

How to confirm what’s going on:

Run:

SHOW VARIABLES LIKE '%tmp%engine%';

To see what your current temp storage engines are.

If you're getting this in application code, try simplifying the query or enabling query logging to inspect what SQL is triggering it.

Let me know if you want help breaking down or optimizing a specific query.

---

I don't know the inner-workings of how selectors ultimately join tables and such, but I'm guessing it's hitting some sort of limit.

To fix for now, I'm falling back to just doing the simple selector:

parent=/orders/,template=order

Then using conditional logic to see if the timing is what I want:

if($order->timings->get("[timing_type.name=proceed-after,datetime!='',datetime<{$time}]")) {
  // code to proceed on this order
}

[jlahijani]

@ryancramerdesign This is a pretty big issue and it also occurs when using owner selector on large sets of data.

I'm getting bit by this one and having to use additional fields with the same data to work-around it.

[jlahijani]

Actually it's not an incredibly rare issue. I Googled wrongly. Better search results:
https://www.google.com/search?q=mysql+too+many+columns