0

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?

8
  • 1
    How many rows are total and selected by this query on each server separately? i.e. show results for select count(*), count(distinct domain_id), sum(domain_id=298506) from logs for each server.
    – Akina
    Mar 6 '19 at 9:11
  • 1
    Are you sure that there is an index on (domain_id) on the "bad" server/table? possible_keys: NULL suggests there isn't. Mar 6 '19 at 9:23
  • Yes, the indexes (the entire create table, it was from a mysqldump snapshot, and I've verified) are exactly the same on both.
    – Tony Meyer
    Mar 7 '19 at 7:09
  • @akina bad server: count(*)=167122005,count(d di)=160512,sum(d id=298506)=97077. Good server: count(*)=157920128, c(d di)=154300, sum(d id)=96866. They have diverged a bit (the bad server is in production, the good one is a snapshot elsewhere), but the problem existed when they were identical as well.
    – Tony Meyer
    Mar 7 '19 at 7:14
  • Does forcing the KEY `domain_id` (`domain_id`) index alters explain on "bad" server to a form which you see on "good" one? explain select * from logs force index (domain_id) where domain_id=298506;
    – Akina
    Mar 7 '19 at 7:48
1
  • 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.
1
  • As mentioned in the post, I've already tried analyse and it didn't help. I've checked the table as well.
    – Tony Meyer
    Mar 13 '19 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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