SQL optimization

Question

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 ).

Answer 1

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 for Files. Files had the filename without the path, plus a reference (dir_id) to a row in Directories.