Mariadb – Why does MariaDB choose different index use for the same data

indexmariadb

I have this table (this is the output from show create table on both servers, ie. using diff to compare the output shows only the auto increment value, because one has moved on slightly from the snapshot).

Create Table: CREATE TABLE `logs` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` varchar(39) CHARACTER SET latin1 NOT NULL,
  `method` varchar(6) CHARACTER SET latin1 NOT NULL,
  `endpoint` varchar(1000) CHARACTER SET latin1 NOT NULL,
  `status` int(3) NOT NULL,
  `params` text,
  `data` mediumtext,
  `client_username` varchar(526) DEFAULT NULL,
  `client_ip` varchar(39) CHARACTER SET latin1 DEFAULT NULL,
  `extra_params` text,
  `error_code` int(10) DEFAULT NULL,
  `request_id` varchar(255) DEFAULT NULL,
  `domain_id` bigint(11) unsigned DEFAULT NULL,
  `admin_id` bigint(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_id` (`request_id`),
  KEY `endpoint` (`endpoint`),
  KEY `method` (`method`),
  KEY `client_username` (`client_username`(255)),
  KEY `domain_id` (`domain_id`),
  KEY `admin_id` (`admin_id`)
) ENGINE=MyISAM AUTO_INCREMENT=685835441 DEFAULT CHARSET=utf8

I have exactly the same data on two different servers (both have the same my.cnf), but I get different behaviour. On one, I get a table scan, no matter what, and on the other I get the expected index use (not just with the domain_id index as in the example below, but with any index).

On the good one:

MariaDB [temp_db]> explain select * from logs where domain_id=298506;
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+
| id   | select_type | table           | type | possible_keys | key       | key_len | ref   | rows   | Extra |
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+
|    1 | SIMPLE      | logs            | ref  | domain_id     | domain_id | 9       | const | 119987 |       |
+------+-------------+-----------------+------+---------------+-----------+---------+-------+--------+-------+

On the bad one:

MariaDB [temp_db]> explain select * from logs where domain_id=298506;
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
|    1 | SIMPLE      | logs            | ALL  | NULL          | NULL | NULL    | NULL | 166719856 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+

They are both using MariaDB 10.1.29, on Debian. I've done an optimise table and analyse table on both.

Asking MariaDB to force use of the index also doesn't:

MariaDB [temp_db]> explain select * from logs force index (domain_id) where domain_id=298506;
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
|    1 | SIMPLE      | logs            | ALL  | NULL          | NULL | NULL    | NULL | 167509512 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

Is there either some explanation for what causes this behaviour, or some way that I can investigate further?

Best Answer

  • This should temporarily fix the statistics that appear to be screwed up: Do ANALYZE TABLE logs;; it will take a long time (for MyISAM, but not for InnoDB).
  • Run CHECK TABLE logs; -- the index may be corrupted. (This is a MyISAM-only thing to do.)
  • Use InnoDB, not MyISAM. InnoDB does not need ANALYZE TABLE nearly as often, and the ANALYZE runs much faster.
Related Question