Mysql table indexing optimization for substring queries

Question

I have a MySQL indexing question for you guys.

I have a very large table (~ 100 million records) in MySQL that contains file information. Most of the queries I make on it include substring operations in the file path column.

Here's the ddl table:

CREATE TABLE `filesystem_data`.`$tablename` (
                `file_id` INT( 14 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
                `file_name` VARCHAR( 256 ) NOT NULL ,
                `file_share_name` VARCHAR ( 100 ) NOT NULL,
                `file_path` VARCHAR( 900 ) NOT NULL ,
                `file_size` BIGINT( 14 ) NOT NULL ,
                `file_tier` TINYINT(1) UNSIGNED NULL, 
                `file_last_access` DATETIME NOT NULL ,
                `file_last_change` DATETIME NOT NULL ,
                `file_creation` DATETIME NOT NULL ,
                `file_extension` VARCHAR( 50 ) NULL ,
                INDEX ( `file_path`, `file_share_name` ) 
                ) ENGINE = MYISAM 
             };

So, for example, the patient has a line with file_game like:

'\\Server100\share2\Home\Zenshai\My Documents\'

And I will extract the username (Zenshai in this example) with something like

SELECT substring_index(substring_index(fp.file_path,'\\',6),'\\',-1) as Username
FROM (SELECT '\\\\Server100\\share2\\Home\\Zenshai\\My Documents\\' as file_path) fp

It gets a little ugly, but actually it's not my problem.

I would like some advice on which index (if any) can help speed up these types of queries in this table. Any other suggestions are welcome.

Thanks.

PS. Although the table becomes very large, there is enough space for indexes.

Answer 1

.

USERNAME, INSERT/UPDATE , SELECT, .

. S. , 100 mln+ ?

Answer 2

(, ) , :

FK_TO_PARENT    PATH_PART
1               Server100
1               share2
1               Home
1               Zenshai
1               My Documents

PATH_PART. , 100 , .