SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Big tables versus many smaller tables

    Hey Sitepoint,

    Just a generic question about tables.

    If I had a table that 10 million entries in it, and there was a field in the table called "category" which housed a unique number from another table called categories, would it be faster for selecting records from this table via specific categories if the table was split apart into multiple tables via category?

    So say something like this is the original.

    Code:
    item_table[]
     - item_id, category_id, item_name
    Then instead we create a bunch of these instead

    Code:
    item_table_category_id[]
     - item_id, item_name
    I'm thinking it might be faster to split the tables apart because it has less data to sort through, but then again it might be slower if you wanted to select data from multiple tables at the same time?

    Any ideas?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,499
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    One table, and the right indexes

  3. #3
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    One table, and the right indexes
    Could you please clarify for me what the "right" indexes means?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wh33t View Post
    Could you please clarify for me what the "right" indexes means?
    that depends on the query

    for "selecting records from this table via specific categories" the right index would be on category_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that depends on the query

    for "selecting records from this table via specific categories" the right index would be on category_id
    So is that something I set in the database? Or do you just mean when writing the query?

    Something like

    Code:
    select count(*) from listings where category_id = "23"

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,499
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    In the database.
    You can create the indexes when you create the table (see MySQL :: MySQL 5.5 Reference Manual :: 12.1.14 CREATE TABLE Syntax), or you can add them later (see MySQL :: MySQL 5.5 Reference Manual :: 12.1.11 CREATE INDEX Syntax)

  7. #7
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Quote Originally Posted by guido2004 View Post
    In the database.
    You can create the indexes when you create the table (see MySQL :: MySQL 5.5 Reference Manual :: 12.1.14 CREATE TABLE Syntax), or you can add them later (see MySQL :: MySQL 5.5 Reference Manual :: 12.1.11 CREATE INDEX Syntax)
    Please excuse my extreme novice-ness but I have no clue what most of that page says.

    Could you please explain to me what an Index in a table does and how it differs from a Primary Key?

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,813
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A primary key is one sort of index - you can add others so as to make the data more quickly available by other fields. Without an index on the field you are searching on it needs to go through all the records to find the ones that match. With the index it can basically access them directly via that field because the index lists all the values in order for that field and points to the corresponding records.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Quote Originally Posted by felgall View Post
    A primary key is one sort of index - you can add others so as to make the data more quickly available by other fields. Without an index on the field you are searching on it needs to go through all the records to find the ones that match. With the index it can basically access them directly via that field because the index lists all the values in order for that field and points to the corresponding records.
    I see, I had no idea that's what an index did. Why wouldn't I just make every field an index then lol?

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,075
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wh33t View Post
    I see, I had no idea that's what an index did. Why wouldn't I just make every field an index then lol?
    Because indices slow down UPDATE and INSERT queries. This is because MySQL uses B-TREEs for the indices, and needs to make sure these trees remain balanced for optimal performance. It may well be that one UPDATE/INSERT query causes the tree to become unbalanced, in which case it needs to be re-ordered, which can take a few seconds up until a few hours, depending on the number of rows. Of course the more indices there are, the more B-TREEs MySQL needs, the slower UPDATEs and INSERTs will be.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •