How to enable Large Indexes in MariaDB 10 on Debian 10
This tutorial shows you how to enable large indexes in MariaDB 10 on Debian 10. This refers to the innodb_large_prefix option which exists in MariaDB and MySQL. I will show you how to enable the large index option permanently by editing the MariaDB configuration file and I will also show you how to enable it temporarily in the current database session by using SQL commands. InnoDB large prefix allows it to have index key prefixes up to 3072 bytes (for 16k pages, smaller otherwise).
Enable InnoDB Large Indexes in MariaDB using the config file
Edit the MariaDB server configuration file:
nano /etc/mysql/mariadb.conf.d/50-server
And place the following lines right after the [mysqld] line:
innodb-file-format=barracuda innodb-file-per-table=ON innodb-large-prefix=ON innodb_default_row_format = 'DYNAMIC'
Close the editor. Then restart MariaDB:
systemctl restart mariadb.service
Enable Large Indexes in MariaDB using SQL commands
These commands are an alternative approach to the config editing option, so don’t use both methods. Open a MariaDB database connection for the root user on the console:
mysql -u root -p
Then run the following commands:
SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_large_prefix=1; Quit
Then log in again and run:
SET GLOBAL innodb_default_row_format=DYNAMIC;The large index prefix option is enabled in your MariaDB system now.
