MySQL 4.1 “WHERE … OR … ” results in full table scan

This is a real life example. Case study is, we had performance problems that was narrowed down to a large number of the following query:

SELECT sg.gid FROM Scheduleable_Group sg WHERE sg.domain=’domain.com’ OR sg.sid=’555′;

The database engine is InnoDB on MySQL 4.1.

Continue reading for the analysis and solution.

NOTICE WHEN THE QUERY WITH “OR” IN THE CLAUSE MAKES A FULL TABLE SCAN, but the first two dont:

mysql> explain SELECT sg.gid FROM Scheduleable_Group sg WHERE sg.domain=’domain.com’;
+—-+————-+——-+——+—————+——–+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——–+———+——-+——+————-+
| 1 | SIMPLE | sg | ref | domain | domain | 129 | const | 1 | Using where |
+—-+————-+——-+——+—————+——–+———+——-+——+————-+
1 row in set (0.00 sec)

mysql> explain SELECT sg.gid FROM Scheduleable_Group sg WHERE sg.sid=’555′;
+—-+————-+——-+——+—————+——+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——-+——+————-+
| 1 | SIMPLE | sg | ref | sid | sid | 5 | const | 1 | Using where |
+—-+————-+——-+——+—————+——+———+——-+——+————-+
1 row in set (0.00 sec)

mysql> explain SELECT sg.gid FROM Scheduleable_Group sg WHERE sg.domain=’domain.com’ OR sg.sid=’555′;
+—-+————-+——-+——+—————+——+———+——+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——-+————-+
| 1 | SIMPLE | sg | ALL | domain,sid | NULL | NULL | NULL | 78352 | Using where |
+—-+————-+——-+——+—————+——+———+——+——-+————-+
1 row in set (0.00 sec)

DID YOU SEE THAT? FULL TABLE SCAN WITH THE “OR” IN THE WHERE CLAUSE!!!!!!!!!!!! HELLO, MYSQL 4.1, THAT’S STUPID.

NOTE: THIS IS NOT A PROBLEM IN MYSQL 5.0. MYSQL 4.1 ONLY. SUGGEST AN UPGRADE, IF YOU SUFFER FROM THIS PROBLEM.

SO, TO FIX THIS PROBLEM IN PRODUCTION, EITHER UPGRADE TO MYSQL 5.0, OR CHANGE THE CODE IN THE APPLICATION TO DO THIS IN TWO QUERIES, RATHER THAN ONE. YOU COULD USE “UNION”.

CONCLUSION: THIS QUERY IS FIXED BY BREAKING IT UP INTO TWO QUERIES. ALTERNATIVELY: COULD UPGRADE TO MYSQL 5.0 (AFTER A BIG ROUND OF DEV AND QA, OF COURSE).

Dave.