With relational database, My SQL proves to be a complex thing. It leaves the applications to meet some issues. An error at database leads to show impact on My SQL performance. In order to ensure that My SQL server runs at greatest speed, it is important to eliminate the issues which lead to work load. Rectifying this problem will improve the performance. Troubleshooting and tuning are the two ways to resolve these problems.

To Improve the Performance,

1. Reduce the Workload:

The best way to realize how the server spends the time is by reducing the workload of server. By reducing the workload, one can resolve the most difficult queries for tuning. Time is the most important factor, because, when you post a query against the server, you no need to feel more about the processing time of query.

SQL query runs faster, if you write query as

Code:
SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of,

Code:
SELECT * FROM student_details;
My SQL Enterprise Monitor is the best tool to reduce work load. This tool will resolve queries executed by the server and returns the tasks sorted by response time. Workload profiling tools group queries together which allow one to see the queries which are running slowly and which are running fast.

2. Understand the Fundamental Resources:

A database server requires 4 fundamental resources like CPU, memory, disk and network. If anyone of these goes weak, then the database server may perform slowly. It becomes highly necessary for one to understand the important resources especially in two areas like hardware selection and troubleshooting.

While choosing hardware for My SQL, be clear in choosing the better performing components. In important cases, addition of memory is the best way to increase the performance. Sometimes, the memory may not be such enough to hold the data of server.

Write query as,

Code:
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS (SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of,

Code:
SELECT DISTINCT d.dept-id, d.dept FROM dept d, employee e WHERE e.dept = e.dept;
3. Do Not Use My SQL As Queue:

Queues may raise problems for two major causes. It may serialize workload and prevents tasks from parallel execution. It often results in table which contains work in process as well as historical data. Add latency to the application and load to My SQL.

If you want to use WHERE clause, write query as follows,


Code:
SELECT id, first-name, age FROM
Student-details WHERE age>10;
Instead of,

Code:
SELECT id, first-name, age FROM
Student details WHERE age! =10;
4. Filter Results By Place Cheap in First Position;

The best way to optimize My SQL is to do cheap. There is a problem with this technique. The formula requires more trigonometric operations. Great circle calculations may run slowly and lead CPU to run slow. Square should contain circle inside so as to make this function easily.

5. Know Two Scalability Traps:

Scalability is not a vague topic. In fact, there are some mathematical definitions which are expressed as equations. Parallel processes should stop for serialization.
Author
Bhawani Singh
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from Bhawani Singh

Top