Description:
Sample Query:
SELECT * FROM `contents` WHERE (`link` LIKE '3059' OR `id` = '3059' OR `name` LIKE '3059') AND `lang_id` ='1' AND `deleted`=0 AND `disabled`=0 AND `publish_at`<'1593196791';
When I import my database and run the above query, it runs in 0.03 seconds.
The size of contents table in /var/lib/mysql/DBNAME/contents.MYD is 22M.
Now when I run "mysqlchek -r DB_NAME" , the size of contents.MYD becomes 48M, and the above query becomes at least 100 times slower and runs in 3 seconds.
- This only Happens with Mysql v8.0 , it does not happen with Mysql 5.7 or 5.6
I confirmed this bug on the following platforms:
- Mysql 8.0.20 from Mysql repo on Centos 8
- Mysql 8.0.20 from Ubuntu repo on Ubuntu 20.04 LTS , with the default config
The structure of contents table is as follows and it has 3012 records :
| contents | CREATE TABLE `contents` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`cat` int unsigned NOT NULL,
`section` varchar(100) NOT NULL,
`name` varchar(255) NOT NULL,
`author_id` int unsigned NOT NULL,
`link` varchar(255) NOT NULL,
`lang_id` int unsigned NOT NULL DEFAULT '1',
`detail` longtext NOT NULL,
`editor_offer` int NOT NULL DEFAULT '0',
`sequence` int unsigned NOT NULL DEFAULT '0',
`visit` int unsigned NOT NULL,
`visit_date` int unsigned NOT NULL,
`add_time` int unsigned NOT NULL,
`publish_at` int unsigned NOT NULL,
`deleted` tinyint unsigned NOT NULL DEFAULT '0',
`disabled` tinyint unsigned NOT NULL DEFAULT '0',
`added_by` int unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `cat` (`cat`),
KEY `section` (`section`),
KEY `deleted` (`deleted`),
KEY `disabled` (`disabled`),
KEY `lang_id` (`lang_id`),
KEY `link` (`link`),
KEY `author_id` (`author_id`),
KEY `visit` (`visit`)
) ENGINE=MyISAM AUTO_INCREMENT=3060 DEFAULT CHARSET=utf8 |
How to repeat:
1. create content table with the structure provided in the description and insert 3000 random records. ( I can share my mysqldump of the db with mysql developers )
2. dump the database to file, and import it to database.
3. check the runtime of the query provided in the description, it should run fast. also check the filesize of the content table.
4. repair your database
5. check the runtime of the query again, it will be much slower. the filesize of the content table will also be larger
