3

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 ?

3
  • 3
    The author of the code doesn't understand indexes in MySQL/MariaDB. Take their database expertise with a grain of salt. Mar 10, 2023 at 18:55
  • If it is k-v table, why isn't key unique? If it is, then have PRIMARY KEY(key) and get rid of id.
    – Rick James
    Mar 30, 2023 at 0:00
  • "index was not needed" -- bull-bleep.
    – Rick James
    Mar 30, 2023 at 0:00

1 Answer 1

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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