Need advice in mysql performance tunning

Expand Post »

hi, i am a newbie in mysql and i have some questions about performance tuning.

1-) in a system where you have to store information about a user, such as, user_name, id, last_log, is it better to store all the information needed in one table (lets assume 30 fields in a table) or split then in more tables (3 tables with 10 fields each).

2-) on my.conf, which are the values that is related with performance and how to change then (there is a formula, or something)?

3-) storage engines: Myisam or Innodb

4-) Queries: select * from db. Is this query good to get all users registered or there is a disadvantage in performance.

Any advice you may want to give will be welcome.

thanks for helping

bas_brain

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Apr 2009

Permalink

Apr 27th, 2009

Re: Need advice in mysql performance tunning

Hi bas_brain,
I can't help with parts 2 and 3 of your question.
For 1) you can improve the performance by structuring the tables around the data you will be requesting the most. E.g. if there are several columns you will always be selecting and others than you'll use rarely you can split the tables along those lines.
Also, constructing the tables to avoid redundant data is a good idea (have a look and 2nd and 3rd normal forms on the net).

For 4) select * takes everything form the table. This obviously takes longer than just selecting what you actually need.

Hope that helps,

hericles

Reputation Points: 78

Solved Threads: 118

Practically a Master Poster

Offline

633 posts
since Nov 2007

Permalink

Apr 29th, 2009

Re: Need advice in mysql performance tunning

As i know , i got this information for your 3 rd question:

(Toggle Plain Text)

you need to look at what makes each engine unique:

myisam -- fast for reading and inserting, table level lock which makes
it not suitable for transactions
innodb -- fast for updating and most suitable for transactions because
it uses row level locks

you certainly can use a combination of these 2 types of engines in your
database.

with innodb, you'll have cascading deletes/updates; however with myisam,
you'll have to do it yourself in the code or in stored procedures. so
maybe some of your tables should be innodb while others should be myisam.

(Toggle Plain Text)

However, I noticed that InnoDB tables get a bit slower than MyISAM
tables during bulk-inserts ,especially of TEXT/Blobs and Varchars. It
is all relative. It depends what fields you want to store, how many
writes / reads and so on.

Refer these links for more information :
http://www.mysqlperformanceblog.com/...sam-to-innodb/
http://binnyva.blogspot.com/2006/08/...nnodb-and.html

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Retrieve data from table

Next Thread in MySQL Forum Timeline: Currently connected to remote server but remote configuration is not yet possible

DaniWeb Message

Cancel Changes

User Name
Password