Entries Published On July, 2012
Optimizing OR’ed WHERE Clauses Between JOIN’ed Tables
OR conditions are normally difficult to optimize when used on different columns, a pain when the columns are of range conditions and worst when done between 2 or more different tables. Look at my example below, the original query is trying to find rows which conditions are based on columns from the JOIN’ed tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT -> e.emp_no, birth_date, first_name, -> last_name, gender, hire_date, -> salary, from_date, to_date -> FROM employees e -> INNER JOIN salaries s ON (e.emp_no = s.emp_no) -> WHERE e.hire_date BETWEEN '1990-06-01 00:00:00' AND '1990-07-01 00:00:00' OR -> s.from_date BETWEEN '1990-06-01 00:00:00' AND '1990-07-01 00:00:00' -> ORDER BY e.emp_no, hire_date, from_date \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: e type: ALL possible_keys: PRIMARY,hire_date key: NULL key_len: NULL ref: NULL rows: 300547 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s type: ref possible_keys: PRIMARY,emp_no,from_date key: PRIMARY key_len: 4 ref: employees.e.emp_no rows: 4 Extra: Using where 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (employees) > SHOW CREATE TABLE employees \G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`), KEY `hire_date` (`hire_date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql [localhost] {msandbox} (employees) > SHOW CREATE TABLE salaries \G *************************** 1. row *************************** Table: salaries Create Table: CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), KEY `from_date` (`from_date`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
…