jordan 13 How Improve The Performance Of Your Mysq

yan3k9d7

till listen an frightful lot of stories from owners whose databases are sprinting 2 slow. In my experience, the three chief places to look in problems are:
1. Faulty Database Design
2. Bad Queries
3. Server factors
Faulty Database Design
Proper database design is the unattached maximum essential factor as the ensuring rendition and maintainability of the database. Here is what you need to response while designing a table: Can I dilute the size of data that every row will have? Here is what you can do:
1. Use unsigned numeric values when the application will not store negate mathematics. Like the “quantity ordered” of one item in an ecommerce petition is not going to be -$125.
2. Use Variable width values instead of nailed length value i.e. used varchar instead of char.
3. Do not use unnecessarily massive field sizes. For most ecommerce application “unsigned smallint” is more than ample to store inventory calculate. A field depicted as “unsigned smallint” can store a max value of 65535.
4. Don’t bypass normalization; its assists prevent needless duplication of data. The chapter B of this is, don’t overuse normalization. If the table ambition no grow in size significantly, there is no point in normalization. For example, whether the user table has fair 20 rows (i.e. 20 employees in a corporation), always ventures of normalization are consumed.
5. Use Keys. Don’t determine keys by “The buyer id has to be indexed in the mandate table”. If the order table is being quested 90% of the times by “order date”,[link widoczny dla zalogowanych], it makes more sense to index “order date”.
Remember,[link widoczny dla zalogowanych], how a table will be accustom should make sure how it is designed. Spending period here will retention annuals of frustration.
Bad Queries
It sounds too agreeable to be true but you wont trust the number of developers out there who completely suck at book queries. There are two types of wrong queries:
a) Unnecessary Queries: These are the queries that shouldn’t have been made at first. The only way to avert this is querying,[link widoczny dla zalogowanych], “Do I truly need this data?”
b) Inefficient Queries: These are the queries that do not use the underlying table building alternatively MySQL features in the correct course.
Here is a starting point to start looking at problem areas:
1. Unnecessary method of “Select * “statements when the all processing is creature done on a single col. The extra data is fetched from the server the extra go MySQL has to do and more bandwidth it takes.
2. Using sub-query instead of a join. On a properly designed database, joins are incredibly fast. Using sub-queries just shows a absence of learning.
3. Improper use of Keys. This is especially valid for range checks. Remember to use the “Explain” statement to retard the usage of keys and then use the “use key” expression in your “where” clauses to compel key usage.
Server Factors
Everything done correctly, there still may be some server factors that may be causing the system to be slow. These are:
1. Hardware associated
2. Server configuration related
Here is what you can do approximately the hardware:
1. The more RAM is on the system the better it is. MySQL frequently fetches data from the RAM and more the RAM is on the system,[link widoczny dla zalogowanych], the better it is.
2. Buy the fastest feasible RAM! A slower RAM is just satire.
3. Once you are stable with the RAM size and speed,[link widoczny dla zalogowanych],[link widoczny dla zalogowanych], look in processing speed. MySQL can use multiple processors.
Once you are satisfied with the hardware, there are a set of variables in “my.cnf” that you must see at:
a) key_buffer_size: This describes the memory available to store the index keys. The default namely 8 MB yet you can set it to 25% of the RAM.
b) query_cache_size: This value is along default 0. if you have a lot of repeating queries favor in reporting application