Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.