1

I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure:

CREATE TABLE logs (
  log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  entity_type VARCHAR(10),
  entity_id INT UNSIGNED,
  field VARCHAR(30),
  old_value VARCHAR(255),
  new_value VARCHAR(255),
  log_message TEXT DEFAULT NULL,
  user_id INT UNSIGNED,
  INDEX (entity_type, entity_id),
  INDEX (entity_type, field),
  INDEX (log_date),
  INDEX (user_id)
) ENGINE=InnoDB

On one of our customers' database, where the table as almost 50 million rows, I am seeing a strange result, for which a query which there are no rows takes a much longer time than one where there are rows.

For example:

SELECT *
FROM logs
WHERE entity_type = 'employee'
  AND entity_id = ID_FOR_WHICH_LOGS_EXIST
ORDER BY log_date DESC, log_id DESC
LIMIT 10

gives a result in about 2-3 seconds, here its EXPLAIN output:

+------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+
| id   | select_type | table         | type  | possible_keys                        | key      | key_len | ref  | rows | filtered | Extra       |
+------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | logs          | index | entity_type,entity_type_2            | log_date | 21      | NULL | 2094 |   100.00 | Using where |
+------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+

While another query

SELECT *
FROM logs
WHERE entity_type = 'employee'
  AND entity_id = INVALID_ID_OR_WITHOUT_LOGS
ORDER BY log_date DESC, log_id DESC
LIMIT 10

takes almost 5 minutes to complete, here is the EXPLAIN output, identical to the previous query:

+------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+
| id   | select_type | table         | type  | possible_keys                        | key      | key_len | ref  | rows | filtered | Extra       |
+------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+
|    1 | SIMPLE      | logs          | index | entity_type,entity_type_2            | log_date | 21      | NULL | 2094 |   100.00 | Using where |
+------+-------------+---------------+-------+--------------------------------------+----------+---------+------+------+----------+-------------+

I have noticed that, in the second case, removing the LIMIT clause brings the execution time to a few milliseconds; the same happens if I change the LIMIT to have an offset, for example LIMIT 10, 10.

Is there anything wrong with my queries or table structure (and, if so, how could I fix it?), or is it an issue with the database itself?

EDIT:

I have just noticed that also the first query, if used with a LIMIT clause without offset, is slower than the same query with an offset, the execution time goes from 2-3 seconds to about 0.01 seconds.

3
  • @ErgestBasha I've edited the question with the EXPLAIN output. Commented May 13, 2022 at 13:15
  • It seems to have worked! Though I am not sure why ... all the WHERE columns were indexed already, so it should have quickly filtered the valid rows, before sorting, and see that there is none. Or am I wrong? Commented May 13, 2022 at 14:33
  • It now seems to use the index entity_type (the first one in the table definition), not the new composite index you made me test; still, much better times than before. Commented May 13, 2022 at 14:47

0

Your Answer

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

Browse other questions tagged or ask your own question.