Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    DB partitioning for phonebook application

    Hi,

    I have a phonebook database with over few hundred million entries as follows:

    userid int(10)
    phonenumber var(12)

    where userid is user id and phonenumber is the phone number of users contact.

    I run two types of queries"

    1) find all the phonenumbers for a userid (select phonenumber from contacts where userid=1)
    2) find all the userids having a particular phonenumber in their contacts (select userid from contacts where phonenumber='1800555')

    I like to partition this huge table however am facing problem, for example, if I partition by userid, second query will be slow and vice versa.

    Can someone help with suggestions to rightly partition this table.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    what makes you believe that you need to partition the table to start with
    a few hundred million should be within the capabilities of most db engines.

    unless you have got a specific problem in mind then I'd leave it well alone.

    there may be things you could do to improve performance by tweaking the design or configuration of the server
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by tanra View Post

    I like to partition this huge table however am facing problem, for example, if I partition by userid, second query will be slow and vice versa.

    Can someone help with suggestions to rightly partition this table.
    That depends on the specific DBMS, which you chose not to indicate for some reason. But healdem is right, two indexes might be all you need.
    Last edited by n_i; 02-13-12 at 18:57.

  4. #4
    Join Date
    Oct 2011
    Posts
    10
    Database is MySQL, I should have mentioned earlier.

    The reasons why I am thinking of partitioning

    1) over a few hundred million rows in contacts table, even billion rows are possible and queries could run slow. Shouldn't partition will make it faster?

    2) Results are joined with another table which has the status of userid and phonenumber.

    So the query for getting all the relevant phonenumbers for userid 123 will be like

    select contacts.phonenumber from contacts, statustable where contacts.userid=123 and statustable.phonenumber = contacts.phonenumber and statustable.status=1
    and query for phonenumber 123456 will be like

    select contacts.userid from contacts, statustable where contacts.phonenumber=123456 and statustable.userid = contacts.userid and statustable.status=1
    Do you think MySQL without partition will be able to handle fast without partition? Also, is it possible that partition makes this slow instead of making it fast?

    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    queires could run slow.... seems to some it up.

    if you are asking will MySQL handle that sort of volume of datas
    ..the answer is yes
    will it handle it fast enough depends on the deisng of the table(s) + indexes and so on
    but you may need to modify the server setup (ie its internal settigns such as cache size) and configuration (eg more memory, as fast as possible processor and so on better disk / network connections)

    some of that is a DBA centric task, some of it is network infrastructure.

    no one here can give you an honest answer as we don't know your specific netwrok requirements, transaction volumes and so on.

    right now the best I'd suggest you do is to stuff your server with as much data as you need to prove or disprove if you even have a problem that requires considering splitting your table.


    a potential problem lies if you insert a lot of data, as with any db you may well need to rebuild indexes. this is something that any competent DBA should be able to sort out for you.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •