MySQL word+word search

I have two tables: Dictionary, Names. Both tables have large number of rows (like 200.000).

Columns in Dictionary: id, word

Columns in Names: id, name

I need to select all Names which are Dictionary.word+Dictionary.word (something like concatenation of same column, for all variation)

Also, I need fast query, not something that would take hours to load... About this speed - I would be satisfied if requirements are set to something li开发者_高级运维ke "min 5 chars" for column/word comparision...

This should work:

SELECT `N`.`name`
FROM `Names` `N`
INNER JOIN `Dictionary` `D` ON `N`.`name` = CONCAT(`D`.`word`, `D`.`word`);

New query:

SELECT `N`.`name`
FROM `Names` `N`
INNER JOIN `Dictionary` `D`
INNER JOIN `Dictionary` `D1`
WHERE `N`.`name` = CONCAT(`D`.`word`, `D1`.`word`);

this tries to get the answer using the index that is on d.word.

SELECT *
FROM Names n
JOIN Dictionary d1
JOIN Dictionary d2
WHERE 
    LEFT(n.name, LENGTH(n.name)/2) = d1.word
AND RIGHT(n.name, LENGTH(n.name)/2) = d2.word
AND n.name = CONCAT(d1.word, d2.word);

Not sure about speed at all:

SELECT n.*
FROM Names AS n
  JOIN Dictionary AS d1
    ON n.name LIKE CONCAT(d1.word, '%')
  JOIN Dictionay AS d2
    ON d1.id <= d2.id
WHERE n.name = CONCAT(d1.word, d2.word))

Try this:

SELECT * FROM `Names` `n`
INNER JOIN `Dictionary` `d`
ON `d`.`word` IS NOT NULL
AND (`n`.`name` LIKE CONCAT(`d`.`word`,`d`.`word`));

Update:

SELECT * FROM `Names` `n`
INNER JOIN `Dictionary` `d1`
INNER JOIN `Dictionary` `d2`
        ON `n`.`name` = CONCAT(`d1`.`word`,`d2`.`word`) 
        OR `n`.`name` LIKE CONCAT(`d1`.`word`,`_%`) 
        OR `n`.`name` LIKE CONCAT(`d2`.`word`,`_%`)