0

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
1
  • MyISAM or InnoDB?
    – Rick James
    Mar 23, 2015 at 15:45

1 Answer 1

1

INDEX(type, stamp) should speed up "Select 1" significantly, even without LOCK IN SHARE MODE.

(Please provide SHOW CREATE TABLE for further discussion.)

Edit

Even though you have type and stamp in indexes, having these two fields in this order will be optimal for "Select 1": INDEX(type, stamp). This is because the optimizer can drill into the BTree to the end of the "type='string'" values and find the MAX. Instantly. Only one rows is fetched. And it is only fetched in the index.

With PRIMARY KEY(type, other stuff) it needs to scan 6M rows to find the answer. COM_KEY is of no help (for this SELECT).

After this query is sped up, the other queries may be less of a problem.

7
  • Rick, There is a primary key on (type) but not on (type, stamp). There is no useful key on (stamp).
    – Uday
    Mar 23, 2015 at 16:07
  • Before reacting to that, I need to know the Engine, size of rows, indexes, etc. Please provide SHOW CREATE TABLE. If you have 6M rows with type='string', then it is impossible for the PRIMARY KEY to be '(type)'. A PK is UNIQUE.
    – Rick James
    Mar 23, 2015 at 16:57
  • (Updated my Answer)
    – Rick James
    Mar 23, 2015 at 17:22
  • Rick, Thanks for your time. My SELECT 1 took 3.5 Hours to completed. My main question is not how to speed this up, instead why this SELECT 1 is that much slow ?
    – Uday
    Mar 23, 2015 at 17:26
  • I thought the second paragraph of my Edit answered "why". You can use EXPLAIN SELECT ... to see that it is probably using the PRIMARY KEY for the query. If you have a new enough version of MySQL, do ALTER TABLE T1 ADD INDEX(type, stamp) ALGORITHM=INPLACE; to add the index with minimal impact.
    – Rick James
    Mar 23, 2015 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.