MySQL Point

MySQL is a rock in the breeze, lighting server fast database that is designed for two factors speed and performance. This is the Ferrari of databases: Lightweight, fast and built for high-speed tracks!
I still hear many stories from proprietary databases including running two slow. Search In my experience, the three most important areas for problems are:
1. Database design wrong
2. Bad Queries
3. Servers factors
Database design wrong
The design of appropriate foundations is the factor most important in ensuring performance and maintainability of the database. Here's what you must answer when designing a table: Can I change the size of each data line is it? Here's what you can do:
1. Use unsigned numeric values, if the application stores a negative number. Designated as the "quantity" of an element in an application e-commerce will never be – $ 125
2. Use the values of variables instead of a fixed length value that is used varchar instead of CHAR.
3. Do not use unnecessarily large size of fields. For the majority of e-commerce applications "unsigned smallint" is more than enough to keep stocks. A field of "unsigned smallint" can store a maximum value of 65535.
4. Ignore standards, which avoids duplication of data. Part B is that too much standardization. If the table does not grow in size, but clearly there is no sense of standardization. For example, if the user table has only 20 lines () or 20 employees of a company are wasted all attempts of normalization.
5. Use the arrow keys. Deciding not to the keys of "The customer will be indexed in the control panel." If the table in order to ask 90% of the time for "Order Date", it is more logical index "Order Date";.
Remember how a table should be used to determine how it is interpreted. Spending time to save years of frustration here.
Bad Queries
It's too good to be true in general, but you think that indicates the number of developers who suck completely written requests. There are two types of bad queries:
a) unnecessary queries: These are issues that should not have to do first. The only way to avoid this is to ask: "Do I really need this information?"
b) inefficient queries: These are the questions that are not structure underlying table, or use the MySQL functions in the right way.
Here is a starting point to begin talking of issues:
1. Useless use of "Select *" statements, all processing is done on a single column. More data retrieved from the server, the more work MySQL has to do and more bandwidth is required.
2. By adhering to the sub-query instead of one. In a database of well designed, links are incredibly fast. With subqueries just shows a lack of knowledge.
3. Improper use of buttons. This is particularly true for field inspections. Remember to use the "Explain" statement to verify the use of keys and then with the "key" of your statement, "where" key clauses use force.
Factors Server
Everything correctly, there are some factors that could cause the server system has been slow. They are:
1. Related material
2. Server Configuration in the context
Here's what you can do about hardware:
1. The more RAM on the system, the better. MySQL often recovers data from RAM and more RAM on the system, the better.
2. You buy the fastest RAM! A RAM is slower than irony.
3. If you have set with the RAM size and speed, look for processing speed. MySQL may have multiple processors.
If you are satisfied with the material, there are a number of variables in my file. Cnf "you need to watch for:
a key_buffer_size): This describes the available space on the index key save. The default is 8 MB, but you can set it to 25% of RAM.
query_cache_size b): This value defaults to 0 If you repeat too as queries in reports of applications, etc., you should use this high value.
table_cache c): This option determines the number of descriptors that MySQL table, holding in the cache. The default is 64 but if you also have more access for 100 users at a table, then this value should be atleast 100. You must also include reflections, etc. Therefore, this value must be set too high.
I hope this article will be a step forward in unlocking the mystery of slow servers and resolve some problems.