0

I have 2 tables:

Create table Files (
   FileId BigInt unsigned not NULL auto_increment,
   FileName varchar(255) not NULL,
   PRIMARY KEY (FileID),
   UNIQUE KEY FileName (FileName)
);
Create table FileRevisions(
   fileRevId BigInt unsigned not NULL auto_increment, 
   FileID BigInt unsigned not NULL, 
   RevisionName varchar(255) Not NULL, 
   deletionFlag boolean default 0,
   PRIMARY KEY (FileID),
   KEY FileRev_FileID_FK_idx(FileId),
   CONSTRAINT FileRev_FileId_FK FOREIGN KEY (FileID)
     REFERENCE Files(FileID) 
     ON DELETE CASCADE
     ON UPDATE CASCADE
);

I have the following code in ADD_FILE(File_Name, Revision_Name) procedure:

Create Procedure Add_File(File_Name varchar(255), Revision_Name varchar(255))
BEGIN
    declare file_id;
    Insert into files(FileName) values (File_Name) 
                  ON DUPLICATE KEY UPDATE FileId=LAST_INSERT_ID(FileId);
    set file_id = LAST_INSERT_ID();
    set @rowNum = 0;
    update FileRevisions set deletionFlag=true where fileRevId in 
    (
     SELECT fileRevId from
     (
       select @rowNum:=@rowNum+1 as rowNumber, fileRevId from FileRevisions 
       Where FileId = file_id and deletionFlag=false order by fileRevId 
     ) tmp where rowNumber>=5
    );
    Insert Into FileRevisions (fileId,RevisionName) values (file_id,Revision_Name);
END;

When I call the procedure and have approx. million records, it take about 1 second to run. from tests I figured the update is the slowly parts, (the inserts are fast) I tried to run the inner select queries and it was also fast, the updtae was the problem, even where the inner select return no rows...

Why is the update so slow? is there a faster way to do the update?

0

Don't use IN ( SELECT ... ). But it seems to be necessary due to the @row_num computation.

Don't use @row_num computation if you can do the equivalent with ORDER BY ... LIMIT ....

What are you trying to do? Set delete_flag=true for all but the latest 5 rows? "Latest" is defined by fileRevId?

"Million records"? In which table? Or do you mean that you need to call the proc a million times?

In checking for 5 rows, are there cases where you have thousands? I ask because 5+ rows can possibly be optimized one way, but 1000+ rows may need a different optimization.

Please provide SHOW CREATE TABLE FileRevisions, I need to check what composite indexes you have.

Update

The schema, as you have it, does not allow for more than one revision per FileId -- because of PRIMARY KEY(FileId) on FileRevisions. Also AUTO_INCREMENT demands to be the first column in some index. So, I recommend

 PRIMARY KEY (FileID),
 KEY FileRev_FileID_FK_idx(FileId),

-->

 PRIMARY KEY (FileID, fileRevId),
 INDEX(fileRevId)
  • I edited the question, The create table was added... – SHR Jan 3 '18 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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