My Table has 25 Million rows total and 6 million rows satisfying below where clause.
Select 1:
select max(stamp) from Table where type = "string" LOCK IN SHARE MODE ; 6000+ seconds
Select 2:
select * from Table where type = "string" LOCK IN SHARE MODE ; 3000+ seconds
Select 3:
select * from Table where type = "string" LOCK IN SHARE MODE ; 2000+ seconds
If I try the SELECTS 2 and 3 without LOCK IN SHARE MODE WITH LIMIT
, I am getting the data almost immediately.
All three queries have locked d table in shared mode. I don't understand why these are taking too much time when we use LOCK IN SHARE MODE
.
Engine is Innodb,
Primary key is a composite key on multiple fields all are varchars
Create Table: CREATE TABLE `T1` (
`TYPE` varchar(25) NOT NULL,
`FIELD2` varchar(50) NOT NULL,
`FIELD3` varchar(130) NOT NULL,
`FIELD4` varchar(100) NOT NULL,
`FIELD5` varchar(30) DEFAULT NULL,
`FIELD6` varchar(20) DEFAULT NULL,
`FIELD7` varchar(110) NOT NULL,
.....
.....
.....
`STAMP` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE
CURRENT_TIMESTAMP(3),
PRIMARY KEY (`TYPE`,`FEILD2`,`FEILD3`,`FEILD4`,`FEILD5`,`FEILD6`,`FEILD7`),
KEY `COM_KEY` (`FIELD9`,`FIELD9`,`STAMP`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1