MySQL raises ER_NO_FT_MATERIALIZED_SUBQUERY (error 3018, SQLSTATE HY000) when you attempt to create a FULLTEXT index on the result of a materialized subquery or derived table, which is not supported.
ER_NO_FT_MATERIALIZED_SUBQUERY occurs when you try to add a FULLTEXT index to a materialized subquery or derived table in MySQL. Convert the subquery into a real table or use a normal index on the base table to resolve the issue.
ER_NO_FT_MATERIALIZED_SUBQUERY
MySQL throws error 3018 with SQLSTATE HY000 whenever a statement tries to build a FULLTEXT index on top of a materialized subquery or derived table. The storage engine cannot generate the necessary full-text auxiliary structures for a non-persistent table, so the operation is rejected.
The error was introduced in MySQL 5.7.4. It commonly appears during attempts to accelerate text searches in views, common table expressions, or derived queries that are temporarily materialized by the optimizer.
FULLTEXT indexing needs persistent on-disk metadata and auxiliary posting lists. Because a materialized subquery is transient, MySQL would have to rebuild the entire index each time the query runs, leading to heavy resource usage. To avoid unpredictable performance and disk churn, the server blocks the request and surfaces error 3018 instead.
Creating a FULLTEXT index inside a CREATE TABLE ... SELECT statement that sources data from a subquery triggers the error. The same happens when you attempt ALTER TABLE ADD FULLTEXT to a temporary table derived from a subquery.
The issue also arises if you wrap a base table in a view or CTE and then try to create a FULLTEXT index on the view output, because MySQL treats the result as a materialized subquery.
The primary fix is to persist the data in a regular InnoDB or MyISAM table before applying the FULLTEXT index. You can INSERT the subquery result into a permanent table or convert the derived table to a concrete staging table.
Alternatively, move the FULLTEXT index to the underlying base table when feasible. This keeps the index supported and avoids repeated materialization.
In reporting pipelines, engineers often create summary tables with CREATE TABLE new_tbl AS SELECT ...; adding a FULLTEXT index in the same statement triggers error 3018. Separate the steps: create the table, populate it, then add the index.
When experimenting in notebooks or the Galaxy SQL editor, users may create temporary tables for ad-hoc analysis. Remember that FULLTEXT indexing requires a non-temporary table; switch to a permanent table or maintain the index on the original source.
Stage text-search data in persistent tables before indexing. Always separate data extraction from index creation in two discrete SQL statements. Add checks in CI pipelines or Galaxy query reviews to block FULLTEXT on transient objects.
Monitor schema changes with tools like Galaxy Collections to ensure FULLTEXT indexes remain on supported tables after refactors.
Error 1214 (ER_TABLE_CANT_HANDLE_FT)：Raised when FULLTEXT is attempted on a storage engine that does not support it. Switch to InnoDB or MyISAM.
Error 1286 (ER_UNKNOWN_STORAGE_ENGINE)：Occurs if FULLTEXT is requested on an unknown engine. Install or enable the engine plugin.
The statement tries to create a new table from a subquery and adds a FULLTEXT index at the same time.
An ALTER TABLE adds a FULLTEXT key to a temporary or derived table that originated from a subquery.
The user attempts to index a view, CTE, or inline derived result rather than the physical base table.
Occurs when attempting FULLTEXT on a storage engine that lacks support. Use InnoDB or MyISAM.
Raised when a FULLTEXT key name duplicates an existing key. Rename or drop the conflicting key.
Appears if the column type is not VARCHAR, CHAR, or TEXT. Convert the column to a supported type.
No. MySQL does not support FULLTEXT on views. Materialize the view into a real table first, then apply the index.
Yes. The limitation remains in MySQL 8.0 and 8.1; FULLTEXT requires a persistent table.
You can use MATCH ... AGAINST on the base table with a FULLTEXT index or rely on BOOLEAN MODE filters instead of indexing a derived result.
Galaxy's linting alerts flag attempts to add FULLTEXT to temporary tables and suggest persisting the data or indexing the source table.
.avif)
.avif){kind=logo}
