From: Dan Nelson
Date: July 23 2002 2:17pm
Subject: Re: Surrounding Rows
List-Archive: http://lists.mysql.com/mysql/115341
Message-Id: <20020723141743.GB82383@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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