Recently I upgraded MySQL on my server from newest 5.7.x to newest 8.0.x and I imported my entire database with multi-indexes that I created when I was using MySQL 5.7.x. These indexes optimize SELECT queries that run with WHERE clause on few columns (both INT and VARCHAR columns) on a table that has ~ 8 million records (no JOINs) and they used to work very well, but strangly after the upgrade they do NOT work at all... some queries on this table are MUCH slower :-( We don't want to stay with MySQL 5.7 as it will be discontinued this year. Have you had any similar problem as I have? I tweaked my.ini the same way I did in MySQL 5.7 by increasing buffer sizes, etc. Please help. I will provide more details later if it's necessary like the table structure, example queries and my.ini . Thank you very much.
EDIT: TABLE AND INDEXES DEFINITIONS:
CREATE TABLE IF NOT EXISTS
idint(11) NOT NULL AUTO_INCREMENT,
imeichar(50) NOT NULL,
cradle_idvarchar(45) NOT NULL,
company_idint(10) unsigned NOT NULL,
event_typeint(10) NOT NULL,
latdouble NOT NULL,
londouble NOT NULL,
speeddouble NOT NULL,
dtimedatetime NOT NULL,
force_leveldouble NOT NULL DEFAULT '0',
durationdouble NOT NULL,
force_histvarchar(300) NOT NULL DEFAULT 'non',
lr_force_histvarchar(300) NOT NULL DEFAULT 'non',
speed_histvarchar(250) NOT NULL DEFAULT 'non',
milagedouble NOT NULL,
filterint(11) DEFAULT '1',
filter_reasonint(11) DEFAULT NULL,
road_typeint(11) DEFAULT NULL,
calibration_variancedouble DEFAULT '0',
reasonint(11) DEFAULT NULL,
distance_adjustmentfloat DEFAULT NULL,
max_speedsmallint(6) DEFAULT NULL,
location_namevarchar(225) DEFAULT NULL,
device_idchar(17) DEFAULT NULL,
utc_offsetint(11) NOT NULL DEFAULT '0',
duplicatedint(11) DEFAULT '0',
PRIMARY KEY (id), KEY event_index (imei,dtime,event_type,filter), KEY event_index2 (device_id,dtime,event_type,filter), KEY event_index3 (filter) ) ENGINE=InnoDB AUTO_INCREMENT=40026132 DEFAULT CHARSET=utf8;
SLOW QUERY LOG:
Query_time: 2.986858 Lock_time: 0.000001 Rows_sent: 1 Rows_examined: 42355 SET timestamp=1680260078; select * from event where ( ( imei = "0e69eb4e-9999-494c-873d-9416d3569ab0" and dtime > "2021-12-16 13:23:50" ) or ( imei = "ae7f20e8-34d8-48e4-99a1-ff6197bc16ff" and dtime > "2021-11-17 14:44:05" and dtime < "2021-12-16 13:23:49" ) or ( imei = "bd415fb6-9a36-493f-ba65-fd54db7e6fce" and dtime > "2021-01-21 08:46:21" and dtime < "2021-11-17 14:44:04" ) or ( imei = "357577093449854" and dtime > "2018-10-07 19:45:26" and dtime < "2021-01-21 08:46:20" ) or ( imei = "359751080340701" and dtime > "2017-07-31 11:58:57" and dtime < "2018-10-07 19:45:25" ) or ( imei = "358979073825062" and dtime > "2016-10-11 09:02:37" and dtime < "2017-07-31 11:58:56" ) ) and filter != 3 order by dtime desc, id desc limit 1;
EXPLAIN of the above query on MySQL 8.0:
1 SIMPLE event range event_index3,event_index event_index 155 82900 50.00 Using index condition; Using MRR; Using filesort
EXPLAIN of the above query on MySQL 5.7:
1 SIMPLE event range event_index3,event_index event_index 155 82900 50.00 Using index condition; Using filesort
The query above takes 1.167099 second on MySQL 8.0, but is super fast on MySQL 5.7, only 0.1 second on MySQL 5.7
This happens with default MySQL 8.0 settings in my.ini and also with tweaked my.ini (increased buffer sizes). Both of the databases have KEYRING plugin enabled.
I tried turning off different indexes (setting them INVISIBLE) on MySQL 8.0 but it didn't speed up the query at all.
The server has NVME disk that reads with speed ~ 12 GB/s.
There are about 8 million records in that table. Unfortunatelly I cannot attach entire dump of data here, because it would violate privacy of clients. I can create some dummy data though if you need it to help me. Thank you.