CODEWITHSUNDEEP
phpmysqlsql
user5332
user5332

Reputation: 774

How find phrase (words) at beginning of text

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?

Upvotes: 0

Views: 81

Answers (3)

user3206387
user3206387

Reputation: 1

You can use an index, so you will avoid the full table scan and will be able to access the exact row at first shot each time.

create index btree on test(phrase)

Upvotes: 0

ragol
ragol

Reputation: 546

You could use regular expressions like so:

SELECT * FROM phrase WHERE `text` REGEXP '^(one (two (three)?)?)';

This works without foreach loop as the regexp is greedy and produces the longest match possible. It uses the index only and does not do any table scan so it is also fast.

You have to be careful with user input, though (as always).

Upvotes: 0

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

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;";

Upvotes: 1

Related Questions