dcsimg
www.webdeveloper.com
Results 1 to 3 of 3

Thread: Database Design mySQL one or many table(s) for performance

  1. #1
    Join Date
    May 2010
    Posts
    23

    Exclamation Database Design mySQL one or many table(s) for performance

    Hello Everybody,

    happy new year!!!

    Summary: I will allow users to upload following files - PDF/Videos/Photos. All files uploaded by the user are shown on a profile page. All uploaded files can be searched by name or tags and file type. I store file paths in database not the actual file.

    Questions
    At moment i will store all files details in one mySQL table, myisam engine used for fulltext and tag searching and also faster to select data.

    Say if their were over 10+ million records stored in this one table, the search would be eventually get slow. i am thinking of breaking the single table into three separate tables e.g a table for pdf/photos/videos in this case if the users want to search all file type, i do union all search. would this be a better approach ?

    the drawback would be if I add a new file type at later stage i would have to create a new table , for a example allow users to upload zip files.



    thanks

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,637
    I would leave them in one table but add an indexed column for the file type (which could simply be an integer pointing to a separate file_type table primary key). Then if you want to search for, say, PDF files, your query's WHERE clause would first check the file_type column to narrow the search before checking text fields.

    If you're truly going to have millions of records to search and it involves a lot of text searching, it might then be time to consider something like Sphinx Search or MongoDB for an approach optimized for text searching (at the price of less efficient writing).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    here's what I thought when I read your post (in red):

    Summary: I will allow users to upload following files - PDF/Videos/Photosplease dont tell me you're storing them in the database as blobs. All files uploaded by the user are shown on a profile page 1 user can have many files, store user_ID in the file table . All uploaded files can be searched by name 1:1 with file or tags many to many, now you need a tags table and FileTags table and file type1:1, maybe 1 to many if an excess of file types exists . I store file paths in database not the actual file. I wonder if he learned this the hard way also...

    Questions
    At moment i will store all files details in one mySQL table, myisam engine used for fulltext and tag searching and also faster to select data. can't cascade delete a user's files when the user is deleted... better handle gc on the application level

    Say if their were over 10+ million records stored in this one table, the search would be eventually get slow. what are you searching against... the name? just use CI substrings? i am thinking of breaking the single table into three separate tables e.g a table for pdf/photos/videos in this case if the users want to search all file type, i do union all search. would this be a better approach ? [COLOR="Sienna]this is a terrible idea, use a byte field or bit field... why would an entirely new table have a faster index?[/COLOR]

    the drawback would be if I add a new file type at later stage i would have to create a new table , for a example allow users to upload zip files. ... I think you're missing the idea of 1N,2N, and 3N all together
    Here's what I would make it look like:



    See how I cut a corner by using ENUM(), that's OK when you can say "the list of possibilities is FINAL, never will another type appear". case closed. Since that's not the case, we can add in NogDog's suggestion:

    to be short sighted:
    ENUM can cut corners for a 1:many relationship (get rid of 1 table).
    SET can cut corners for a many:many relationship (get rid of 2 tables).

    but these 1 or 2 tables could contain many more relationships that ENUM and SET will not capture, and you might end up having to rewrite a lot of the code base if you accidentally cut a corner you shouldn't have.

    I think this is acceptable:


    Now some thoughts about this:
    I don't like having all of the FKs being stored in a myISAM table, but that's exactly what you're wanting to do. For the sake of full text search.

    • What exactly are you searching against?


    </p>



    I suppose you could make the FileType PK the fileType VARCHAR(45) field instead, and change the FK_File2FileType constraint also. But it begs the question, why even bother making the table if the FK is the foreign PK and no other fields exist in the table. More specifically, why does anyone make a foreign key to a table with only 1 field.

    I guess the answer is- it makes more sense, because once 64 file types (64 bits) exist, the alternative is a binary string (blob).

    Moving it to a new table enforces having unique file types stored while allowing for easy access and more than 64 types. If the PK is in fact the name itself, you may never need to join on the table at all (ever) and all of this is actually just a formality to beat the 64 bit limit on integers. Just a thought. If you have more than 18446744073709551615 file types you're screwed.

    My check is in the mail right?
    Last edited by eval(BadCode); 01-07-2012 at 11:15 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles