List:General Discussion« Previous MessageNext Message »
From:Jan Peuker Date:July 23 2002 6:35am
Subject:Re: Surrounding Rows
View as plain text  
Hi Dan,

that's exactly what I do. But it's not that fast, I missed 2 '0''s, the
table has 10000 rows (which is still not big, I think), but zip is only
index. The "procedure" takes over a second, and that (that's the point) for
every user and every query.
But now I think it's the only way.

jan

----- Original Message -----
From: "Dan Nelson" <dnelson@stripped>
To: "Jan Peuker" <jan.peuker@stripped>
Cc: "Gurhan Ozen" <ergenekon@stripped>; <mysql@stripped>
Sent: Monday, July 22, 2002 11:35 PM
Subject: Re: Surrounding Rows


> In the last episode (Jul 22), Jan Peuker said:
> > What I want to do is, select the 10(say, a value) nearest values to a
> > given number. The easiest way would be "near 5" shows 1..10. The
> > problem is, the number should affect rows, not values, because values
> > are not unique nor in order. What I want to do is a query like
> > "SELECT code FROM zip WHERE zip=12345 SURROUND 5,5" or "SURROUND 10".
> > At the moment, I create a new table w/ an autoincrement and do a
> > select...insert, then I select first the id and then id-5 and id+5.
> > This a a) very slow and redundant b) afaik not very safe.
>
> So what you really want is
>
> CREATE TEMPORARY TABLE tmp
>  SELECT code FROM mytable WHERE zip < 12345 ORDER BY zip DESC LIMIT 5;
> INSERT INTO tmp
>  SELECT code FROM mytable WHERE zip = 12345;
> INSERT INTO tmp
>  SELECT code FROM mytable WHERE zip > 12345 ORDER BY zip LIMIT 5;
> SELECT code FROM tmp ORDER BY zip;
> DROP TABLE tmp;
>
> That will use the zip index, so it should run fast.
>
> --
> Dan Nelson
> dnelson@stripped

Thread
Surrounding RowsJan Peuker22 Jul
  • Re: Surrounding RowsGurhan Ozen22 Jul
  • Re: Surrounding RowsJan Peuker22 Jul
    • Re: Surrounding RowsDavid Felio22 Jul
    • RE: Surrounding RowsGordon22 Jul
    • Re: Surrounding RowsDan Nelson22 Jul
  • Re: Surrounding RowsGurhan Ozen22 Jul
  • Re: Surrounding RowsJan Peuker23 Jul
    • Re: Surrounding RowsDan Nelson23 Jul