What is Query Optimization in DBMS? How to optimize a query?

AS my knowledge there is some proccess
1.inner querry
2.join query
3.using specific keyword(like max ,min)

A few techniques of Query Optimization are:

1. Faster SELECT query, when you use the actual column names instead of '*'.
Original SQL Code:

SELECT id, first_name, last_name, age FROM member_details;

Optimized SQL Code:

SELECT * FROM member_details;

2. HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
Original SQL Code:

SELECT subject, count(subject) 
FROM student_details 
GROUP BY subject 
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

Optimized SQL Code:

SELECT subject, count(subject) 
FROM student_details 
WHERE subject != 'Science' 
AND subject != 'Maths' 
GROUP BY subject;

3. Sometimes you may have more than one sub-queries in your main query. Try to minimize the number of subquery block in your query.
Original SQL Code:

SELECT name 
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details) 
AND age = (SELECT MAX(age) FROM employee_details) 
AND emp_dept = 'Electronics';

Optimized SQL Code:

SELECT name 
FROM employee 
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
FROM employee_details) 
AND dept = 'Electronics';

4. Use operator EXISTS, IN and table joins appropriately in your query.
Original SQL Code:

Select * from product p 
where product_id IN 
(select product_id from order_items)

Optimized SQL Code:

Select * from product p 
where EXISTS (select * from order_items o 
where o.product_id = p.product_id)

5. Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
Original SQL Code:

SELECT DISTINCT d.dept_id, d.dept 
FROM dept d,employee e 
WHERE e.dept = e.dept;

Optimized SQL Code:

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

Adding a index to a column is also a good way to optimize your search query

for eg if you have database having table say A

having attributes such as city, state, address , then you can add index to these to make your search fast

2:- Always try to avoid unwanted parenthesis in your query