Does simple key value storage on MySQL/MariaDB requires index on key?

I was exchanging with another developer regarding the performance of SELECT queries on a simple key/value table.

CREATE TABLE cache (
            id BIGINT unsigned not null auto_increment,
            name VARCHAR(32),
            uri VARCHAR(255),
            value LONGTEXT,
            expire DATETIME,
            PRIMARY KEY (id)
        ) DEFAULT CHARSET=utf8";

The table has no index by default and I experienced really poor performance of simple SELECT queries as soon as the cache growed up:

SELECT value
FROM cache
WHERE name = '$cache_name' AND expire >= '$now'
LIMIT 1;

I added an index on name and it solves my performance problem.

However, the author of the original code told me that the cardinality of data stored in the name field was really low because there is no duplicate values (we store hashes), and so an index was not needed and even worse it may causes performance problems.

Is it true ? Why do i experienced the contrary ? Could it be possible that MySQL and MariaDB behave differently ?

Without an index, you will get a full table scan every time. Glad you indexed the table.

I would recommend you make a compound index for every better performance as the table grows:

ALTER TABLE cache ADD INDEX name_expire_ndx (name,expire);

If you frequently clear the cache, just remember to run ANALYZE TABLE cachel

Perhaps once a week, run OPTIMIZE TABLE cache; to shrink the table.

StackExchange Database Administrators Q#324617, answer score: 5