SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    ohio
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Max recommended # of rows per table?

    When exactly does a mysql table become too crowded with rows? 10,000? 50,000? 100,000? 1,000,000? More?

    I'm asking because I am creating a "product" table, and each product_id will have ~5 images located on the server, but using the product_id I must locate all 5 filenames...


    Option1: Following normalization rules, I could create an "image" table and associate each product_id with a single image_id and filepath. But ~5 images x 10,000 products = 50,000 rows in the "image" table...

    Option2: List all 5 image paths in a comma-separated list in the "product" table, and just parse it for each query...



    I just have this feeling in my gut that says a query like...

    SELECT image_path FROM image WHERE product_id = $product_id

    ...on a table with 50,000 rows might be a bad idea compared to the alternative in Option2... What do you guys think?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a comma-separated list inside a single column is a very, very bad idea

    50,000 rows is small

    even 50,000,000 is not too "crowded"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    ohio
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    a comma-separated list inside a single column is a very, very bad idea

    50,000 rows is small

    even 50,000,000 is not too "crowded"
    Hmm, thanks for the answer but I was looking for an answer a little more specific . Because I'm not loaded with cash, I want to design my database so that it saves on bandwith and speed. Each additional table I add is going to increase server query time and bandwith costs.

    I understand though why some things should be in separate tables. So why exactly is having a comma separated list for file-names a bad ides (or just rely on the implode/explode). As long as I build the query functions in PHP correctly, I can't see the benefit to doing two tables and having one be 50,00+ in size...?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you use implode/explode, you are by definition scanning the entire table

    a table scan is the most expensive database operation you can have

    you are forfeiting the good performance you would get from using indexes to join tables, in favour of a programming technique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    ohio
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you use implode/explode, you are by definition scanning the entire table

    a table scan is the most expensive database operation you can have

    you are forfeiting the good performance you would get from using indexes to join tables, in favour of a programming technique
    I don't understand how I would need to scan the entire table to use the explode function? Here is an example of my table:

    Table "Product"
    ---------------
    id (primary key)
    name
    price
    images

    Example of a row in table "Product"
    ----------------------------------
    id = 1
    name = drill
    price = 22.50
    images = drill1.jpg,drill2.jpg,drill3.jpg

    A customer would see the product list, and click on a product directing the customer to "that" product's page. The MySQL database would then be queried with something like:

    $query_string = "SELECT name, price, images FROM product WHERE id = 1";
    $result = mysql_query($query_string);
    $image_array = explode(",", $result['images']);

    The explode() function does not affect the database query. And since 'id' is a PK, the entire table would not be scanned. Finally, even if 'id' was not a PK and there was no index on it, the entire table would not be scanned... only the 'id' column.

    Thoughts?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i understand, yes, in that scenario, you are searching for a specific page

    it would not be a table scan if you have an index on id

    (otherwise, yes, without an index on id, it would be a table scan )

    but you're right, in this scenario, you would never search for a specific image
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2005
    Location
    Lithuania
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another solution is to make a directory "product_images". Then, when making new product, create new directory named the same that id of a product, and put the pictures there. So you won't have to do anything with mysql, or you can have just a flag in a table, to know exactly if there are photos for this product.

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its not good design to store more than one peice of information in a table. The costs associated with making another table cant be too much more and it would probably be easier to create IMO.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, it's not good design to store more than one piece of information in a single column value

    if you only had one piece of information in a table, you would have a bazillion tables, and you wouldn't be able to join them

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

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Err yes thanks Rudy thats what I meant.... =/

  11. #11
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    ohio
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Quote Originally Posted by xaoc
    Another solution is to make a directory "product_images". Then, when making new product, create new directory named the same that id of a product, and put the pictures there. So you won't have to do anything with mysql, or you can have just a flag in a table, to know exactly if there are photos for this product.
    That's what I'm going to do.


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
  •