CODEWITHSUNDEEP
mysqlnormalization
Hander
Hander

Reputation: 3

Mysql big table vs joins

I have one (items [id, name, color, size, etc] ) table with more than 6000. And i think it can reach 10000 at most. In that table basically is information about, well, items. I also have 5 constant storehouses' tables and from 0 to 7 temporary storehouses' tables. Those tables are like this (id, item_id, quantity). I tried to normalize that database by combining those storehouses in one table (id, item_id, quantity, storehouse_id). That made searches extremely slow 6-10sec when searching for multiple items from all storehouses using self-joins. Then I decided to combine items with that newly created database (id, name, color, size, etc, storehouse1_quantity, storehouse2_quantity, etc). That made queries less than a 0.1s for any request. But now the database is very denormalized. As storehouses have no more than 2000 items and mostly less than 500. I can live with that if there is no better way. I don't know where to look at to make database more logic and easier to maintain. Any help is appreciated! Thanks in advance!

Upvotes: 0

Views: 209

Answers (1)

GDP
GDP

Reputation: 8178

Well, the normalize approach is the "right" way, and since DB engines are built to deal with this, I'd start looking at what your index arrangement is. If there a lot of lookups/joins, there generally should be an index so that MySQL doesn't have to chug through all the text.

Upvotes: 1

Related Questions