CODEWITHSUNDEEP
mysql
Mosi
Mosi

Reputation: 1258

Finding nearest matching value in MYSQL table column

Suppose there is a column of doubles in database and i have a target value. Let's call it x. The column is indexed and sorted in database. What is the best way to find the x or nearest value to the x (if there is no x in the column) in this column?

(suppose that the table is really big (more than 10,000,000 rows) and x or nearest value to the x is always stored in last rows(within last 1000 rows but this value is not exact). Query time is critical, I'm seeking for as fast as possible method)

Thanks in advance

Upvotes: 0

Views: 1171

Answers (1)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

The index isn't going to help you to find "nearest value to X", because you cannot index that particular expression if it is variable.

The following query will give you the value of col that's closest to x, along with the id:

SELECT col, id FROM table ORDER BY ABS(x - col) LIMIT 1;

For a large table, that's going to be very slow. If you're absolutely sure that the closest value is within the "last 1000 rows", then you could use a subquery to shave some time off, assuming that "last 1000 rows" means "rows with the highest 1000 id values":

SELECT col, id FROM 
  (SELECT col, id FROM table ORDER BY id DESC LIMIT 1000) t
ORDER BY ABS(x - col) LIMIT 1;

Upvotes: 1

Related Questions