Re: Sequential Scan with LIMIT

--- John Meinel <john(at)johnmeinel(dot)com> escribió:
> Curt Sampson wrote:
> > On Sun, 24 Oct 2004, John Meinel wrote:
> >
> >
> >>I was looking into another problem, and I found
> something that surprised
> >>me. If I'm doing "SELECT * FROM mytable WHERE col
> = 'myval' LIMIT 1.".
> >>Now "col" is indexed...
> >>The real purpose of this query is to check to see
> if a value exists in
> >>the column,...
> >
> >
> > When you select all the columns, you're going to
> force it to go to the
> > table. If you select only the indexed column, it
> ought to be able to use
> > just the index, and never read the table at all.
> You could also use more
> > standard and more set-oriented SQL while you're at
> it:
> >
> > SELECT DISTINCT(col) FROM mytable WHERE col =
> 'myval'
> >
> > cjs
>
> Well, what you wrote was actually much slower, as it
> had to scan the
> whole table, grab all the rows, and then distinct
> them in the end.
>
> However, this query worked:
>
>
> SELECT DISTINCT(col) FROM mytable WHERE col =
> 'myval' LIMIT 1;
>
>
> Now, *why* that works differently from:
>
> SELECT col FROM mytable WHERE col = 'myval' LIMIT 1;
> or
> SELECT DISTINCT(col) FROM mytable WHERE col =
> 'myval';
>
> I'm not sure. They all return the same information.

of course, both queries will return the same but
that's just because you forced it.

LIMIT and DISTINCT are different things so they behave
and are plenned different.

>
> What's also weird is stuff like:
> SELECT DISTINCT(NULL) FROM mytable WHERE col =
> 'myval' LIMIT 1;

why do you want to do such a thing?

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com