MATCH .... AGAINST ... Problem

Denise · 21.03.2007, 07:23

Hallo an alle!

Habe gestern fast 2 Stunden alles mögliche an Foren und auch die MySQL Referenz studiert und finde keine Lösung zu meinem Problem. Hab zwar auch hier einig Threads zum Thema Volltextsuche gefunden, aber keinen der mein Problem behandelt oder löst.

Zu meinem Problem:
Ich haben mit PHP eine Suche erstellt, mit der man in einer MySQL-Tabelle nach Musiktiteln suchen kann. Die Suche soll so funktionieren, dass bei Eingabe von mehreren durch Leerzeichen getrennten Suchbegriffen nur Titel gefunden werden in denen "jeder" der Suchbegriffe unbedingt enthalten sein muss.

Hier der dafür verwendete SQL-Query würde bei 2 eingegebenen Suchbegriffen so aussehen:

Code:

SELECT id, name, size, section, added FROM `music` WHERE MATCH name AGAINST ('+begriff1 +begriff2' IN BOOLEAN MODE)

Diese Abfrage liefert mir zwar Ergebnisse in denen die Suchbegriffe vorhanden sind, aber nicht so wie ich es mir eigentlich vorgestellt habe. Es werden auch Datensätze ausgegeben in denen sich nur einer der beiden Suchbegriffe befindet. Ich möchte aber das beide Suchbegriffe zwingend im Feld "name" vorhanden sein müssen.

1. Frage: Was mach ich falsch, bzw. wie kann ich das lösen?
2. Frage: Wie ist die selbe Aufgabenstellung lösbar wenn man die selbe Suchfunktion auch noch per MATCH ... AGAINST ... mit Wildcards machen möchte? (so das "begriff1" und "begriff2" auch als Wortteile im Feld "name" gefunden werden?

- Index für das Feld "name" ist ein Volltext Index
- derzeit ca. 145.000 Datensätze in besagter Tabelle
- MySQL Version 4.0.27
- PHP 4.4.6 auf Apache Server

Tabellenstruktur

Code:

CREATE TABLE `music` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `hash` varchar(40) NOT NULL default '',
  `name` varchar(100) NOT NULL default '',
  `size` bigint(20) unsigned NOT NULL default '0',
  `quality` varchar(50) NOT NULL default '',
  `language` varchar(50) NOT NULL default '',
  `section` tinyint(3) unsigned NOT NULL default '0',
  `user` mediumint(8) unsigned NOT NULL default '0',
  `added` int(10) unsigned NOT NULL default '0',
  `updated` int(10) unsigned NOT NULL default '0',
  `alive` int(10) unsigned NOT NULL default '0',
  `ul` smallint(5) unsigned NOT NULL default '0',
  `dl` smallint(5) unsigned NOT NULL default '0',
  `status` tinyint(3) unsigned NOT NULL default '0',
  `info` text NOT NULL,
  `link` varchar(255) NOT NULL default '',
  `views` int(8) default '0',
  `points` int(10) unsigned NOT NULL default '0',
  `votes` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
  UNIQUE KEY `hash` (`hash`),
  KEY `added` (`added`),
  KEY `section` (`section`),
  FULLTEXT KEY `name` (`name`)
) TYPE=MyISAM COMMENT='Music Title' AUTO_INCREMENT=144652 ;

Mein erster Lösungsansatz war die Abfrage mit "LIKE" zu versuchen. Da auf besagter Seite jedoch permanent so zwischen 300-500 Besucher gleichzeitig Online sind, zieht eine LIKE-Abfrage den Server total nieder. CPU-Belastung sogar bis 90% wobei bei MATCH Höchstwerte von 20% meßbar sind.

Da ich momentan schon nicht mehr weiß wo ich mit meiner Lösungssuche fortsetzen soll. bin ich über alle hilfreichen Antworten dankbar.

Grüße Denise

dr.e. · 21.03.2007, 08:37

Hallo Denise,

ich tippe darauf, dass das MATCH an dieser Stelle zweimal in der Abfrage eingesetzt werden und mit AND verknüpft werden muss. Nur so kannst du eindeutige Ergebnisse für beide Begriffe erzielen. Danach noch mit DISTINCT oder GROUP BY die doppelten Ergebnisse rausschmeißen.

Theoretisch sollte somit ein

Zitat:

SELECT DISTINCT id, name, size, section, added FROM music WHERE MATCH name AGAINST ('+begriff1' IN BOOLEAN MODE) AND MATCH name AGAINST ('+begriff2' IN BOOLEAN MODE)

funktionieren. Kann nur sein, dass die MySQL 4.2.x da nicht mitmacht...

Denise · 21.03.2007, 10:37

Hallo dr.e.,

Danke für die Antwort. Diese Lösung hatte ich gestern schon versucht. Da ich aber nicht mehr wußte welches Ergebnis die lieferte, habe ich es jetzt nochmal laut Deinen Vorschlag gemacht.

Leider liefert die Abfrage so überhaupt kein Ergebnis wenn 2 Suchbegriffe eingegeben werden. Solange nur nach einen Begriff gesucht wird, klappt es aber sobald ein 2. Begriff gleichzeitig gesucht wird (dann ist ja auch MATCH 2x in der Abfrage) liefert mir die Abfrage keine Ergebnisse mehr.

Aber auch keine Fehlermeldung!

Ist ist zum aus der Haut fahren

dr.e. · 21.03.2007, 12:52

Hallo Denise,

ich weiß nicht wie sich das Performance-technisch auswirkt, aber könntest du nicht per INSTR() das Ergebnis nochmals eingrenzen? Andere Ideen habe ich aktuell nicht. Evtl. könnte man etas durch eine Design-Änderung erreichen. Hierzu bräuchte ich aber ein paar Details zu deinem Tabellen-Layout.

Denise · 21.03.2007, 20:05

@dr.e.

Danke das Problem hat sich jetzt zwar nicht von selbst, aber dennoch gelöst.

Hier die Ursache für meine Probleme:
MATCH ... AGAINST ... wie oben beschrieben hat eigentlich einwandfrei funktioniert. Der Grund warum bei mir teilweise seltsame bis überhaupt keine Abfrageergebnisse entstanden sind war in den SQL-Systemvariablen zu finden:

Code:

[mysqld]
ft_min_word_len=x (x = minimale Wortlänge für z.b. Match)

Dieser Wert war auf meinem Server zu hoch (5) eingestellt, was zur Folge hatte, dass MATCH Wörter in der Datenbanktabelle erst ab einer Zeichenlänge von 5 Zeichen als Wort erkannt hatte und somit nicht viel finden konnte wenn jemand z.b. als suchbegriff "Los Rios" eingegeben hat (los < 5 Zeichen und Rios < 5 Zeichen).

Tipp: Der Wert für ft_min_word_len sollte aber auch nicht sinnlos "zu klein" gewählt werden, da diese Begrenzung ja nicht ohne Grund geschaffen wurde: z.B. bei 1 würde ja schon jeder Buchstabe oder Ziffer als Wort gelten was dann auch zur Folge hätte das Abfragen viel Schrott und vor allem bei großen Tabellen endlos unnötige Datensätze auswerfen würden. Dies ist dann auch wieder mit einer stärkeren Serverbelastung verbunden.

Vielleicht hilft das ja mal jemanden mit einem ähnlichen Problem.

Danke nochmals!

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] Galerie Problem...	coraplanet	PHP Tipps 2008	4	06.06.2008 13:42
Einzeltreffer von MATCH ... AGAINST ... ermitteln	proud	Datenbanken	3	09.05.2008 08:31
Suche (MATCH) mit MYSQL	ruferp	Datenbanken	1	10.09.2007 22:06
match, against: ergebnisse filtern	DarkThunder	Datenbanken	4	30.03.2007 10:51
Problem mit den Fulltext Keys	R4v3r	Datenbanken	2	16.02.2007 12:41
Volltext-Suche Problem - 2 verschiedene Tabellen		Datenbanken	1	18.09.2006 16:32
datensätze defekt oder problem mit dem einlesen?	Ministry	Datenbanken	4	06.07.2006 18:42
problem!!!		PHP Tipps 2006	6	08.02.2006 11:06
[Erledigt] wieder ein Problem bei phpmailer und smtp		PHP Tipps 2006	24	07.02.2006 01:07
Match ... Against... einzeln oder gesammten string?	EvilDragon	Datenbanken	2	06.02.2006 14:49
Volltextsuche mit MATCH AGAINST über mehere Tabellen?		Datenbanken	5	13.09.2005 15:02
match .. against	Mutatos	Datenbanken	1	29.03.2005 16:42
Array Problem [Ansatz ?]		PHP Tipps 2005	0	27.01.2005 18:24
Smarty und PHP-Skript Problem		PHP Tipps 2004-2	2	03.12.2004 22:27
Problem mit alter JavaScript-Funktion	woods	PHP Tipps 2004	1	13.08.2004 13:34

21.03.2007, 07:23
Denise Neuer Benutzer Registriert seit: 21.03.2007 Beiträge: 3	MATCH .... AGAINST ... Problem Hallo an alle! Habe gestern fast 2 Stunden alles mögliche an Foren und auch die MySQL Referenz studiert und finde keine Lösung zu meinem Problem. Hab zwar auch hier einig Threads zum Thema Volltextsuche gefunden, aber keinen der mein Problem behandelt oder löst. Zu meinem Problem: Ich haben mit PHP eine Suche erstellt, mit der man in einer MySQL-Tabelle nach Musiktiteln suchen kann. Die Suche soll so funktionieren, dass bei Eingabe von mehreren durch Leerzeichen getrennten Suchbegriffen nur Titel gefunden werden in denen "jeder" der Suchbegriffe unbedingt enthalten sein muss. Hier der dafür verwendete SQL-Query würde bei 2 eingegebenen Suchbegriffen so aussehen: Code: SELECT id, name, size, section, added FROM `music` WHERE MATCH name AGAINST ('+begriff1 +begriff2' IN BOOLEAN MODE) Diese Abfrage liefert mir zwar Ergebnisse in denen die Suchbegriffe vorhanden sind, aber nicht so wie ich es mir eigentlich vorgestellt habe. Es werden auch Datensätze ausgegeben in denen sich nur einer der beiden Suchbegriffe befindet. Ich möchte aber das beide Suchbegriffe zwingend im Feld "name" vorhanden sein müssen. 1. Frage: Was mach ich falsch, bzw. wie kann ich das lösen? 2. Frage: Wie ist die selbe Aufgabenstellung lösbar wenn man die selbe Suchfunktion auch noch per MATCH ... AGAINST ... mit Wildcards machen möchte? (so das "begriff1" und "begriff2" auch als Wortteile im Feld "name" gefunden werden? - Index für das Feld "name" ist ein Volltext Index - derzeit ca. 145.000 Datensätze in besagter Tabelle - MySQL Version 4.0.27 - PHP 4.4.6 auf Apache Server Tabellenstruktur Code: CREATE TABLE `music` ( `id` int(10) unsigned NOT NULL auto_increment, `hash` varchar(40) NOT NULL default '', `name` varchar(100) NOT NULL default '', `size` bigint(20) unsigned NOT NULL default '0', `quality` varchar(50) NOT NULL default '', `language` varchar(50) NOT NULL default '', `section` tinyint(3) unsigned NOT NULL default '0', `user` mediumint(8) unsigned NOT NULL default '0', `added` int(10) unsigned NOT NULL default '0', `updated` int(10) unsigned NOT NULL default '0', `alive` int(10) unsigned NOT NULL default '0', `ul` smallint(5) unsigned NOT NULL default '0', `dl` smallint(5) unsigned NOT NULL default '0', `status` tinyint(3) unsigned NOT NULL default '0', `info` text NOT NULL, `link` varchar(255) NOT NULL default '', `views` int(8) default '0', `points` int(10) unsigned NOT NULL default '0', `votes` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `hash` (`hash`), KEY `added` (`added`), KEY `section` (`section`), FULLTEXT KEY `name` (`name`) ) TYPE=MyISAM COMMENT='Music Title' AUTO_INCREMENT=144652 ; Mein erster Lösungsansatz war die Abfrage mit "LIKE" zu versuchen. Da auf besagter Seite jedoch permanent so zwischen 300-500 Besucher gleichzeitig Online sind, zieht eine LIKE-Abfrage den Server total nieder. CPU-Belastung sogar bis 90% wobei bei MATCH Höchstwerte von 20% meßbar sind. Da ich momentan schon nicht mehr weiß wo ich mit meiner Lösungssuche fortsetzen soll. bin ich über alle hilfreichen Antworten dankbar. Grüße Denise __________________ Es ist ja schon mal ein Anfang wenn man weiß das man nichts weiß.


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

21.03.2007, 10:37
Denise Neuer Benutzer Registriert seit: 21.03.2007 Beiträge: 3	Hallo dr.e., Danke für die Antwort. Diese Lösung hatte ich gestern schon versucht. Da ich aber nicht mehr wußte welches Ergebnis die lieferte, habe ich es jetzt nochmal laut Deinen Vorschlag gemacht. Leider liefert die Abfrage so überhaupt kein Ergebnis wenn 2 Suchbegriffe eingegeben werden. Solange nur nach einen Begriff gesucht wird, klappt es aber sobald ein 2. Begriff gleichzeitig gesucht wird (dann ist ja auch MATCH 2x in der Abfrage) liefert mir die Abfrage keine Ergebnisse mehr. Aber auch keine Fehlermeldung! Ist ist zum aus der Haut fahren __________________ Es ist ja schon mal ein Anfang wenn man weiß das man nichts weiß.

21.03.2007, 12:52
dr.e. Moderator und Wett-König Registriert seit: 21.05.2008 Beiträge: 3.633 PHP-Kenntnisse: Fortgeschritten	Hallo Denise, ich weiß nicht wie sich das Performance-technisch auswirkt, aber könntest du nicht per INSTR() das Ergebnis nochmals eingrenzen? Andere Ideen habe ich aktuell nicht. Evtl. könnte man etas durch eine Design-Änderung erreichen. Hierzu bräuchte ich aber ein paar Details zu deinem Tabellen-Layout. __________________ Viele Grüße, Dr.E. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1. Think about software design before you start to write code! 2. Discuss and review it together with experts! 3. Choose good tools (-> Adventure PHP Framework (APF))! 4. Write clean and reusable software only! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

21.03.2007, 20:05
Denise Neuer Benutzer Registriert seit: 21.03.2007 Beiträge: 3	@dr.e. Danke das Problem hat sich jetzt zwar nicht von selbst, aber dennoch gelöst. Hier die Ursache für meine Probleme: MATCH ... AGAINST ... wie oben beschrieben hat eigentlich einwandfrei funktioniert. Der Grund warum bei mir teilweise seltsame bis überhaupt keine Abfrageergebnisse entstanden sind war in den SQL-Systemvariablen zu finden: Code: [mysqld] ft_min_word_len=x (x = minimale Wortlänge für z.b. Match) Dieser Wert war auf meinem Server zu hoch (5) eingestellt, was zur Folge hatte, dass MATCH Wörter in der Datenbanktabelle erst ab einer Zeichenlänge von 5 Zeichen als Wort erkannt hatte und somit nicht viel finden konnte wenn jemand z.b. als suchbegriff "Los Rios" eingegeben hat (los < 5 Zeichen und Rios < 5 Zeichen). Tipp: Der Wert für ft_min_word_len sollte aber auch nicht sinnlos "zu klein" gewählt werden, da diese Begrenzung ja nicht ohne Grund geschaffen wurde: z.B. bei 1 würde ja schon jeder Buchstabe oder Ziffer als Wort gelten was dann auch zur Folge hätte das Abfragen viel Schrott und vor allem bei großen Tabellen endlos unnötige Datensätze auswerfen würden. Dies ist dann auch wieder mit einer stärkeren Serverbelastung verbunden. Vielleicht hilft das ja mal jemanden mit einem ähnlichen Problem. Danke nochmals! __________________ Es ist ja schon mal ein Anfang wenn man weiß das man nichts weiß.