MySQL Forums
Forum List  »  Partitioning

Partioning based on string column
Posted by: yogesh chawla
Date: August 07, 2009 04:17PM

Hello All,
We currently have an application where almost all searches are done against one table and every search requires two characters of a last name be entered. As this table has grown to around 10 million records the searches have been bogging down.

I was thinking a solution to this problem would be replication. However the database gets considerably more writes than reads and it seemed like replication worked better for high read, low write databases. Regardless I have it set up and working.

I would like to partition the table based on this last name field. From what I see so far, partitioning based on a string is not available. It looks like I could do a key partition, but I don't see how I would get a performance gain there since queries might still have to work over multiple partitions.

Can I partition by Last name using a range like a-c, d-f, etc? If I am required to have a numeric field for the partition, should I add a column called where last names that start with A get assigned the number 1, b->2, c->3 and do that?

Any suggestions that anyone has would be helpful. I am not a DBA and mostly work writing web apps. Unfortunately no DBAs work on the team so any assistance you can provide would be great.

Thanks,
Yogesh

Options: ReplyQuote


Subject
Views
Written By
Posted
Partioning based on string column
3327
August 07, 2009 04:17PM


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.