Say I have a reference table like languages with only 20 records. Is it worth indexing a field like language_code (e.g. en, es) that's frequently used in queries? I'm wondering if an efficient server would effectively be indexing columns like this automatically.
This is specifically for MariaDB with InnoDB engine.
On the MySQL support page they write some about index optimization. (I assume MariaDB isn't deviating too much from this since they're somewhat similar.) Here they write that indexes are not so important on small tables.
Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.
On the page about How to avoid full table scans they define the limit as "more than a few rows".
For tables with more than a few rows, consider redesigning the query by adding an index for one or more of the columns tested in the WHERE clause.
I personally would add an index just because I always add index to the key on all tables. In your case I don't know if the index would contribute so much though.
Additional indexing on such small table is not going to help that will worth the write overhead. though I have seen very complex query which has many small tables and if query is not written in right way optimizer not able t come up with good execution plan and query takes unexpected execution time. most of the time db engine should not have such issue but if you encounter such things then start looking at is query written efficiently.
