I've got a MySQL indexing question for everyone.

I have got a really large table (~100Million Records) in MySQL that consists of details about files. The majority of the Queries I actually do onto it involve substring procedures around the file path column.

Here's the table ddl:

CREATE TABLE `filesystem_data`.`$tablename` (
				`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` ) 

So for instance ill possess a row having a file_path like:

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

And I'll extract anyone's title (Zenshai within this example) with something similar to

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 will get a little ugly, but that is not necessarily my concern at this time.

What I would like top tips on is exactly what type of index (if any whatsoever) might help accelerate these kinds of queries about this table. Every other suggestions are welcome too.


PS. Even though table will get large there's enough space for indexes.

You can't use indices together with your current table design.

You might give a column known as USERNAME, complete it the INSERT/UPDATE trigger using the expression you utilize in SELECT, and check about this column.

P. S. Just curious, you actually have 100 mln+ files in your server?

I'd produce a small (posts, not record count) subtable that will possess the file path damaged out and saved like so:

1               Server100
1               share2
1               Home
1               Zenshai
1               My Documents

After which just index PATH_PART. Obviously when the parent table is 100 Million plus, then this is starting the vast amounts of records.