Query Optimization

1. Optimize queries by selecting only necessary columns in multi-table joins. Avoid SELECT *.

2. For large datasets with small result sets:

   • Use covering indexes
   • Modify schema (e.g., summary tables)
   • Rewrite complex queries for optimizer efficiency

3. Query refactoring approaches:

   • Split complex quereis into simpler ones
   • Use divide-and-conquer with incremental filtering
   • Decompose joins into application-side operations

4. Application-side joins are preferable when:

   • Caching early query results
   • Using multiple MyISAM tables
   • Data is distributed across servers
   • Replacing joins with IN() clauses
   • Multiple references to the same table

5. Query states can be checked with:

SHOW FULL PROCESSLIST;

6. For uncached queries:

SELECT SQL_NO_CACHE COUNT(*) FROM employees;

7. Optimization techniques:

   • Reorder join tables
   • Convert outer to inner joins
   • Apply algebraic equivalences
   • Optimize MIN/MAX/COUNT functions
   • Simplify constant expressions
   • Leverage covering indexes
   • Optimize subqueries
   • Early termination
   • Equality propagation
   • IN() clause optimization
   • Table/index statistics
   • Join execution strategies

8. MySQL optimizer limitations:

   • Correlated subqueries
   • UNION restrictions
   • Index merge optimization

9. LIMIT/OFFSET optimization:

SELECT * FROM employees WHERE position BETWEEN 50 AND 54 ORDER BY position;

10. Query hints:

SELECT STRAIGHT_JOIN * FROM employees;
SELECT HIGH_PRIORITY * FROM employees;
INSERT DELAYED INTO employees(name,salary) VALUES('temp',4000);

11. INTERVAL for date ranges

12. User variables:

SET @counter := 0;

Advanced Features

1. Query cache acts as a lookup table

2. Cache excludes non-deterministic functions (NOW(), CURRENT_DATE())

3. InnoDB transactions invalidate cache for modified tables

4. Cache miss reasons:

   • Non-cacheable queries
   • First-time query execution
   • Cache eviction

5. Cache configuration:

query_cache_type = ON
query_cache_size = 16777216
query_cache_min_res_unit = 4096
query_cache_limit = 1048576
query_cache_wlock_invalidate = OFF

6. Cache control:

SELECT SQL_CACHE * FROM employees;
SELECT SQL_NO_CACHE * FROM employees;

7. Stored procedures example:

DELIMITER //
CREATE PROCEDURE batch_insert(IN iterations INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < iterations DO
        INSERT INTO employees(name,salary) VALUES('batch',1000);
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

8. Triggers example:

DELIMITER //
CREATE TRIGGER salary_cap 
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF (NEW.salary > 10000) THEN
        SET NEW.salary = 10000;
    END IF;
END//
DELIMITER ;

9. Scheduled events example:

CREATE EVENT weekly_cleanup
ON SCHEDULE EVERY 1 WEEK
DO
    CALL cleanup_routine();

10. Prepared statements example:

PREPARE emp_query FROM 'SELECT * FROM employees WHERE id = ?';
SET @emp_id = 42;
EXECUTE emp_query USING @emp_id;
DEALLOCATE PREPARE emp_query;

11. Views for security:

CREATE VIEW employee_view AS SELECT name, department FROM employees;
GRANT SELECT ON employee_view TO reporting_user;

12. Table partitioning example:

CREATE TABLE sales (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (sale_date, product_id)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);