Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.1.9
-
None
-
Linux debian, Windows 2008 R2
Description
SELECT 144 as f_id, M_ID, m_video,m_audio,m_album, m_topic, M_Linear, M_NUM, M_TITLE, M_Content, M_User, M_DATE FROM forum_messages WHERE m_visible=1 and m_forum =144 and m_id>8292603 and m_topic=759799 order by m_id asc
running fastly on 10.0.22
running fastly most of the time on 10.1.9.
1/10000 I see an unacceaptable slow (about 2-5 minutes or more). the slowest state is `sending data`.
table:
CREATE TABLE `forum_messages` (
|
`m_forum` smallint(6) NOT NULL DEFAULT '0',
|
`m_id` int(11) NOT NULL AUTO_INCREMENT,
|
`m_topic` mediumint(9) NOT NULL DEFAULT '0',
|
`m_linear` varchar(1200) CHARACTER SET utf8 NOT NULL,
|
`m_title` varchar(60) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
|
`m_Content` mediumtext CHARACTER SET utf8mb4 NOT NULL,
|
`m_user` mediumint(9) NOT NULL DEFAULT '0',
|
`m_num` smallint(6) NOT NULL DEFAULT '0',
|
`m_date` datetime NOT NULL DEFAULT '1987-11-13 00:00:00',
|
`m_ip` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
|
`m_visible` tinyint(4) NOT NULL DEFAULT '1',
|
`m_video` tinyint(4) NOT NULL,
|
`m_audio` tinyint(4) NOT NULL,
|
`m_album` tinyint(4) NOT NULL,
|
`m_anon_user` int(11) NOT NULL DEFAULT '0',
|
PRIMARY KEY (`m_id`),
|
KEY `topic num` (`m_topic`,`m_num`),
|
KEY `forum-id` (`m_forum`,`m_id`),
|
KEY `user` (`m_user`,`m_date`,`m_forum`) USING BTREE,
|
KEY `Normal Queries` (`m_topic`,`m_linear`(200),`m_visible`),
|
KEY `stat` (`m_date`,`m_forum`,`m_user`)
|
) ENGINE=InnoDB AUTO_INCREMENT=8256287 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
|
explain:
1 SIMPLE forum_messages ref PRIMARY,topic num,forum-id,Normal Queries topic num 3 const 48 Using index condition; Using where; Using filesort
|
Optimizer switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
|
what I need to upload to help solving this issue? I can see it on three servers with the same data.