Results 1 to 14 of 14
  1. #1
    Full Member
    Join Date
    January 16th, 2006
    Posts
    447
    Question Is One Giant Table Okay?
    Is it bad to have one giant table with all my datafeed information in it if it is all the same type (and assuming my data is correct)?

    If I have shoes and three stores, is it wrong to put all the datafeed from all three into one table? As long as I am not locking the entire table with *, shouldn't it be okay? I am guessing it would have about 50 -60k rows, but will increase as stores are added.

    I thought I might put brands in separate tables for speed though. I suppose I could query the data and create separate tables for each type or brand.

    Just thought I'd get some thoughts. Right now all I can do is one big ole table (not that slow either).

    Thank you.

  2. #2
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    You definitely want to keep all of your data in a single table. Especially if you're only dealing with 50 to 60k rows. That's a small number of rows and a single table can handle them just fine. It's only when you start hitting 500,000 and you've got fulltext indexes that you need to start creating new tables. In which case you would need a separate table that only stores the most pertinent and searchable data. You never want to divide up your data among tables by brand name or anything like that. Datafeeds are best kept in a single table for a single site. You can even use a single table for multiple sites as long as you aren't creating duplicate content for the search engines.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  3. #3
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Agreed, keep them in one table. Just make sure you are indexing everything you use in your sql statement. You can check what the sql statement is using by doing an explain.

    For mysql here is the instructions for this.
    http://dev.mysql.com/doc/refman/4.1/en/explain.html

    Chet

  4. #4
    Full Member
    Join Date
    January 16th, 2006
    Posts
    447
    Quote Originally Posted by chetf
    Agreed, keep them in one table. Just make sure you are indexing everything you use in your sql statement. You can check what the sql statement is using by doing an explain.

    For mysql here is the instructions for this.
    http://dev.mysql.com/doc/refman/4.1/en/explain.html

    Chet
    Thanks. I had created the indices today. At least I doing something right...now if I can just make money doing it .

  5. #5
    Resident Genius and Staunch Capitalist Leader's Avatar
    Join Date
    January 18th, 2005
    Location
    Florida
    Posts
    12,817
    Quote Originally Posted by Snib
    You definitely want to keep all of your data in a single table.
    Quote Originally Posted by chetf
    Agreed, keep them in one table.
    Cool! That's how I've always had mine set up, but I never knew if that was something that'd make coders cringe... Especially since the books/tutorial sites I saw all seem to love "relational" databases. I thought relational dbs sounded like a bunch of making work where there didn't need to be any, so I ignored that part of the books, but they had me wondering if there was some unknown Real Reason that I'd missed.

    So glad to see that one table is indeed considered fine, at least by those who don't have a financial stake in dragging the job out longer...

  6. #6
    Full Member
    Join Date
    January 16th, 2006
    Posts
    447
    Quote Originally Posted by Leader
    Cool! That's how I've always had mine set up, but I never knew if that was something that'd make coders cringe... Especially since the books/tutorial sites I saw all seem to love "relational" databases. I thought relational dbs sounded like a bunch of making work where there didn't need to be any, so I ignored that part of the books, but they had me wondering if there was some unknown Real Reason that I'd missed.

    So glad to see that one table is indeed considered fine, at least by those who don't have a financial stake in dragging the job out longer...
    I did not see any reason for normalization. Just having smaller tables for some known searches. Most of the data is not such that it could be related amongst tables, but the size of the table did concern me. I guess I will worry about it when it hit half a billion rows.

  7. #7
    Roll Tide mobilebadboy's Avatar
    Join Date
    January 18th, 2005
    Location
    Mobile, Alabama
    Posts
    1,220
    I'm a stickler for organization.

    I never could bring myself to dump everything in to one single table.

    Shawn Kerr (.com) | Disney World | SEC Football

  8. #8
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    I want to make another point about this. When I say use a single table for product data I'm referring to prices, links, product names, descriptions, thumbnail URL's, etc. But you may need additional tables for things like your category structure, click statistics, product reviews, coupons, etc. This is when you start getting into relational database design.

    I actually have two product tables that contain mostly the same information. One table is where my datafeed imports go directly and the other table is for customers on the front end. That way the datafeed importing process doesn't interfere with the customers' shopping experience. It also speeds up the import process as there are no fulltext indexes on my import table. I have a script that runs after all the imports are complete every night. It mirrors the data between the two tables and ensures the customer table only contains in-stock items.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  9. #9
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Again agreed with scott. the one table is for the "catalog" the others are needed to make your site functional.

  10. #10
    Newbie
    Join Date
    January 4th, 2006
    Location
    Berlin
    Posts
    41
    We are data feed management software developers and personally i am horrified to see all product data feed information stored in just one table, as someone pointed out, its only ever going to be 50k - 60k to start with but these things have a habit of growing and then suddenly you are in a mess. Having said that, its much easier to actually get some damn work done by having all the information in one table!

    Even against our judgement even we try and keep information on 1 table, it will hit problems i am sure one day, but certainly not at 500k records like someone suggested, on a 2gig ram machine and decent speed hard drives mysql can handle at least 5 million records with Full text indexing very well indeed if you configure and optimizre it properly.

    One way we do work around the problem of database not being in an optimal structure (ie all information in one table) is when we import data we might duplicate the import, ok the database size increases (lowering performance) but the speeds of only searching one table with very small amount of information like an id or name is increased, so its kind of both worlds really.

    But for now, no matter how techy you get it seems one table will do perfectly will for even the most agressive affiliate marketing bods.

  11. #11
    Full Member
    Join Date
    January 16th, 2006
    Posts
    447
    Quote Originally Posted by pricethat
    We are data feed management software developers and personally i am horrified to see all product data feed information stored in just one table, as someone pointed out, its only ever going to be 50k - 60k to start with but these things have a habit of growing and then suddenly you are in a mess. Having said that, its much easier to actually get some damn work done by having all the information in one table!

    Even against our judgement even we try and keep information on 1 table, it will hit problems i am sure one day, but certainly not at 500k records like someone suggested, on a 2gig ram machine and decent speed hard drives mysql can handle at least 5 million records with Full text indexing very well indeed if you configure and optimizre it properly.

    One way we do work around the problem of database not being in an optimal structure (ie all information in one table) is when we import data we might duplicate the import, ok the database size increases (lowering performance) but the speeds of only searching one table with very small amount of information like an id or name is increased, so its kind of both worlds really.

    But for now, no matter how techy you get it seems one table will do perfectly will for even the most agressive affiliate marketing bods.
    The only real problem I have is the management of categories. I have to split the category because it has things like

    accessories:handbags:something:something...

    It makes it hard to parse it so I can display it properly and it makes make me have to use a lot of LIKEs. I am considering tree structures and hierarchies, but nothing is in stone yet. I really in over my head, but I'm learning. Simple SQL isn't working to well alone. Thanks for the comment. I am at 134K rows so far.

  12. #12
    Member
    Join Date
    January 18th, 2005
    Posts
    87
    I currently have just over 2,000,000 products in my database and started noticing a big decline in performance with this amount. I then contacted my web hosting provider and they had just realised after nearly two years of having my dedicated server that it was missing 1gb of ram.

    So they have put that in and things have now improved somewhat.

    I have also used 1 table for the bulk of my datafeed information and another table for like the merchant name, if the merchant products is approved or not, the general category of the merchant if it has one, etc.

    I also have another table then that includes the information if someone clicks on the "more" link so that they can read more about the product.

    This is practically the same as the main table apart from it also includes the full description of the product and a little more information.

    It did try normalization as I thought that it would be much better and other people also said it would be faster. But after spending nearly a month building the scripts I then gave up as I found that it was taking forever to add the products to each table plus the querying of products where slower, which in whole made the performance alot slower.

    The only advantage that I received was that I was using much less space on my server.
    [url=www.gamesdash.com]Games Dash[/url] - listing many free online games that you can play.

  13. #13
    Newbie
    Join Date
    January 4th, 2006
    Location
    Berlin
    Posts
    41
    Quote Originally Posted by john9245
    It did try normalization as I thought that it would be much better and other people also said it would be faster. But after spending nearly a month building the scripts I then gave up as I found that it was taking forever to add the products to each table plus the querying of products where slower, which in whole made the performance alot slower.

    The only advantage that I received was that I was using much less space on my server.
    Most people think that ram will help them in these situations, in fact ram does very little to actually increase the speed of searches on big databases. You need to increase the hard disk drive speed as you can have all the ram in the world but it cant do anything until the hard disks get the information out of the drive.

    Most web server setups have 7200rpm disks or something, try getting the hard disks upgraded to 15000rpm disks and you will see things fly in comparison. Also make sure you run optimize table commands at the end of each of your updates that delete records as the missing records slow things down. Optimizing the table actually helps increase the read speeds in most cases.

  14. #14
    Member
    Join Date
    January 18th, 2005
    Posts
    87
    I'm sure that I have noticed a little increase in performance. It is not much, but a little.

    Any, wouldn't increasing the CPU also help the speed. I was thinking of maybe adding a few CPU's together so that it can make one larger CPU. Would something like this also work great even though I don't upgrade the disk.

    Also, if I increase the disk to 15000rpm, would that mean I would have to get a totally new disk and there for have to back up all of my work and then move it over to the new disk?

    At the moment I have most of my work and databases on one server. I am planning on doing it some eventually my products database is on it's own server and my articles database is also on another server of it's own, etc.
    [url=www.gamesdash.com]Games Dash[/url] - listing many free online games that you can play.

  15. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Mega Shark vs. Giant Octopus
    By Merchant Consultant Team in forum Virtual Family and Off-Topic
    Replies: 1
    Last Post: May 17th, 2009, 12:49 PM
  2. All over a giant burrito.
    By Adam Ward in forum Virtual Family and Off-Topic
    Replies: 1
    Last Post: May 3rd, 2005, 12:22 AM
  3. Anyone know the implications MS and Giant?
    By ronwls in forum Midnight Cafe'
    Replies: 1
    Last Post: December 17th, 2004, 10:44 PM
  4. Kudos to Sticker Giant
    By Cedric in forum Commission Junction - CJ
    Replies: 2
    Last Post: September 8th, 2002, 11:00 PM
  5. the giant squids
    By Ebudae in forum Midnight Cafe'
    Replies: 2
    Last Post: March 24th, 2002, 07:38 PM

Posting Permissions

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