Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: What is more efficient...

    I have a MySQL 5.0 database which has about 10 million records (updated daily).

    Is it more efficient to search:

    (a) 10 tables each containing 1 million records, or

    (b) 1000 tables each containing 10,000 records?

    My own thoughts are that (b) is NOT as efficient as there are more opening and closing of tables, but I could be wrong.

    Any advice would be much appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, that all depends on what you're searching for.
    Generally speaking (a) is the most efficient imo
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends

    generally speaking Id say that in design terms n tables containing similar data would be a contradiction of good practice.

    if the table is properly designed, using appropriate indexes I don't think you would see any substantial performance discrepancy if the table contained 1 million, 10 million or god knows how many rows MySQL can support these days.

    if your SQL query isn't particularly well crafted, and/or you are performing searches on non indexed columns then there could be significant problems, but if you are gettign probelsm with those then essetnailly you will have the same problems with your n table design..... you just wont notice is quite as obviously.

    however it depends on your data.
    its perfectly reasonable say to have n separate tables covering say each of n branches sales figures, its perfectly reasonable say if each table stored each years sales, ie the main criteria you are going to apply are always going to identify which table to search in. its not the way Id want to do it, but if it works for you there no reason why not.

    However if your proposed design arbitarily splits the data into n tables, and the users may have a requirement to get data form any one of those n, or potentially more than one of those n tables then Id say stick with a single table design.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2005
    Posts
    26
    Thanks for your help.

    Each record contains product information, i.e. for Electronics it would be: Manufacturer, Model, Description, Price, Shipping, Availability, SKU, etc.

    I mentioned 10 tables because we have 10 seperate departments, i.e. Books, Computers, Electronics, Music, Movies, etc.
    Last edited by db__novice; 07-17-07 at 07:18.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Depends on a lot of things, like your search criteria for example.
    SELECT * on any of the above options will kill most database servers

    I also doubt they are directly comparable, because there would essentially be different Entity relationships between those two databases. Either that or one of the options is hideously un-normalised, which would lead to difficulties in other areas as well as performance.

    Part of the answer will also come down to keys. Updating a large table (1 million records) will require restructuring of the key set continuously (and possibly unnecessarily if unnormalised data is contained), whereas restructuring a smaller table key set will always take less time. (this answer is based on BTree algorithms).

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Damn you people. Wishing my reply was "submitted" earlier...

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I wish I'd gone into more detail now
    George
    Home | Blog

  8. #8
    Join Date
    Oct 2005
    Posts
    26
    If it's any help, it's for a price comparison search engine (we're doing a prototype website but can't decide on the DB structure), basically the same as pricegrabber, Kelkoo, shopping.com, etc.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So really you want to post a topic in the "Database Concepts & Design" thread for advice on design?
    George
    Home | Blog

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I would concur with that. It seems you need advice more on the E-R of your tables that on a particular MySQL problem.

    I would say that
    I mentioned 10 tables because we have 10 seperate departments, i.e. Books, Computers, Electronics, Music, Movies, etc.
    is probably better describe as "categories" and NOT "departments". All of the above are particular types of product. I imagine that I might model categories in their own table

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by aschk
    I would concur with that. It seems you need advice more on the E-R of your tables that on a particular MySQL problem.

    I would say that

    is probably better describe as "categories" and NOT "departments". All of the above are particular types of product. I imagine that I might model categories in their own table
    I'm not convinced that I would....
    a product is a product.. after all if soemone is enquiring about say "SQL" you may find relevant categories in books, software, clothing or whatever. I might sub categorise items out (ie have a child table which had book specific data in eg ISBN, author No Pages.... heck I might even use an EAV model for such detail) but I'd still keep the master product detail in one table
    id include a category type int he product detail, referencing a categories table, which would probably have a tree structure in in (ie it should be possible to derive say publications | books | IT design, so I could search for anything with say MySQL in the title specifying the level of category (ie if I specified publications Id want to return all results for that category and ALL sub categories of each child, or I may want to specify MySQL in IT Design..... its down to you as to how you see your users wanting to retrieve information form you.

    how much data you maintain yourself and how you see yourselves making your self different to the likes of Kelkoo or whoever. after all theres no point being a "mee too" if all you are doing is replicating what they currently do....
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Oct 2005
    Posts
    26
    At the moment we've got the following:

    Table "Departments" (contains 10 departments as described above)
    Table "Categories" (Contains 100 categories, for example Electronics has 15 categories, i.e. Televisions)
    Table "Subcats" (contains 800 sub-categories, for example LCD, i.e. Televisions > LCD)

    Then we have the 10 individual department tables, i.e. Electronics, these contain the product lists from the various stores, also all tables are indexed. Now we're happy with the DB structure, it's just that we can't decide if it's best to have 1 table for all products, or 1 table per department, or 1 table per store (which could be upto 1000), hence the original question.

    I hope I've explained it clearly enough.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can tell you how to make up your mind

    write the sql for searching for a product called "acme" (searching in product name, product description, product vendor, etc.) when
    - there is one table
    - there are 10 tables
    - there are 1000 tables

    i think you will find that writing the sql for 1000 tables is too difficult, so you can probably discard that option right up front

    have you ever written sql that searches in 10 tables?

    hint: you will need UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2005
    Posts
    26
    Yes, we've already done the prototype sql code to search the 10 tables and it works perfectly, we've used "INNER JOIN" is that what you mean by a UNION. We've loaded the 10 tables up with 1000 records each of dummy data and we can return any data we chose to search for in about 0.01 seconds.

    However, we don't know what sort of time to expect for it to search 10 million odd records, or at least not until the website goes live. It could well be that what we've already done is sufficient enough but I thought I would ask the question just in case.

    I recognise your username r937, I believe you have helped me before. Thanks.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INNER JOIN? not likely, not unless the same product exists in each of the 10 tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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