这个mysql插件可以加速吗？

Question

This insert is a part of my application that allows the user to take photos and store them in the database. There's a pretty specific naming convention for all the filenames being stored in the database, which is why my insert has such intense case statements. I'm guessing this has something to do with my slow performance. When inserting new shots for a patient with very few, or no, shots stored in the database, my application runs much faster. However, if the patient I'm adding shots to has already has a lot stored in the database, the performance is much slower.

Is it possible to speed up this insert statement? If so, how? My app is coded in PHP and I'm using phpMyAdmin and mysqli

INSERT INTO shot (ID, VISIT_ID, PATIENT_ID ,IMG_FILENAME ,SHRUNK_IMG_FILENAME ,SUBDIR ,SUBSUBDIR ,IMG_FILE_FORMAT ,EYE)
SELECT (
        SELECT (max(ID) + 1)  FROM SHOT
        )
      ,(
        SELECT max(ID)  FROM visit  WHERE visit.patient_id = " . $_SESSION['id'] ."
        )
    ," . $_SESSION['id'] . "
    ,(
        SELECT CASE 
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100
                    THEN CONCAT ( '00000000' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 1000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 99
                    THEN CONCAT ( '0000000' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 10000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 999
                    THEN CONCAT ( '000000' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 9999
                    THEN CONCAT ( '00000' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 1000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 99999
                    THEN CONCAT ( '0000' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 10000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 999999
                    THEN CONCAT ( '000' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 9999999
                    THEN CONCAT ( '00' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 1000000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 99999999
                    THEN CONCAT ( '0' ,(MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 10000000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 999999999
                    THEN CONCAT ( (MAX(shot.ID) + 1) ,'r.jpg' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100000000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 9999999999
                    THEN CONCAT ( (MAX(shot.ID) + 1) ,'r.jpg' )
            END
            )
    ,(
        SELECT CASE 
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100
                    THEN CONCAT ( '00000000' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 1000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 99
                    THEN CONCAT ( '0000000' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 10000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 999
                    THEN CONCAT ( '000000' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 9999
                    THEN CONCAT ( '00000' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 1000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 99999
                    THEN CONCAT ( '0000' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 10000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 999999
                    THEN CONCAT ( '000' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 9999999
                    THEN CONCAT ( '00' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 1000000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 99999999
                    THEN CONCAT ( '0' ,(MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 10000000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 999999999
                    THEN CONCAT ( (MAX(shot.ID) + 1) ,'s.bmp' )
            WHEN ( SELECT MAX(shot.ID) + 1 FROM shot ) < 100000000000
                    AND ( SELECT MAX(shot.ID) + 1 FROM SHOT ) > 9999999999
                    THEN CONCAT ( (MAX(shot.ID) + 1) ,'s.bmp' )
            END
        )
    ,$SUB
    ,$ISUB
    ,'124'
    ,'0'
FROM SHOT

Answer 1

It will propably be faster but at least a lot shorter.. Why use cases when you can use LPAD?

INSERT INTO shot (ID, VISIT_ID, PATIENT_ID ,IMG_FILENAME ,SHRUNK_IMG_FILENAME ,SUBDIR ,SUBSUBDIR ,IMG_FILE_FORMAT ,EYE)
SELECT 
    x.ID + 1,
    (
        SELECT max(ID)  FROM visit  WHERE visit.patient_id = " . $_SESSION['id'] ."
    ),
    " . $_SESSION['id'] . ",
    CONCAT (LPAD(MAX(x.ID) + 1, 10, 0), 'r.jpg'),
    CONCAT (LPAD(MAX(x.ID) + 1, 10, 0), 's.bmp'),
    $SUB,
    $ISUB,
    '124',
    '0'
FROM SHOT x