In your query your still asking for all rows in the table. To utilize an
index, a restrictive query often works for example:
Assuming you have an index on distance ---
SELECT * FROM loeb WHERE distance = 2;
The random tottid results from your grouping by distance. In effect you
have made groups that all have the same distance. If you take one of those
groups and look at another attribute (say tottid...) that attribute is
likely to have many values (say 1, 5, 5, & 9). The query you gave below is
asking for one of those many possible values from the group. You could ask
for the MIN, MAX, AVG of those values and get a consistent answer (MAX = 9,
MIN = 1, AVG = 5). Indexes have nothing to do with your query returning
different tottid's every time. I can't tell from your query what you really
wanted or I would've tried to point out how to do it.
Good luck,
Jeff
-----Original Message-----
From: Wix,Christian XCW [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 30, 2001 10:05 AM
To: 'Thalis A. Kalfigopoulos'
Cc: '[EMAIL PROTECTED]'
Subject: SV: index troubles
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)
// 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
---------------------------------------------------------------------
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
