List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 23 2002 2:17pm
Subject:Re: Surrounding Rows
View as plain text  
In the last episode (Jul 23), Jan Peuker said:
> Dan Nelson wrote:
> > 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;
>
> 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.

Even without indexes, a query on a table with only 10000 rows should be
just about instantaneous.  I tried the exact same set of commands on a
4 million record table here, and the whole set of queries takes .04
second.

Try explaining each select and make sure they are using an index.

-- 
	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