Many fields vs. many tables in php mysql

Question

I am building a game site with a lot of queries. For optimisation what is best. handeling the data with a lot of tables and relations or fewer tables but with many fields?

I would think, especially regarding to inserts and updates that fewer fields with many fields would be better than many tables. That would give more queries or???

I'm trying to figure out what is best course I am experiencing high load on my server at the evenings when I have a lot of users...

You'll need to give some more info on your data model. General rule is to Normalise first then look at optimising. — user1914530
– user1914530, Commented Jan 28, 2013 at 22:07

Ed Heal · Accepted Answer · 2013-01-28 22:28:55Z

2

Start off with the database normalized. Ensure that you have the appropriate indexes for the queries/updates/inserts that you are doing. Use optimize table periodically.

If you are still encountering problems do some profiling to find out where the performance is insufficient. Then consider either denormalizing or perhaps rewriting the queries.

In addition make sure that the system cannot have deadlocks. That really messes up performance.

edited Jan 28, 2013 at 22:28

answered Jan 28, 2013 at 22:16

Ed Heal

60.3k18 gold badges91 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mansa Over a year ago

What do you mean about deadlocks?

Chris Brickhouse Over a year ago

if you put with (nolock) in all of your sql queries you can avoid locking. ie: select * from table with (nolock)

Ed Heal Over a year ago

@Mansa - See the link above. There are techniques to prevent them, avoid them and/or handle them. Also you can prevent locking as Chris has pointed out.

Chris Brickhouse · Accepted Answer · 2013-01-28 22:07:25Z

1

i don't think the number of columns effects anything, really. it's all about how well you've indexed the columns. if you do more updates then selects on a particular field, you might want to drop the index if you have one.

not really an answer, just something i've noticed.

answered Jan 28, 2013 at 22:07

Chris Brickhouse

6485 silver badges15 bronze badges

Collectives™ on Stack Overflow

Many fields vs. many tables in php mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related