Large table join using "text" as the joining key

two tables:
-----------------------------------------------
1.

CREATE TABLE topics (
cat_id INT(10) UNSIGNED NOT NULL
, topic_id TEXT NOT NULL
)ENGINE=MYISAM;

2.

CREATE TABLE links (
topic_id TEXT NOT NULL
, cat_id INT(10) UNSIGNED
)ENGINE=MYISAM;


SQL:
-----------------------------------------------
explain select * from `links`, `topics` force index(topic_id) WHERE
`links`.`topic_id` = `topics`.`topic_id` ;

Returns:
-----------------------------------------------
-------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+---------+------------------------------------------------+
| 1 | SIMPLE | links | ALL | topic_id | NULL | NULL |
NULL | 4806466 | |
| 1 | SIMPLE | topics | ALL | topic_id | NULL | NULL |
NULL | 715636 | Range checked for each record (index map: 0x2) |


Why key cannot be used? How to modify the query to use the key?
(assume table structre cannot be changed)

Thanks.

howachen@gmail.com wrote:

> two tables:
> -----------------------------------------------
> 1.
>
> CREATE TABLE topics (
> cat_id INT(10) UNSIGNED NOT NULL
> , topic_id TEXT NOT NULL
> )ENGINE=MYISAM;
>
> 2.
>
> CREATE TABLE links (
> topic_id TEXT NOT NULL
> , cat_id INT(10) UNSIGNED
> )ENGINE=MYISAM;
>
>
> SQL:
> -----------------------------------------------
> explain select * from `links`, `topics` force index(topic_id) WHERE
> `links`.`topic_id` = `topics`.`topic_id` ;
>
> Returns:
> -----------------------------------------------
> -------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+------------+------+---------------+------+---------+------+---------+------------------------------------------------+
> | 1 | SIMPLE | links | ALL | topic_id | NULL | NULL |
> NULL | 4806466 | |
> | 1 | SIMPLE | topics | ALL | topic_id | NULL | NULL |
> NULL | 715636 | Range checked for each record (index map: 0x2) |
>
>
> Why key cannot be used? How to modify the query to use the key?
> (assume table structre cannot be changed)
>
> Thanks.
I see nothing that says that the key "cannot" be used, rather that
mysql chose not to use one.

However, having said that, the CREATE TABLE commands that you have
posted do not declare any indexes. mysql cannot use an index if it
doesn't exist. If we assume that the table structure cannot be changed
(as you stated) and that therefore you cannot create an index, then
there remains no index to use.

howachen@gmail.com wrote:

> two tables:
> -----------------------------------------------
> 1.
>
> CREATE TABLE topics (
> cat_id INT(10) UNSIGNED NOT NULL
> , topic_id TEXT NOT NULL
> )ENGINE=MYISAM;
>
> 2.
>
> CREATE TABLE links (
> topic_id TEXT NOT NULL
> , cat_id INT(10) UNSIGNED
> )ENGINE=MYISAM;
>
>
> SQL:
> -----------------------------------------------
> explain select * from `links`, `topics` force index(topic_id) WHERE
> `links`.`topic_id` = `topics`.`topic_id` ;
>
> Returns:
> -----------------------------------------------
> -------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
>
+----+-------------+------------+------+---------------+------+---------+------+---------+------------------------------------------------+
> | 1 | SIMPLE | links | ALL | topic_id | NULL | NULL |
> NULL | 4806466 | |
> | 1 | SIMPLE | topics | ALL | topic_id | NULL | NULL |
> NULL | 715636 | Range checked for each record (index map: 0x2) |
>
>
> Why key cannot be used? How to modify the query to use the key?
> (assume table structre cannot be changed)



The index you specified does not exist.



--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

Captain Paralytic 寫道：

> howachen@gmail.com wrote:
>
> > two tables:
> > -----------------------------------------------
> > 1.
> >
> > CREATE TABLE topics (
> > cat_id INT(10) UNSIGNED NOT NULL
> > , topic_id TEXT NOT NULL
> > )ENGINE=MYISAM;
> >
> > 2.
> >
> > CREATE TABLE links (
> > topic_id TEXT NOT NULL
> > , cat_id INT(10) UNSIGNED
> > )ENGINE=MYISAM;
> >
> >
> > SQL:
> > -----------------------------------------------
> > explain select * from `links`, `topics` force index(topic_id) WHERE
> > `links`.`topic_id` = `topics`.`topic_id` ;
> >
> > Returns:
> > -----------------------------------------------
> > -------------+
> > | id | select_type | table | type | possible_keys | key | key_len
> > | ref | rows | Extra |
> > +----+-------------+------------+------+---------------+------+---------+------+---------+------------------------------------------------+
> > | 1 | SIMPLE | links | ALL | topic_id | NULL | NULL |
> > NULL | 4806466 | |
> > | 1 | SIMPLE | topics | ALL | topic_id | NULL | NULL |
> > NULL | 715636 | Range checked for each record (index map: 0x2) |
> >
> >
> > Why key cannot be used? How to modify the query to use the key?
> > (assume table structre cannot be changed)
> >
> > Thanks.
> I see nothing that says that the key "cannot" be used, rather that
> mysql chose not to use one.
>
> However, having said that, the CREATE TABLE commands that you have
> posted do not declare any indexes. mysql cannot use an index if it
> doesn't exist. If we assume that the table structure cannot be changed
> (as you stated) and that therefore you cannot create an index, then
> there remains no index to use.

sorry, the index is created afterward, as you all can see the index
`topic_id` exist from the explain statement above.

the number of rows of two tables is 4M and 0.7M, I can't see the reason
of not using the key.


thanks...

howachen@gmail.com wrote:
> Captain Paralytic 寫道：
>
> > howachen@gmail.com wrote:
> >
> > > two tables:
> > > -----------------------------------------------
> > > 1.
> > >
> > > CREATE TABLE topics (
> > > cat_id INT(10) UNSIGNED NOT NULL
> > > , topic_id TEXT NOT NULL
> > > )ENGINE=MYISAM;
> > >
> > > 2.
> > >
> > > CREATE TABLE links (
> > > topic_id TEXT NOT NULL
> > > , cat_id INT(10) UNSIGNED
> > > )ENGINE=MYISAM;
> > >
> > >
> > > SQL:
> > > -----------------------------------------------
> > > explain select * from `links`, `topics` force index(topic_id) WHERE
> > > `links`.`topic_id` = `topics`.`topic_id` ;
> > >
> > > Returns:
> > > -----------------------------------------------
> > > -------------+
> > > | id | select_type | table | type | possible_keys | key | key_len
> > > | ref | rows | Extra |
> > > +----+-------------+------------+------+---------------+------+---------+------+---------+------------------------------------------------+
> > > | 1 | SIMPLE | links | ALL | topic_id | NULL | NULL |
> > > NULL | 4806466 | |
> > > | 1 | SIMPLE | topics | ALL | topic_id | NULL | NULL |
> > > NULL | 715636 | Range checked for each record (index map: 0x2) |
> > >
> > >
> > > Why key cannot be used? How to modify the query to use the key?
> > > (assume table structre cannot be changed)
> > >
> > > Thanks.
> > I see nothing that says that the key "cannot" be used, rather that
> > mysql chose not to use one.
> >
> > However, having said that, the CREATE TABLE commands that you have
> > posted do not declare any indexes. mysql cannot use an index if it
> > doesn't exist. If we assume that the table structure cannot be changed
> > (as you stated) and that therefore you cannot create an index, then
> > there remains no index to use.
>
> sorry, the index is created afterward, as you all can see the index
> `topic_id` exist from the explain statement above.
>
> the number of rows of two tables is 4M and 0.7M, I can't see the reason
> of not using the key.
>
>
> thanks...

One thing I don't understand is why mysql doesn't complain about
topic_id being an ambiguous reference?