 |  |  |  |  |  |  |  |
Need advice in mysql performance tunning
Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
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
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
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,
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
MCP MCAD
MCP MCAD
•
•
Join Date: Jul 2008
Posts: 1,072
Reputation: 
Solved Threads: 98
As i know , i got this information for your 3 rd question:
Refer these links for more information :
http://www.mysqlperformanceblog.com/...sam-to-innodb/
http://binnyva.blogspot.com/2006/08/...nnodb-and.html
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/...sam-to-innodb/
http://binnyva.blogspot.com/2006/08/...nnodb-and.html
Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..
|
Other Threads in the MySQL Forum
- Previous Thread: Retrieve data from table
- Next Thread: Currently connected to remote server but remote configuration is not yet possible
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MySQL Posts
- Today's Posts
- Unanswered Threads
agplv3 alfresco api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal dui ec2 email enterprise eudora facebook form foss gnu government gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
