Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: DB Design Question

    Let me give a little background to my question. I have a large amount of data, currently at around 15,000 Rows 60MBs, and it grows at the rate of about 4,000 rows (16MBs) a year. When the database was smaller I used to have just one table with everything in it. But at around 8,000 rows (32MBs) it was running too slow, even simple select statments took a long time to complete. So I divided the data up, made catagories and gave each catagory its own table, and everything worked fine.
    The way it worked is that each row is a member of a catagory (catagories are defined in a seperate table), but now I have been asked to make it so that each row can be a member of more than one catagory.
    Normally I would have made a table connect the catagoryID to the articleID from the one main table, but because I have all the articles spread out over different tables that won't work. I can't combine all the rows into one table again because it is too slow. I could add a table field in the connecting table that has the name of the table that contains the actual data, but that would require 2 sql statements to get the final data which i'd rather not do if i can avoid it. Does any one have any suggestions as to how i can design my tables so the information can be called with one sql statement without having to have all the rows in one table?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "without having to have all the rows in one table"?

    nope, sorry

    15,000 rows is peanuts

    15,000,000 rows is medium size

    15,000,000,000 rows is large

    probably you did not have the right indexes defined for your queries, which is why it was slow

    splitting the data into separate tables was the wrong decision

    unless you can bring them back together into one table, then you will have real trouble with your many-to-many category linkage

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    Bringing them all back together isn't a problem if I can solve the speed issue. From past conversations I was told that my table size (60MB right now) was too much. Can you point me in the right direction as far as indexing so i can see if that will solve my problem? I currently just have a Primary key.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there should be an index on every column in every table that is not already declared as a primary key and that is used in a join condition or a search condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    8
    Will try that thanks for the help

Posting Permissions

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