1

I built an application from the ground up on INNODB due to it's ACID functionality. However, I have hit a bit of a road block. I have a products table and the categories for each product are separated by a delimiter. In total there are around 1800 unique categories and each product is associated with about 3-4 at a time. I have about 300,000 rows of unique products.

I tried doing this by the book and normalized my categories by creating a separate table for them and associated them with my products using a many to many relationship. However, the problem is that the products table itself is around 300,000 rows strong and add on to that a many to many categories association table with about 900,000 rows and we have a monster! It is next to impossible getting reasonable speeds for joins between the two tables....

What would you make of denormalizing a little in this case by storing the categories in their raw format in their own TEXT field in the products table. To filter through these products based on categories I could use fulltextsearch? What are the pros and cons of such an approach?

By the way I am on a shared server, so I can't increase the buffer size or utilize memory management to really make use of InnoDB

Solved If I could I would upvote everyone here who mocked and scoffed at my false illusions about how going by the book (normalizing) seemed to have cost me speed. In truth it's given me consistency and with a few indexes tweaked here and there I am getting lightning fast speeds. Thanks everyone.

captainspi
  • 445
  • 2
  • 15
  • 1
    did you add the right index in the right place?, sometimes the problem could be in the query more that the structure – jcho360 Nov 29 '12 at 18:29
  • Yes, I indexed the columns I was hooking the tables together by... – captainspi Nov 29 '12 at 18:32
  • Please post an example query, an `EXPLAIN` of that query, and the tables' structure. MyISAM won't make a significant difference over InnoDB in this situation. – G-Nugget Nov 29 '12 at 18:33
  • 1
    900,000 rows is hardly a "*monster*". It's actually not even a big table. –  Nov 29 '12 at 18:34
  • I will edit this post the second I have my query and an EXPLAIN. That's good to hear, maybe I won't have to change the structure. – captainspi Nov 29 '12 at 18:35

1 Answers1

3

Your subject suggests you are considering using MyISAM instead of InnoDB, but then your question asks about the value of denormalizing.

See my answer to Is storing a delimited list in a database column really that bad?

Denormalizing can help if you always need to know the categories for a given product. If you need to know the products for a given category, it'll be much worse -- or else you need to store a redundant list of products in each row of the categories table. Good luck keeping them in sync.

Joining a 300k table to a 900k table should be easy and efficient, if you have the right indexes.

You should learn to optimize queries with EXPLAIN.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • That makes sense. It is also why I chose to normalize till 3NF before I hit the panic button a few minutes ago. It must be the indexes, I tried modifying them before posting here but the webserver crashed on me for some reason. Yikes! – captainspi Nov 29 '12 at 18:45