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_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 instance ill possess a row having a file_path like:
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:
FK_TO_PARENT PATH_PART 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.