To Improve the Performance do the following:

1. Reduce the Workload:

The best way to realize how the server spends the time is by reducing the workload of the 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 the query.

SQL query runs faster if you write the query as

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

SELECT * FROM student_details;
My SQL Enterprise Monitor is the best tool to reduce workload. 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, the 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

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

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 the 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 a query as follows,


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

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.

Was this answer helpful? 2 Users Found This Useful (72 Votes)