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
event
(id
int(11) NOT NULL AUTO_INCREMENT,imei
char(50) NOT NULL,cradle_id
varchar(45) NOT NULL,company_id
int(10) unsigned NOT NULL,event_type
int(10) NOT NULL,lat
double NOT NULL,lon
double NOT NULL,
speed
double NOT NULL,dtime
datetime NOT NULL,force_level
double NOT NULL DEFAULT '0',duration
double NOT NULL,
force_hist
varchar(300) NOT NULL DEFAULT 'non',lr_force_hist
varchar(300) NOT NULL DEFAULT 'non',speed_hist
varchar(250) NOT NULL DEFAULT 'non',milage
double NOT NULL,filter
int(11) DEFAULT '1',filter_reason
int(11) DEFAULT NULL,road_type
int(11) DEFAULT NULL,calibration_variance
double DEFAULT '0',
reason
int(11) DEFAULT NULL,reason_other
text,
distance_adjustment
float DEFAULT NULL,max_speed
smallint(6) DEFAULT NULL,location_name
varchar(225) DEFAULT NULL,
device_id
char(17) DEFAULT NULL,utc_offset
int(11) NOT NULL DEFAULT '0',duplicated
int(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.