SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    one big database or several small ones.

    hi

    i have a quick question here.


    i have a few thousand or almost hunderd thousand names.

    user would be able to search the names.

    my question is;

    is it better to have one big database

    or

    several databases containing the same first leter of alphabets?

    thank you for yuor opinion.

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Australia
    Posts
    122
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One database several tables

  3. #3
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    thank you for your reply.

    oh..........

    yea.

    actually i meant to that.

    so, it would actually be

    one database with one table
    or
    one database with a-z table
    thanks


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no, it would be better to have one database, one table

    consider: what is your sql to count how many entries you have for each letter?

    and anyway, 100,000 is small

    100,000,000 is medium

    100,000,000,000 is large

    do not worry about 100,000, mysql can handle it easily, assuming you index the search columns properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2005
    Location
    Illinois, USA
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might also want to make an extra column with the first letter then .

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    good idea (shows you're thinking), but that extra column might not really work

    the selectivity might not be good enough to ensure that an index will always be used on it, especially if searches are for specific names and not just always all names with that first letter
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you for all of your suggestions.

    so,

    i would have one database and one table...

    thank you very much.

    ermm....

    you mentioned indexing...

    do you any good article for me to read about indexing?
    ..........

    newbie here..........

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what did you think of the information about indexing on the mysql site?

    did you like it? did you understand it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by defiance
    do you any good article for me to read about indexing?
    This article is old, but explains the concept nicely.

    http://www.sitepoint.com/article/opt...ql-application
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  10. #10
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i read it...

    but didnt give me much stuff.

    i mean,

    that article will be good if i do have some background knowledge abt indexes.

    but, as a total newb, i really still don get what IS index....

    well may be it is just me.

    but thank you so much for the recoomendation...

    have a nice day

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, i'll give you a simple example

    grab your telephone book, white pages

    it's in sequence by last name, then first name, then address, right?

    okay, i want you to give me a list of all the people that live between 100 Main Street and 200 Main Street

    please post back here when you have the answer

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i am in school now, cant find any phone book around.

    i will post here tonight again.



    thanks for the reply though......

  13. #13
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I Added something like that to my Db When I started it as it relates to Artists and groups I needed to handle things like 3rd edge = thi and say 411 = fou
    I decided to use it only for the sorting and have a max of three charactors for better sorting which seems to work nicly in my main Db.
    I have it on my site as well as both db's which are synced where need be but havent had a use for it as yet but its available should I need to do things more in the way I do in my main db.
    Not too sure about the indexing for the above as I don't search on it any advice r937.

    thanks

    mick

    Quote Originally Posted by r937
    good idea (shows you're thinking), but that extra column might not really work

    the selectivity might not be good enough to ensure that an index will always be used on it, especially if searches are for specific names and not just always all names with that first letter
    Be Proud, Be Loud, Be Heard.
    Systems designed by me
    Battle Of The Bands
    http://www.database-dreams.co.uk/Com...p?act=artchart
    Worldwide Music Charts
    http://www.database-dreams.co.uk/Com...ex.php?act=RCH
    UK Charts history (Started 9 Setember 01)
    http://www.database-dreams.co.uk/Com...php?act=charts

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    mick, are you typing your posts one-handed (or one-thumbed) into a cell phone or something?

    because the sentences are sort of run-on and hard to understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    mick, are you typing your posts one-handed (or one-thumbed) into a cell phone or something?

    because the sentences are sort of run-on and hard to understand
    Sorry r937 always had problems putting things into words hope you understand.
    Be Proud, Be Loud, Be Heard.
    Systems designed by me
    Battle Of The Bands
    http://www.database-dreams.co.uk/Com...p?act=artchart
    Worldwide Music Charts
    http://www.database-dreams.co.uk/Com...ex.php?act=RCH
    UK Charts history (Started 9 Setember 01)
    http://www.database-dreams.co.uk/Com...php?act=charts

  16. #16
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    i am back...

    i cant find any phone book ard here...

    i have SBC yellow pages...

    but they list business..

    so the list of ppl that you asked me to find, let me just make it up alright?

    1. John Kean | 100 Main St
    2. Honey Dew | 101 Main St
    3. David Star | 102 Main St
    4. Ryan Sean | 103 Main St
    5. Monte Stephen | 104 Main St
    6. Chapman James | 105 Main St
    7. Steven Shore | 106 Main St
    8. Chuck Smith | 107 Main St
    9. Tom Cason | 108 Main St
    10. Eric Shore | 109 Main St


    is this data too little to be indexed??

    anyhoo

    thank you very much

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i don't think you understood what i was getting at

    in order to find those people, you had to read the entire phone book!

    here's another example

    which of these authors wrote their works in german? --
    http://www.kirjasto.sci.fi/indeksi.htm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    i think i am getting you now..

    so, in order to find out which author who wrote in German, i would need to go to each individual page and find out in their "selected works"

    thus

    i can relate this to indexing.

    now, lets relate this finding author in indexing.

    ==========================================

    so i would have to index the field "Selected Works".

    but i still don actually get the meaning of indexing,

    taken from the mentioned article "If we create an index on the “name” column, MySQL will automatically order this index alphabetically"

    so once we make an index in the "selected works" mysql would order them alphabetiaclly.


    "So if I want to get the value of peopleid when the name is Mike (SELECT peopleid FROM people WHERE name='Mike', MySQL can look in the name index for Mike, jump directly to the correct row in the data file, and return the correct value of peopleid (999). MySQL only has to look at one row to get the result. "

    so when we are indexing, we are actually asking mysql to 'remember' or 'cache' the field and its values, am i right?

    like during an open book exam, chapter 12 is being tested.
    so you need to 'remember' or 'learn' or 'cache' chapter 12 first to be able to do the exam in much shorter time because you have read the chapter and know where is the answer for the given question, am i right?

    whereas

    if you havenot 'read', 'remember', 'learn', 'cache' chapter 12, during the exam you would need to actually 'read' first page of the chapter until the end to find the answer, and that would take much longer time.


    am i right about indexing in relation to my chapter 12 exam, chapter 12 being the field that we are searching???


    thank you so mcuh.

  19. #19
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not really caching, it is more like a cross-reference table.

    Think about an index in a book. That is pretty much exactly the way a database index works. Surely one of your textbooks has an index, but if none do look at a a cookbook.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the index on the white pages phone book that i was trying to get you to understand would be a list of people in sequence by their street address, not by their lastname/firstname

    there actually are books like this, but they are usually available only in public libraries

    thus, you could jump to the page in that index that starts with M, scan ahead until you get to Main Street, then read the people's names who live between 100 and 200 Main Street

    to get the same answer from the ordinary white pages phone book, you have to read every single person in the entire phone book (a few hundred thousand entries? a few million? i guess it depends on the size of your city), check each one to see if that person has an address between 100 and 200 Main Street, write down any that you find, and a couple of years later, when you're finished, you would still have to sort the ones you found into the right order

    that's the equivalent of a "table scan" where the database has to read the entire table to find the rows you want

    with an index, it can just "jump to" the Ms in the index, and scan through the Main Street addresses real quick, and voila, they're also in the right order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Guru defiance's Avatar
    Join Date
    Oct 2004
    Location
    United states
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oookkkk............
    i understand now...............


    ermm....

    thanks alot...


    i ll post questions if i have any ques then

    thanks once again


Bookmarks

Posting Permissions

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