|
|
Posted by Bret Hughes on 02/18/05 07:12
On Thu, 2005-02-17 at 22:02, Jason Petersen wrote:
> On 17 Feb 2005 19:28:18 -0600, Bret Hughes <bhughes@elevating.com> wrote:
> > On Thu, 2005-02-17 at 18:24, Sebastian wrote:
> > > Hello,
> > > im working on an article system and looking to avoid running three queries.
> > > example, i have this query:
> > >
> > > SELECT id,title FROM articles WHERE id=$_GET[id]
> > >
> > > now say $_GET[id] = 5
> > >
> > > I would like to get the previous id 4 and the next id 6 (if there is one)
> > > so i can do something like:
>
> I would do something like:
>
> $theId = $_GET['id'];
> $ids = $theId-1 . ", ". $theId ", ". $theId+1;
> $query = "SELECT id, title FROM articles WHERE id IN ($ids)";
>
That is a good approach I wonder if there is any difference in
performance? ...
To answer my own question before I even post it:
A quick test or two shows that on a postgres table with about 45K rows
both ways used an index scan. on a table with about 35 rows the < >
always used a index scan but the in() used a seq scan. I am sure mysql
would use the index both ways as well but I do not know. Seems like the
smaller the number of elements used the better off you are with the in()
deal since each element in the index had to be compared to each of the
numbers where as with the <> approach only two comparisions have to be
made regardless of the range.
This is not to say my approach was better I am just thinking out loud.
Bret
Navigation:
[Reply to this message]
|