Home / Tricky Select Queries / Question No: 65

I have a buyers table having 500000 rows. I want to find all those buyers where half their discount rate is less than $5. The query I am writing is like below:

SELECT buyer_id, buyer_name FROM buyers WHERE buyer_discount/2<5;

This query takes considerable time to return the desired results. Is there any trick to get it speed up?

Actually, using this query MySQL seems reading every single buyer record. The query part "buyer_discount/2" causing the delay to respond. Every buyer_discount (every record) has to be read in order to divide it by 2. So problem can be solved by adding index and also not to perform any calculations on the buyer_discount column. The formula can be same something like 'x/2 = y' is equal to 'x = y*2'. Therefore, the query can be rewritten, by seeing if the buyer_discount is less than 5*2:

Firstly, add index on the buyer_discount column:

ALTER TABLE buyers ADD INDEX(buyer_discount);

Secondly, rewrite the query to fetch the desired records:

SELECT buyer_id, buyer_name FROM buyers WHERE buyer_discount < 5*2;