I have a MySQL indexing question for you guys.

我有一个MySQL索引的问题。

I've got a very large table (~100Million Records) in MySQL that contains information about files. Most of the Queries I do on it involve substring operations on the file path column.

我在MySQL中有一个非常大的表(大约1亿个记录)，其中包含关于文件的信息。我在它上做的大多数查询都涉及文件路径列上的子字符串操作。

Here's the table ddl:

这是表的ddl:

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 ill have a row with a file_path like:

例如，我有一个带有file_path的行:

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

And I'll extract the User's name (Zenshai in this example) with something like

我将用类似的方法提取用户名(本例中为Zenshai)

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 bit ugly, but that's not really my concern right now.

它变得有点丑陋，但这不是我现在真正关心的。

What I'd like some advice on is what kind of index (if any at all) can help speed up these types of queries on this table. Any other suggestions are welcome too.

我想要一些建议是什么样的索引(如果有的话)可以帮助加快这个表上的这些查询类型。其他建议也欢迎。

Thanks.

谢谢。

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

虽然表变得很大，但是有足够的空间来存放索引。

2 个解决方案

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