MySQL Forums
Forum List  »  Partitioning

Re: Possible to Partition Strings ?
Posted by: Phil Hildebrand
Date: April 22, 2008 08:13PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4212
April 22, 2008 04:47PM
Re: Possible to Partition Strings ?
3300
April 22, 2008 08:13PM
2898
April 23, 2008 03:30PM
2732
April 23, 2008 04:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.