Error
Error Code:
1167
MariaDB Error 1167: Storage Engine Indexing Issue
Description
This error indicates that the selected storage engine for a table cannot create an index on a specified column. It typically occurs when attempting to define an index on a data type, column property, or configuration that the storage engine does not support for indexing. This often happens during `CREATE TABLE` or `ALTER TABLE` operations.
Error Message
The used storage engine can't index column '%s'
Known Causes
3 known causesUnsupported Column Data Type or Length
The column's data type (e.g., BLOB, TEXT) or its defined length exceeds the indexing capabilities or limits of the storage engine, or requires a specific prefix length that was not provided.
Storage Engine Incapability
The chosen storage engine for the table (e.g., CSV, MEMORY, or specific versions of others) inherently does not support indexing the column's properties or type as specified.
Virtual/Generated Column Indexing Rules
Attempting to index a virtual or generated column in a way that is not supported by the storage engine or violates its specific indexing rules for such columns.
Solutions
4 solutions available1. Change Data Type for Indexing medium
Modify the column's data type to one that the storage engine supports for indexing.
1
Identify the column causing the error. The error message will typically specify the column name.
2
Determine the current data type of the problematic column.
SHOW COLUMNS FROM your_table_name LIKE 'your_column_name';3
Research the indexing capabilities of your current storage engine (e.g., InnoDB, MyISAM) for different data types. Some data types, like BLOB, TEXT, or JSON, may not be directly indexable or require specific configurations.
4
If the column's data type is not suitable for indexing, alter the table to change it to a compatible type. For example, if you have a large TEXT column that you need to index for searching, consider using a VARCHAR with a specified length or a full-text index if applicable.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255);5
After changing the data type, you may need to recreate any indexes that were on that column.
ALTER TABLE your_table_name ADD INDEX index_name (your_column_name);2. Use Full-Text Indexing for Textual Data medium
Implement full-text indexing for columns containing large amounts of text if your goal is efficient text searching.
1
Identify the column containing textual data that cannot be indexed directly.
2
Ensure your storage engine supports full-text indexing (e.g., InnoDB and MyISAM).
3
Add a full-text index to the problematic column. Note that full-text indexes are specifically designed for searching within text and have different performance characteristics than standard B-tree indexes.
ALTER TABLE your_table_name ADD FULLTEXT(your_column_name);4
When querying, use the `MATCH() AGAINST()` syntax for full-text searches.
SELECT * FROM your_table_name WHERE MATCH(your_column_name) AGAINST('search term');3. Change Storage Engine advanced
Switch to a storage engine that supports indexing the problematic column's data type.
1
Identify the current storage engine of the table. You can find this information using `SHOW CREATE TABLE`.
SHOW CREATE TABLE your_table_name;2
Research storage engines available in MariaDB and their indexing capabilities. InnoDB is generally the default and most feature-rich engine.
3
If your current engine (e.g., a custom or older engine) doesn't support indexing certain data types, consider migrating to a more capable engine like InnoDB.
ALTER TABLE your_table_name ENGINE=InnoDB;4
After changing the storage engine, you may need to rebuild any existing indexes or create new ones that were not automatically converted.
ALTER TABLE your_table_name ADD INDEX index_name (your_column_name);4. Review and Refactor Schema Design advanced
Re-evaluate the need for indexing the specific column and consider alternative design patterns.
1
Analyze why the column is being indexed. Is it for searching, sorting, or ensuring uniqueness?
2
If the column contains large, unstructured data (like BLOBs or long TEXTs) that are not meant for direct comparison or search, consider storing such data separately or using a different approach.
3
For very large text fields, consider extracting relevant keywords or metadata into a separate, indexable column. This might involve application-level processing or triggers.
4
If the goal is to index a large JSON document, explore using generated columns that extract specific, indexable values from the JSON.
ALTER TABLE your_table_name ADD COLUMN extracted_value INT AS (JSON_EXTRACT(your_json_column, '$.path.to.value')) STORED;
ALTER TABLE your_table_name ADD INDEX idx_extracted_value (extracted_value);