如何在文本开头找到短语（单词）

Question

How find phrase (words) at beginning of text

I need very fast solution for find out if text begins some of known phrases

Phrases I have in Mysql (innodb) table like this:

CREATE TABLE IF NOT EXISTS `phrase` (
  `id` int(10) unsigned NOT NULL,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `text` (`text`)
) ENGINE=InnoDB;

INSERT INTO phrase VALUES (1, 'one');
INSERT INTO phrase VALUES (2, 'one two');
INSERT INTO phrase VALUES (3, 'two');
INSERT INTO phrase VALUES (4, 'three');

Phrase text is ONE OR MULTIPLE WORDS. Table contains about 20.000 rows

Now I get text from user request and need to know if this text begins like any phrase. But I need find out longest phrase in database

I should precache rows to server memory or I can search in mysql table.

I tryed this

 $_REQUEST['text'] = 'one two three';
 $_REQUEST['text'] = explode(' ', $_REQUEST['text']);
 $search = ''; $found = null;
 foreach ($_REQUEST['text'] as $next_word) {
  $search .= (($search == '') ? '' : ' ').$next_word;
  $query = "SELECT SQL_CACHE * FROM phrase WHERE phrase = '{$search}' LIMIT 1;";
  ...
  $row = mysql_fetch_assoc($result);
  if ( ... not found ... ) break;
   else $found = $row;
 }
 print_r($row); // print latest found phrase "one two"

I thing that this way is slowly, because I need FULL TABLE SCAN for EACH WORD

Do you know an faster solution?

Answer 1

instead

$query = "SELECT SQL_CACHE * FROM phrase WHERE phrase = '{$search}' LIMIT 1";

you can do liket this:

$query = "SELECT SQL_CACHE * FROM phrase WHERE phrase like '%{$search}%' LIMIT 1";

Or additionally you can reduce you code like this:

$count = count($_REQUEST['text']);
$where = "";
for($loop=0;$count<$loop;$loop++) {
if($loop!=($count-1))
 $where .= "phrase like '%".$_REQUEST['text'][$loop]."%' OR";
else 
 $where .= "phrase like '%".$_REQUEST['text'][$loop]."%'";
}
$query = "SELECT SQL_CACHE * FROM phrase WHERE $where LIMIT 1;";