Scott Haneda wrote:
> Mysql 4
>
> I have a frustrating case here, a list of numbers, say, 0 through 30,000 or
> so, however, there are gaps in the numbers, so not purely sequential.
>
> I am faced with selecting one record from the dataset, that's simple,
> however, before that select, I need to make sure the record is there, and if
> it is not, find the one either just before it, or just after it. Whichever
> is closest. If they are the same, lean on either one, I don't really care.
>
> Any suggestion on this one would be appreciated, I can do this with 3
> separate queries, but that is taking too long, since I have to do this with
> two separate datasets and shove this all back out a browser to the user.
It really would be helpful when posting a question like this if you would
actually show us the three queries and tell us how long "too long" is. It would
also help to know the structure of your table.
Scott Haneda wrote:
> Clearing this up a little, I have data like this:
>
> 3, 4, 5, 8, 9, 10
> If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5
> is closest, so I would like 5 back, but both are ok, as I can use server
> side code to determine the closest.
OK, that's clear.
Scott Haneda wrote:
I am thinking UNION and two SQL queries would do this, how is UNION
optimized, is it more or less the same as running two selects?
Usually, but a UNION of what two queries? I won't comment on the efficiency of
a query I haven't seen.
This can be done in one query. You didn't give any details, so I'll make them
up. The table is named scotts_table, the numbers are in the column named val,
and the target value is 413. I'll use user variables for clarity, but they
aren't necessary.
For each row in the table, the distance from that row's val to the target value
is the absolute value of the difference between val and the target value. The
row with the smallest distance is the one you want. Hence,
SET @target = 413;
SELECT *
FROM scotts_table
ORDER BY ABS([EMAIL PROTECTED])
LIMIT 1;
Of course, that's a full-table scan with a filesort, so it's not very efficient.
We can improve on this, however, if we know the size of the largest gap. For
example, if we know that the largest gap is 26, we can do the following:
SET @target = 413;
SET @range=26;
SELECT *
FROM scotts_table
WHERE val BETWEEN (@target - @range) AND (@target + @range)
ORDER BY ABS([EMAIL PROTECTED])
LIMIT 1;
In this case, mysql can use the index on val (You do have an index on val,
right?) to choose the few rows near the target value, before performing the
filesort on just those few matching rows.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
