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.