Professional Web Applications Themes

Large table join using "text" as the joining key - MySQL

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 | ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Large table join using "text" as the joining key


    com wrote:
     
    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.

    Captain Guest

  3. #3

    Default Re: Large table join using "text" as the joining key

    com wrote:
     
    +----+-------------+------------+------+---------------+------+---------+------+---------+------------------------------------------------+ 



    The index you specified does not exist.



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

  4. #4

    Default Re: Large table join using "text" as the joining key


    Captain Paralytic 寫道:
     
    > 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.[/ref]

    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 Guest

  5. #5

    Default Re: Large table join using "text" as the joining key


    com wrote: 
    > > 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.[/ref]
    >
    > 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...[/ref]

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

    Captain Guest

Similar Threads

  1. Text as paths, when below 32 point, "l" and"-" become strokes not boxes
    By Brad_Wallwork@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: January 23rd, 02:19 AM
  2. #39657 [Opn]: The extended table-specification "database.table" creates errors
    By w dot kaiser at fortune dot de in forum PHP Bugs
    Replies: 0
    Last Post: November 28th, 06:29 AM
  3. Replies: 0
    Last Post: December 2nd, 11:18 AM
  4. How to make the "search text" feature work with non "txt" file
    By Sharon F in forum Windows XP/2000/ME
    Replies: 0
    Last Post: June 30th, 07:43 PM
  5. Replies: 5
    Last Post: January 13th, 01:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139