MySQL Volltextsuche unter 4 Zeichen (bzw. 3)

mgutt · Beitrag von **mgutt** » 30.10.2006 22:09

Ich möchte gerne mein Forum auf MySQL Volltextsuche umstellen bzw. das habe ich schon verwirklicht. Dummerweise kann ich damit aber nicht nach 3-stelligen Wörtern suchen:

Code: Alles auswählen

SELECT DISTINCT p.topic_id FROM phpbb_posts_text pt, phpbb_posts p, phpbb_topics t WHERE p.post_id = pt.post_id AND t.topic_id = p.topic_id AND p.forum_id IN(0, 18, 15, 20) AND MATCH(pt.post_subject, pt.post_sub_title, pt.post_text) AGAINST('+abc' IN BOOLEAN MODE) ORDER BY t.topic_last_post_id DESC LIMIT 1000

Also "+abc" gibt mir Null Ergebnisse aus. Dagegen würde +abcd problemlos funktionieren.

Welche Alternative gibt es, die genauso schnell arbeitet? (insbesondere, wenn ich bereits einen fulltext index gesetzt habe)

Gruß

Miriam · Beitrag von **Miriam** » 30.10.2006 23:10

Das es kein nutzbares Ergebnis gibt wird daran liegen, dass es zu viele Treffer gibt und somit die Relevanz der Ergebnisse eher eine Irrelevanz ist. Das Suchwort wird dann als Stopwort betrachtet.

mgutt · Beitrag von **mgutt** » 30.10.2006 23:57

Nein, lag an den Grundeinstellungen von Fulltext. Habe das auf 3 Zeichen heruntergesetzt:
http://dev.mysql.com/doc/refman/5.0/en/ ... uning.html

Danke!

IPB_Flüchtling · Beitrag von **IPB_Flüchtling** » 03.11.2006 02:36

Ahoi mgutt,

dürfte ich Dich fragen, wie Du die Volltextsuche verwirklicht hast? Ich wäre daran nämlich auch sehr interessiert. Es gibt diesbezüglich einen Mod von fanrpg:

http://www.phpbb.de/viewtopic.php?t=113095

Das Problem mit diesem Mod ist aber, dass die Suche nur noch mit vollständigen Suchbegriffen funktioniert.

Ein Beispiel: Wenn ich nach blumenwiese suche, erhalte ich Ergebnisse. Suche ich aber nur nach blumenw oder blumenw* wird kein Ergebnis gefunden. Dieses Phänomen hat auch Kollegin emily bestätigt.

Dabei könnte man durch die Umstellung auf die Volltextsuche nicht zuletzt die Datenbank kleiner halten.

Schon jetzt ein schönes Wochenende!
Wolfgang

mgutt · Beitrag von **mgutt** » 03.11.2006 11:10

Die Umstellung auf die Volltextsuche ist eine Option in Categories Hierarchy. Es ist aber um einiges umfangreicher, als das was fan rpg gemacht hat. Liegt hauptsächlich an der Suchmethode selbst. Da man die Begriffe entsprechend vorbereiten muss.

Die Klasse, die speziell die Suchwörter aufbereitet und den Query generiert ist diese:

Code: Alles auswählen

class search_fulltext extends search
{
	function get_drop_chars()
	{
		$drop_char_match = array('^', '$', ';', '#', '&', '(', ')', '<', '>', '"', ',', '@', '?', '%', '~', '.', '[', ']', '{', '}', ':', '/', '=', '!');
		$drop_char_replace = array_pad(array(), count($drop_char_match), ' ');

		// remove silent chars
		$drop_char_match = array_merge($drop_char_match, array('`', '\'', '\\'));
		$drop_char_replace = array_merge($drop_char_replace, array('', '', ''));

		return array($drop_char_match, $drop_char_replace);
	}

	function prepare_keywords()
	{
		$this->highlight = '';

		// deal with first char
		$dft_operator = $this->parms['search_terms'] ? '+' : '|';
		$last_operator = $dft_operator;
		for ( $i = count($this->keywords) - 1; $i >= 0; $i-- )
		{
			$operator = substr($this->keywords[$i], 0, 1);
			$word = substr($this->keywords[$i], 1);
			if ( !in_array($operator, array('+', '-', '|')) )
			{
				$word = $operator . $word;
				$operator = ($i == 0) ? $last_operator : $dft_operator;
				$this->keywords[$i] = $operator . $word;
			}
			if ( strlen($word) >= WORD_MIN_LENGTH )
			{
				if ( $operator != '-' )
				{
					$this->highlight .= ' ' . $word;
				}
				$last_operator = ($operator == '|') ? '|' : '+';
				if ( strpos(' ' . $word, '*') )
				{
					$word = trim(str_replace('*', '', $word)) . '*';
					$this->keywords[$i] = $operator . $word;
				}
			}
			else
			{
				unset($this->keywords[$i]);
			}
		}
		$this->keywords = empty($this->keywords) ? array() : array_keys(array_flip($this->keywords));
		$this->highlight = urlencode(phpbb_rtrim(trim($this->highlight), '\\'));
	}

	function get_order()
	{
		// deal with order by
		$sqlorder = array();
		switch ( $this->parms['search_sort_by'] )
		{
			case 1: // Sort_Post_Subject
				if ( $this->parms['show_results'] == 'posts' )
				{
					$sqlorder = array(
						'order' => 'pt.post_subject' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
					);
					break;
				}
				else
				{
					$this->parms['search_sort_by'] = 2;
				}
			case 2: // Sort_Topic_Title
				$sqlorder = array(
					'order' => 't.topic_title' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
					'from' => ', ' . TOPICS_TABLE . ' t',
					'where' => 't.topic_id = p.topic_id',
				);
				break;
			case 3: // Sort_Author
				if ( $this->parms['show_results'] == 'posts' )
				{
					$sqlorder = array(
						'order' => 'u.username' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
						'from' => ', ' . USERS_TABLE . ' u',
						'where' => 'u.user_id = p.poster_id',
					);
				}
				else
				{
					$sqlorder = array(
						'order' => 't.topic_poster' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
						'from' => ', ' . TOPICS_TABLE . ' t',
						'where' => 't.topic_id = p.topic_id',
					);
				}
				break;
			case 4: // Sort_Forum
				$sqlorder = array(
					'order' => 'f.forum_order' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
					'from' => ', ' . FORUMS_TABLE . ' f',
					'where' => 'f.forum_id = p.forum_id',
				);
				break;
			default: // Sort_Time
				if ( $this->parms['show_results'] == 'posts' )
				{
					$sqlorder = array(
						'order' => 'p.post_time' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
					);
				}
				else
				{
					$sqlorder = array(
						'order' => 't.topic_last_post_id' . ($this->parms['search_sort_dir'] == 'DESC' ? ' DESC' : ''),
						'from' => ', ' . TOPICS_TABLE . ' t',
						'where' => 't.topic_id = p.topic_id',
					);
				}
				break;
		}
		return $sqlorder;
	}

	function read_search_ids()
	{
		global $db, $limiter;

		$this->search_ids = array();

		$sql_match_fields = '';
		switch ( $this->parms['search_fields'] )
		{
			case 2: // title only
				$sql_match_fields = 'pt.post_subject, pt.post_sub_title';
				break;
			case 0: // message text only
				$sql_match_fields = 'pt.post_text';
				break;
			default: // title + message text
				$sql_match_fields = 'pt.post_subject, pt.post_sub_title, pt.post_text';
				break;
		}

		// order by
		$sqlorder = $this->get_order();

		// field id
		$sql_field_id = ($this->parms['show_results'] == 'posts') ? 'pt.post_id' : 'DISTINCT p.topic_id';
		$field_id = ($this->parms['show_results'] == 'posts') ? 'post_id' : 'topic_id';

		// build the request
		$sql = 'SELECT ' . $sql_field_id . '
					FROM ' . POSTS_TEXT_TABLE . ' pt, ' . POSTS_TABLE . ' p' . $sqlorder['from'] . '
					WHERE p.post_id = pt.post_id' . (empty($sqlorder['where']) ? '' : '
						AND ' . $sqlorder['where']) . '
						AND p.forum_id IN(' . implode(', ', $this->forum_ids) . ')' . (empty($this->user_ids) ? '' : '
						AND p.poster_id IN(' . implode(', ', $this->user_ids) . ')') . (empty($this->parms['topic_days']) ? '' : '
						AND p.post_time >= ' . (time() - ($this->parms['topic_days'] * 86400))) . (empty($this->keywords) ? '' : '
						AND MATCH(' . $sql_match_fields . ') AGAINST(\'' . $db->sql_escape_string(str_replace('|', '', implode(' ', $this->keywords))) . '\' IN BOOLEAN MODE)') . '
					ORDER BY ' . $sqlorder['order'] . '
					LIMIT ' . intval($limiter);
		$result = $db->sql_query($sql, false, __LINE__, __FILE__);
		while ( $row = $db->sql_fetchrow($result) )
		{
			$this->search_ids[] = intval($row[$field_id]);
		}
		$db->sql_freeresult($result);
	}
}

Man sollte bei der Volltextsuche auch einen Fulltext-Index auf title und text setzen, um beide Felder durchsuchen zu können. So wie ich das verstanden habe ist der "IN BOOLEAN MODE" wichtig, da ohne ihn SQL die Ergebnisse grundsätzlich erstmal nach eigener Relevanz sortiert. Soll heißen mit diesem Mode erspart man der Datenbank unnötige Arbeit.

Es ist auf jedenfall richtig, dass man mindestens 50% schneller Beiträge absenden kann. Das kann sogar viel mehr sein, denn umso mehr Beiträge in der Datenbank sind, umso länger braucht die wordlist bis sie abgearbeitet wurde.

Klar hat das ganze auch Nachteile. Soll heißen, dass die Suche langsamer wird. Aber das ist zu vernachlässigen, da der Anwender problemlos auf das Ergebnis wartet. Und ich rede jetzt hier nicht von Sekunden, sondern vielleicht max. 0,5 Sekunden Unterschied.

Ach ja und die Mindestanzahl von 4 war für mich ein Ausschlussparameter bis ich die entsprechende Einstellung gefunden habe und sie auf 3 reduzieren konnte. Beispiel: Jemand such nach: mod, faq, acp, sql, php, cgi, bin, usw., wenn man aus der Sicht dieses Forums hier gehen würde. Daher finde ich die Reduzerierung auf 3 doch schon sehr wichtig.

Gruß

Ein Standard-phpBB ist optimal für Google ausgestattet. Es gibt keinerlei Nachteile. (Dennis Böge)

guter Witz

Und das wo phpBB.de den besten Gegenbeweis mit knapp 50.000 Seiten double Content liefert:
http://www.google.de/search?q=allinurl: ... =&filter=0

Und das ist nur ein Beispiel und umfasst schon 15% des Gesamtindexes.

Weitere 3.500 Seiten kommen durch das Archiv:
http://www.google.de/search?hl=de&q=sit ... v%2F&meta=

Und weitere 15.000 Seiten durch previous und next Ansicht:
http://www.google.de/search?q=allinurl: ... =&filter=0

Soll ich noch mehr sagen

Gruß

IPB_Flüchtling · Beitrag von **IPB_Flüchtling** » 05.11.2006 19:01

Ahoi mgutt,

danke für Deine Ausführungen! Leider bin ich in Sachen php nicht so fit, als dass ich es selbst umsetzen könnte. Und den CH würde ich mir selbst dann nicht installieren, wenn ich es in meinem zugemoddeten Board noch könnte. Aber die konsequente Umstellung auf die Volltextsuche wäre doch einmal eine schöne Idee für einen eigenständigen Mod, oder?

mgutt hat geschrieben:
Ein Standard-phpBB ist optimal für Google ausgestattet. Es gibt keinerlei Nachteile. (Dennis Böge)
guter Witz

Habe ich auch so empfunden. Deshalb war, als ich das gelesen hatte, klar für mich, eine neue Signatur gefunden zu haben.

Schönen Abend noch!
Wolfgang