Re: Possible to Partition Strings ?

[Phil Hildebrand]

Your searches sound like they might be better suited for a full text search.

If your searches are using like to match words or strings within the primary key (file name), then you'll always do a full scan of the primary key, and that could possibly even be slower in a partitioned scenario given that each partition must be opened.

If, however, most of the searches include the date, or size ( where name like x and size > x, or date between x and y ), then they would benefit from a range partition on size and/or date.

If your searches usually try to match the beginning of file name ( like 'this%' ), then you would likely still have an issue with partitioning from a query perspective, because I doubt the optimizer would be able to use one of the allowed functions to 'prune' out the unnecessary partitions in that case, because optimizer would still have to scan each of the partitions for the match.

Using partition by key (filename) would allow you to partition on the string filename, but MySQL will apply something akin to the password function or md5 hash to turn the partition key into an integer, and so your like 'xxx%' queries would still have to scan each partition.

There might be some benefit depending on your storage engine with inserts and updates, but probably not from a query perspective if most of the queries search for parts of the filename.