Re: Indexes #2

[Paul DuBois]

At 8:23 PM +0300 6/14/1999, Mark Papadakis wrote:
>Hi!
>
>
>Say we have a table
>
>create table xx
>(
>  a1 smallint unsigned not null,
>  a2 smallint unsigned not null,
>  a3 smallint unsigned not null,
>  primary key (a1,a2,a3)
>);
>
>According the the manual:
>
>Keys are used to retreive values withotu consulting the data file, in some
>cases.
>If all used columns for some table and numeric and form a leftmost prefix
>for some key, the values
>may be retrieved from the index tree from greater tree.
>
>So, will the above get the values from the index tree instead from the
>datafile?

There is no select query "above".  ???

>select xx.a2, count(xx.a2) as mc, sum(a3) as mys from xx where xx.a1
>in(1,2,4,5,6) group by a2 having myc=5 order by mys;
>
>A query like this
>select xx.a2, count(xx.a2) as myc from xx where xx.a1 in(1,2,4,5,6) group by
>a2 having myc=5;
>gets all its values from the index tree and it's VERY fast. But, how about
>the previous one ?

Hard to say.  Your first query doesn't look legal.  You have "having myc=5"
but there is no column called myc.  Do you mean mc or mys?

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/