MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Tricky Select Queries / Question No: 65

An important trick to speed up select query.

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?

Answer No: 65

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;

Related MySQL FAQs to the Above FAQ

How-to-do-numeric-sorting-within-a-string-field-in-MySQL How to do numeric sorting within a string field in MySQL?

How-to-take-data-back-up-of-single-table How to take data back up of single table?

How-to-find-duplicate-records-from-a-table-in-MySQL How to find duplicate records from a table in MySQL?

How-to-get-last-top-records-in-asceding-order How to get last top records in asceding order?

How-to-move-table-from-one-to-another-MySQL-database How to move table from one to another MySQL database?

How-to-get-parent-table-child-table-and-grandchild-table-aggregates-in-MySQL How to get parent table child table and grandchild table aggregates in MySQL?

How-to-skip-repeating-values-from-the-query-results How to skip repeating values from the query results?

How-to-retrieve--one-child-row-against-each-parent-id How to retrieve one child row against each parent id?

How-to-find-the-books-which-have-authors-with-more-than-one-book-in-the-table How to find the books which have authors with more than one book in the table?

How-to-count-the-number-of-rows-containing-APOSTROPHE-in-MySQL How to count the number of rows containing APOSTROPHE in MySQL?

How-to-compare-data-of-two-tables-of-two-different-databases-in-MySQL How to compare data of two tables of two different databases in MySQL?

How-to-retreive-multiple-records-without-a-reference-table How to retreive multiple records without a reference table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.