I am trying to optimaze a query on mysql.
Here is the table structure:
TABLE FILE:
id: int(8) primary key auto_increment
filename: varchar(1024)
The ideia is to create a navigation struture like a filebrowser.
So the user click on the first folder, and see a list of files and folders where it can click to interact with the file or click in another folder.
Let assume I know the name of the first directory is '/storage/'.
Here is the query I am using the to list the files and folders inside the '/storage/' folder:
-- List Files
SELECT FileName,ID
FROM FILE
WHERE SUBSTRING(FileName, 1, 9) = '/storage/'
AND LOCATE('/', SUBSTRING(FileName, 9+1)) = 0
UNION
-- List folders
SELECT DISTINCT(SUBSTRING(FileName, 11+1, LOCATE('/', SUBSTRING(FileName, 9+1)))) AS FileName,
0 AS ID
FROM FILE
WHERE
SUBSTRING(FileName, 1, 9) = '/storage/'
AND LOCATE('/', SUBSTRING(FileName, 9+1)) > 0
ORDER BY ID, FileName
This query is working and list the files and folders:
FILENAME ID
subdirectory/ 0
/storage/test.jpg 123
How can I optimize the query to be faster?
I already add index to the filename and id columns, but look like the index is not been used when I use the mysql functions in the query like locate, substring, etc.
My storage has 20T and the query is taking 7 seconds to finish.
EDITED
I add another column 'dirpath varchar' where I add the directory which the file is located, and I was able to get 50% improviment.
I created two triggers to populate this column:
CREATE TRIGGER TR_dirpath_INSERT_file
BEFORE INSERT
ON file FOR EACH ROW
SET NEW.dirpath = SUBSTR(new.filename, 1, LENGTH(new.filename)-LENGTH(SUBSTRING_INDEX(new.filename, '/', -1)));
CREATE TRIGGER TR_dirpath_UPDATE_file
BEFORE UPDATE
ON file FOR EACH ROW
SET NEW.dirpath = SUBSTR(new.filename, 1, LENGTH(new.filename)-LENGTH(SUBSTRING_INDEX(new.filename, '/', -1)));
The final SQL is:
-- List Files
SELECT FileName,ID
FROM FILE
WHERE dirpath = '/storage/'
UNION
-- List folders
SELECT DISTINCT(SUBSTRING(FileName, 11+1, LOCATE('/', SUBSTRING(FileName, 9+1)))) AS FileName,
0 AS ID
FROM FILE
WHERE
SUBSTRING(FileName, 1, 9) = '/storage/'
AND LOCATE('/', SUBSTRING(FileName, 9+1)) > 0
ORDER BY ID, FileName
Now I have to find out a way to improve the second part of this SQL ( List Folders ).
Best Answer
A general rule in designing MySQL schemas -- Separate pieces that will need to be split up. Do this before inserting. You are already seeing how messy it is to split the path from the filename.
When I did a similar database, I chose to have 2 tables: One for
Directories
, one forFiles
.Files
had thefilename
without the path, plus a reference (dir_id
) to a row inDirectories
.