SQL查找條件

誤用或濫用的地雷與使用建議：

---- 使用!=、<>、NOT查詢，會變全掃
SELECT * FROM user WHERE age != 20
SELECT * FROM user WHERE age <> 20
SELECT * FROM user WHERE age NOT IN(20)

---- 用 like 且 ％ 作為前綴，會變全掃
SELECT * FROM user WHERE name like '%-Mark'

---- 誤用 OR
-- index column: {age} / bad
SELECT * FROM user WHERE age = 18 OR name = 'C-Ian';
-- index column: {age},{name} / good
SELECT * FROM user WHERE age = 18 OR name = 'C-Ian';
-- index column: {age} with AND / good
SELECT * FROM user WHERE age = 18 AND name = 'C-IAn'; 

---- 在WHERE欄位進行運算
-- index column: {age} / bad
SELECT * FROM user WHERE age/2 = 18;
-- good
SELECT * FROM user WHERE age/2 = 18;

---- 使用某些函數
-- full scan while using RAND()
SELECT * FROM test.user where age >= RAND();

---- SELECT * FROM
-- index column: {name} 
-- 覆蓋索引失效。要先至 secondary index 查找再去 clustered Index 抓資料 / bad
SELECT * FROM user;
-- secondary index only/ good
SELECT name FROM user;

---- 使用 ORDER BY RAND()，耗費空間做排序
SELECT * FROM test.user where age <= 18 ORDER BY RAND();