On Mon, 30 Apr 2001, Wix,Christian XCW wrote:
> But why doesn't this work then:
> select name, tottid, distance, min(mintid) from loeb group by distance;
> (tottid seems to be random)
>
> mysql> explain select name, tottid, distance, min(mintid) from loeb group by
> distance;
> +-------+------+---------------+------+---------+------+------+-------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +-------+------+---------------+------+---------+------+------+-------+
> | loeb | ALL | NULL | NULL | NULL | NULL | 90 | |
> +-------+------+---------------+------+---------+------+------+-------+
> 1 row in set (0.00 sec)
>
Because your index is on (mintid,name) and you are grouping by distance. The group by
will force the table to be sorted (i think) and from each group you then select the
min(mintid),etc. Having an index on things that are in your select clause is of no
help. I bet (not much, but still I'd bet something) that if you added a where clause
involving (mintid) you'd see you index utilised.
If you want to help this particular query, add an index on (distance)
regards,
thalis
:w
> // Chris
>
> > -----Oprindelig meddelelse-----
> > Fra: Thalis A. Kalfigopoulos [SMTP:[EMAIL PROTECTED]]
> > Sendt: 30. april 2001 15:42
> > Til: Wix,Christian XCW
> > Cc: '[EMAIL PROTECTED]'
> > Emne: Re: index troubles
> >
> > Hello,
> >
> > The index you have created is just fine and should be utilized as long as
> > you perform a query that needs to use an index.
> > Your query:
> > select a,b from t;
> > doesn't have any conditions imposed upon the table's rows. It just asks
> > for all the them. Simple file scan.
> >
> > regards,
> > thalis
> >
> >
> > On Mon, 30 Apr 2001, Wix,Christian XCW wrote:
> >
> > > Hi!
> > > I have some troubles with my index.
> > > I want to be able to use an index (test=(mintid, name)). I will use the
> > > index when I write: select mintid, name from loeb;
> > > I have created an index but it doesn't seem to work. Why?
> > > // Chris - Copenhagen
> > >
> > > mysql> show index from loeb;
> > >
> > +-------+------------+----------+--------------+-------------+-----------+
> > --
> > > -----------+----------+
> > > | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
> > |
> > > Cardinality | Sub_part |
> > >
> > +-------+------------+----------+--------------+-------------+-----------+
> > --
> > > -----------+----------+
> > > | loeb | 0 | PRIMARY | 1 | id | A
> > |
> > > 90 | NULL |
> > > | loeb | 1 | rekord | 1 | mintid | A
> > |
> > > NULL | NULL |
> > > | loeb | 1 | test | 1 | mintid | A
> > |
> > > NULL | NULL |
> > > | loeb | 1 | test | 2 | name | A
> > |
> > > NULL | NULL |
> > >
> > +-------+------------+----------+--------------+-------------+-----------+
> > --
> > > -----------+----------+
> > > 4 rows in set (0.00 sec)
> > >
> > > mysql> show columns from loeb;
> > > +----------+-------------+------+-----+----------+----------------+
> > > | Field | Type | Null | Key | Default | Extra |
> > > +----------+-------------+------+-----+----------+----------------+
> > > | name | varchar(20) | | | | |
> > > | distance | double(3,1) | | | 0.0 | |
> > > | dato | date | YES | | NULL | |
> > > | id | int(11) | | PRI | 0 | auto_increment |
> > > | tottid | time | | | 00:00:00 | |
> > > | mintid | time | | MUL | 00:00:00 | |
> > > | art | varchar(10) | YES | | NULL | |
> > > +----------+-------------+------+-----+----------+----------------+
> > > 7 rows in set (0.00 sec)
> > >
> > > mysql> explain select mintid, name from loeb;
> > > +-------+------+---------------+------+---------+------+------+-------+
> > > | table | type | possible_keys | key | key_len | ref | rows | Extra |
> > > +-------+------+---------------+------+---------+------+------+-------+
> > > | loeb | ALL | NULL | NULL | NULL | NULL | 90 | |
> > > +-------+------+---------------+------+---------+------+------+-------+
> > > 1 row in set (0.00 sec)
> > >
> > >
> > > ---------------------------------------------------------------------
> > > Before posting, please check:
> > > http://www.mysql.com/manual.php (the manual)
> > > http://lists.mysql.com/ (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> > >
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
