Engine Independent Table statistics: https://mariadb.com/kb/en/library/engine-independent-table-statistics/
MySQL schema worst practices: https://docs.google.com/presentation/d/1q7e8wE5wz4aOglXhFjm1tndytWhwB3k9KqM3HQhwuMc/edit?usp=sharing
Optimizer hints: https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html
GROUP BY: https://www.percona.com/blog/2018/02/05/four-ways-to-execute-mysql-group-by/
--- string wildcard and index size
SELECT first_name, last_name FROM employees WHERE first_name LIKE "D%";
EXPLAIN SELECT first_name, last_name FROM employees WHERE first_name LIKE "D%" \G
EXPLAIN FORMAT=JSON SELECT first_name, last_name FROM employees WHERE first_name LIKE "D%" \G
ALTER TABLE employees ADD INDEX fname(first_name);
EXPLAIN SELECT first_name, last_name FROM employees WHERE first_name LIKE "D%" \G
EXPLAIN FORMAT=JSON SELECT first_name, last_name FROM employees WHERE first_name LIKE "D%" \G
ALTER TABLE employees DROP INDEX fname;
ALTER TABLE employees ADD INDEX fname(first_name(8));
EXPLAIN SELECT first_name, last_name FROM employees WHERE first_name LIKE "D%" \G
EXPLAIN SELECT first_name, last_name FROM employees WHERE first_name LIKE "%D" \G
--- index on range WHERE conditions
SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00';
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' \G
SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' LIMIT 1;
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' LIMIT 1 \G
ALTER TABLE salaries ADD KEY sal(salary);
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' \G
ALTER TABLE salaries ADD KEY frdate(from_date);
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' \G
# no luck, first range disables last part of index
ALTER TABLE salaries ADD KEY sal_frm_date_t_date(salary,from_date,to_date);
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' AND to_date < '2001-06-22' \G
# from_date is equality, we use even last part of index (compare key_len)
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date = '1995-01-01 00:00:00' AND to_date < '2001-06-22' \G
--- and now the same with multi column index
ALTER TABLE salaries DROP KEY frdate;
ALTER TABLE salaries DROP KEY sal;
ALTER TABLE salaries ADD KEY frdate(salary,from_date);
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 AND from_date > '1995-01-01 00:00:00' \G
EXPLAIN SELECT * FROM salaries WHERE salary = 60117 \G
EXPLAIN SELECT * FROM salaries WHERE from_date = '1995-01-01' AND salary = 60117 \G
EXPLAIN SELECT * FROM salaries WHERE from_date > '1995-01-01 00:00:00' \G
--- evaluation on left side of argument
--- first example can use indexes, second cannot
EXPLAIN SELECT * FROM salaries WHERE salary=60117 \G
EXPLAIN SELECT * FROM salaries WHERE salary+1=60118 \G
--- AND != OR
EXPLAIN SELECT first_name, last_name FROM employees WHERE emp_no=10001 AND first_name LIKE "Georgi"\G
EXPLAIN SELECT first_name, last_name FROM employees WHERE emp_no=10001 OR first_name LIKE "Georgi"\G
ALTER TABLE employees ADD KEY emp_first_name(emp_no,first_name);
EXPLAIN SELECT first_name, last_name FROM employees WHERE emp_no=10001 OR first_name = "Georgi"\G
ALTER TABLE employees DROP KEY fname;
EXPLAIN SELECT first_name, last_name FROM employees WHERE emp_no=10001 OR first_name = "Georgi"\G
--- it is more complicated when joins kick in
EXPLAIN SELECT first_name, last_name, salary FROM employees INNER JOIN salaries USING(emp_no) WHERE last_name LIKE "Kl%" AND salary > 85000 \G
ALTER TABLE employees ADD KEY last_name(last_name(10));
EXPLAIN SELECT first_name, last_name, salary FROM employees INNER JOIN salaries USING(emp_no) WHERE last_name LIKE "Kl%" AND salary > 85000 \G
ALTER TABLE salaries ADD KEY salary(salary);
EXPLAIN SELECT first_name, last_name, salary FROM employees INNER JOIN salaries USING(emp_no) WHERE last_name LIKE "Kl%" AND salary > 85000 \G
ALTER TABLE salaries ADD KEY emp_no_salary(emp_no,salary);
EXPLAIN SELECT first_name, last_name, salary FROM employees INNER JOIN salaries USING(emp_no) WHERE last_name LIKE "Kl%" AND salary > 85000 \G
EXPLAIN SELECT first_name, last_name, salary FROM employees INNER JOIN salaries USING(emp_no) WHERE hire_date > "2015-01-01" AND salary > 85000 \G
ALTER TABLE employees ADD KEY hire_date(hire_date);
EXPLAIN SELECT first_name, last_name, salary FROM employees INNER JOIN salaries USING(emp_no) WHERE hire_date > "2015-01-01" AND salary > 85000 \G
-- try to evade filesort, if possible
EXPLAIN SELECT * FROM employees WHERE hire_date = "2000-01-01" ORDER BY last_name \G
ALTER TABLE employees ADD KEY hire_last_name(hire_date,last_name);
EXPLAIN SELECT * FROM employees WHERE hire_date = "2000-01-01" ORDER BY last_name \G
-- no luck here, cannot avoid filesort because range
EXPLAIN SELECT * FROM employees WHERE hire_date > "2000-01-01" ORDER BY last_name \G
--- standalone subquery
SELECT last_name, first_name
FROM employees
WHERE emp_no IN (
SELECT emp_no FROM dept_emp WHERE dept_no = 'd005'
);
--- correlated subquery
SELECT salary
FROM salaries sal
WHERE salary > (
SELECT AVG(salary) FROM salaries WHERE emp_no = sal.emp_no
);
Beware of dead code: https://www.percona.com/blog/2018/07/12/why-mysql-stored-procedures-functions-triggers-bad-performance/
https://www.vividcortex.com/blog/2014/07/10/4-things-to-know-about-mysql-prepared-statements/
https://medium.com/@anna.f/speeding-up-mysql-by-using-materialized-views-282ecbd3a53f
http://www.fromdual.com/mysql-materialized-views
https://www.percona.com/blog/2018/03/19/speed-pattern-matching-queries/