StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimizing Mysql Table Indexing for Substring Queries
text
Body
copied!<p>I have a MySQL indexing question for you guys.</p> <p>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. </p> <p>Here's the table ddl:</p> <pre><code>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 }; </code></pre> <p>So for example ill have a row with a file_path like:</p> <pre><code>'\\Server100\share2\Home\Zenshai\My Documents\' </code></pre> <p>And I'll extract the User's name (Zenshai in this example) with something like </p> <pre><code>SELECT substring_index(substring_index(fp.file_path,'\\',6),'\\',-1) as Username FROM (SELECT '\\\\Server100\\share2\\Home\\Zenshai\\My Documents\\' as file_path) fp </code></pre> <p>It gets a bit ugly, but that's not really my concern right now. </p> <p>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. </p> <p>Thanks.</p> <p>PS. Although the table gets very large there is enough space for indexes.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload