List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 11 1999 11:59am
Subject:MYSQL aliases weirdness :(
View as plain text  
>>>>> "Wico" == Wico de Leeuw <wico@stripped> writes:

Wico> Hiya folks can someone explain this to me:
Wico> (when a table is alliased twice or only the fist will use the ref :(
Wico> just take a look at the examples should say enough)

Wico> using  mysql 3.22.26a

Wico> # --------------------------------------------------------
Wico> #
Wico> # Table structure for table 'word'
Wico> #

Wico> CREATE TABLE word (
Wico> word_id int(11) NOT NULL auto_increment,
Wico> word char(35) NOT NULL,
Wico> soundex char(4) NOT NULL,
Wico> PRIMARY KEY (word_id),
Wico> UNIQUE word (word),
Wico> KEY soundex (soundex)
Wico> );

Wico> SQL-query:
Wico> EXPLAIN SELECT *
Wico> FROM word AS WA, word as WB
Wico> WHERE WA.soundex = 'C600' AND WB.soundex = 'C600'

Wico> table type  possible_keys key     key_len ref  rows  Extra
Wico> WA    ref   soundex       soundex 4       C600  5
Wico> WB    range soundex       soundex               5

Wico> SQL-query:
Wico> EXPLAIN SELECT *
Wico> FROM word AS WA, word as WB
Wico> WHERE WA.soundex = 'C600' AND WB.soundex = 'P420'

Wico> table type  possible_keys key     key_len ref  rows  Extra
Wico> WA    ref   soundex       soundex  4      C600  5
Wico> WB    range soundex       soundex               16


Hi!

I agree this is strange, but in this case 'range' should be about as
fast as 'ref'.

Any change you can test this with 3.23 and if you still get the
problem, you can maybe ftp to ftp://www.mysql.com/pub/mysql/secret a
dump of the table so that I can check this?

Regards,
Monty
Thread
MYSQL aliases weirdness :(Wico de Leeuw21 Oct
  • MYSQL aliases weirdness :(Michael Widenius11 Nov
    • Re: MYSQL aliases weirdness :(Wico de Leeuw12 Nov