Member Avatar for bas_brain

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

  • 3 Contributors
  • 2 Replies
  • 135 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Shanti C
Member Avatar for hericles

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,

Member Avatar for Shanti C

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

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.
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/2009/01/12/should-you-move-from-myisam-to-innodb/
http://binnyva.blogspot.com/2006/08/mysql-storage-engines-myisam-innodb-and.html

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.