SQL Best Practise

Isolating indexed field
//Bad Idea.
Where TO_DAYS(order_created) - TO_DAYS(CURRENT_DATE())>= 7

//Better Idea. CURRENT_DATE() not working for query cache
Where order_created >= CURRENT_DATE() - INTERVAL 7 DAY

//Best Idea
Where order_created >= '11/11/2009' - INTERVAL 7 DAY


Using calculated field
//Bad idea
WHERE email_address LIKE '%.com';

//Better idea
ALTER TABLE CUSTOMERS
ADD COLUMN rv_email_address VARCHAR(80) NOT NULL;
UPDATE customers SET rv_email_address = REVERSE(email_address);
CREATE INDEX ix_rv_email ON customer (rv_email_address(20));

DELIMITER ;;
CREATE TRIGGER trg_bi_cust BEFORE INSERT ON customers
FOR EACH ROW BEGIN
SET NEW.rv_email_address=REVERSE(NEW.email_address);
END;;

//same trigger for BEFORE UPDATE...
//Then SELECT on the new field...
WHERE r_email_address LIKE CONCAT(REVERSE('.com'),'%');