> On Thu, Oct 14, 1999 at 10:01:44AM -0400, elvis@stripped wrote:
> > mysql> describe wb_users ;
> > +---------+------------------+------+-----+---------+-------+
> > | Field | Type | Null | Key | Default | Extra |
> > +---------+------------------+------+-----+---------+-------+
> > | project | int(10) unsigned | | MUL | 0 | |
> > | user | varchar(255) | | MUL | | |
> > +---------+------------------+------+-----+---------+-------+
> >
> > mysql> describe users ;
> > +------------+---------------------+------+-----+---------------------+-------+
> > | Field | Type | Null | Key | Default | Extra |
> > +------------+---------------------+------+-----+---------------------+-------+
> > | fn | varchar(255) | | | | |
> > | ln | varchar(255) | | | | |
> > | username | varchar(255) | | PRI | | |
> > +------------+---------------------+------+-----+---------------------+-------+
> >
> >
> > mysql> DESCRIBE SELECT fn,ln FROM users,wb_users WHERE
> wb_users.user=users.username AND project=1;
> > +----------+------+---------------+------+---------+------+------+------------+
> > | table | type | possible_keys | key | key_len | ref | rows | Extra |
> > +----------+------+---------------+------+---------+------+------+------------+
> > | users | ALL | PRIMARY | NULL | NULL | NULL | 7 | |
> > | wb_users | ALL | project,user | NULL | NULL | NULL | 4 | where used |
> > +----------+------+---------------+------+---------+------+------+------------+
> > 2 rows in set (0.01 sec)
This is version 3.22.13-beta on a Solaris box. (sparc)
>
> Please add the output of
>
> SHOW INDEX FROM wb_users
mysql> show index from wb_users ;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality
| Sub_part |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+
| wb_users | 1 | project | 1 | project | A | NULL
| NULL |
| wb_users | 1 | user | 1 | user | A | NULL
| NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+
2 rows in set (0.03 sec)
> SELECT COUNT(*) FROM wb_users
mysql> select count(*) from wb_users ;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)
> SELECT COUNT(*) FROM users
mysql> select count(*) from users ;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
>
> > Why isn't the key used?
>
> What is the output of
>
> EXPLAIN SELECT STRAIGHT_JOIN fn,ln FROM wb_users,users WHERE
> wb_users.user=users.username AND project=1
mysql> EXPLAIN SELECT STRAIGHT_JOIN fn,ln FROM wb_users,users WHERE
> wb_users.user=users.username AND project=1 ;
+----------+--------+---------------+---------+---------+---------------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+----------+--------+---------------+---------+---------+---------------+------+-------+
| wb_users | system | project,user | NULL | NULL | NULL | 1 | |
| users | const | PRIMARY | PRIMARY | 255 | wb_users.user | 1 | |
+----------+--------+---------------+---------+---------+---------------+------+-------+
2 rows in set (0.00 sec)
Thanks again,
Bill
>
> [...]
> > Is it because the field names are different?
> [...]
>
> No. The names of the columns are irrelevant.
>
> Bye,
>
> Benjamin.
>
