SQL mySQL count() und GROUP BY für ein Feld mit zwei Werten

[Shadow1701]

Wiedereinmal arbeite ich an meiner Filme und Serien Datenbank, diesmal geht es um die Suche.
Ich hoffe ich bekomme das hier verständlich beschrieben.

Die Suche funktioniert über einen Suchindex mit diesen Tabellen:

--
-- Tabellenstruktur für Tabelle searchindex
--

CREATE TABLE searchindex (
wordid int(11) NOT NULL,
word char(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle searchmatch
--

CREATE TABLE searchmatch (
matchid int(11) NOT NULL,
id int(11) NOT NULL,
fileid int(11) NOT NULL,
istitle int(1) NOT NULL,
filekind int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Jetzt sollen ALLE Suchwörter (in diesem Beispiel sind es zwei: 'mission' und 'impossible') im Titel und in der Beschreibung zu finden sein. Es ist ein Titel (searchmatch.istitle=1) oder eine Beschreibung (searchmatch.istitle=0).

Ich bekomme die Suche nach Titel ODER nach Beschreibung hin. Die Suche nach Titel UND Beschreibung schaffe ich nicht, mein Hirn ist was den logischen Denkprozess angeht offenbar blockiert.

Das ist die Abfrage nach dem Titel, die auch einwandfrei funktioniert:

SELECT
              movie.mvid, movie.mvname_de, movie.mvname_or, movie.mvdescription, movie.mvimage, movie.mvyear, movie.mvcountry, movie.mvfileid, movie.mvruntime, movie.mvsortnr,movie.mvactortpl,
              genre.gname, genre.gid,
              category.cname,
              file.fileid, file.fsize, file.fpath, file.fname, file.fchid,
              drive.dpath,
              channel.chname, channel.chnr, channel.chimage,
              COUNT(*) FROM searchindex
              LEFT JOIN `searchmatch` ON (searchindex.wordid=searchmatch.matchid)
              LEFT JOIN `movie` ON (searchmatch.id=movie.mvid)
              LEFT JOIN `genre` ON (movie.mvgid=genre.gid)
              LEFT JOIN `category` ON (movie.mvcatid=category.cid)
              LEFT JOIN `file` ON (movie.mvfileid=file.fileid)
              LEFT JOIN `drive` ON (drive.did=file.fpathid)
              LEFT JOIN `channel` ON (file.fchid=channel.chid)
              WHERE word IN ('mission','impossible')
              AND searchmatch.filekind=1
              AND searchmatch.istitle IN (1)
              GROUP BY id HAVING COUNT(*) = 2 ORDER BY genre.gname, category.cname, movie.mvname_de, movie.mvsortnr;

Entscheidend ist hier die Zeile "AND searchmatch.istitle IN (1)"

Die Suche funktioniert einwandfrei nach
AND searchmatch.istitle IN (1)
oder nach
AND searchmatch.istitle IN (0)

Aber nicht nach AND searchmatch.istitle IN (0,1)

Kann mir bitte jemand auf die Sprünge helfen wie ich das GROUP BY unter Berücksichtigung von ZWEI Suchwörtern sowohl für searchmatch.istitle=0 AND searchmatch.istitle=1 hinbekomme?

(searchmatch.istitle=0 muss beide Suchwörter enthalten UND searchmatch.istitle=1 muss beide Suchwörter enthalten)

 

[floq0r]

GROUP BY id HAVING COUNT(*) = 2

sieht für mich merkwürdig aus, das bedeutet du suchst results für die es in searchmatch exakt 2 Einträge gibt?
Außerdem: Wenn du in searchindex folgenden Eintrag hast:

wordid	word
123	impossible

und in searchmatch:

matchid	id	fieldid	istitle	filekind
123	456	x	1	x

dann enthält searchindex nur redundate informationen und du kannst das word gleich in searchmatch speichern.

 

[Shadow1701]

darüber muss ich nachdenken. Möglicherweise macht es wirklich Sinn den Suchindex umzubauen.

die Suche funktioniert jedenfalls so wie ich es möchte. Die Lösung lautet sum().

 

[floq0r]

Die Lösung wofür lautet sum()?

 

[nutrix]

darüber muss ich nachdenken. Möglicherweise macht es wirklich Sinn den Suchindex umzubauen.

Immer. Ich hatte das erst letztes hier, wo die Entwickler genau das nicht machten, und wunderten, warum Abfragen über 10 Sekunden brauchten, und mit dem mal schnell als DB Admin erstellten Index dauerte es nur noch 0,5 Sekunden.

 

[Shadow1701]

was bedeutet "DB Admin erstellten Index"? Würde mich interessieren.

Meine Abfragen dauern auch nicht länger als 0,5 Sekunden, nicht einmal so lange, meine Filmdatenbank ist aber auch nicht wirklich groß.

 

[nutrix]

Wenn Du Admin auf einem System wie einer Datenbank bist, kannst Du ganz schnell mal Indexe erstellen. Das sieht bei Dir oben ja nach MySQL/MariaDB aus:
https://dev.mysql.com/doc/refman/8.4/en/create-index.html

 

[floq0r]

@Shadow1701 Stell dir ein unsortiertes Telefonbuch vor. Wenn da 100 Einträge drin sind kann man schon mal leicht durchschauen, bei 1Mio. siehts anders aus. Wird das Telefonbuch nach Namen sortiert wäre das das Äquivalent zu einem clustered index. Nachdem du das Alphabet kennst du man davon ausgehen kann, dass die Mengenverteilung aller Buchstaben ungefähr gleichmäßig ist wirst du bei der Suche nach einem Herrn Schneider nicht in der ersten sondern in der zweiten Hälfte suchen und nach ein paar Schritten deinen Schneider gefunden haben. Der Datenbankserver tut nichts anderes, berücksichtigt dabei unter anderem aber auch noch ein Histogramm in dem die statistische Verteilung der Daten hinterlegt ist.
Zusätzlich kannst du weitere Indizes erstellen, beispielsweise für die Vornamen oder Postleitzahlen der Personen.

 

[Enurian]

Es schadet nicht, sich mal über so Probleme selbst Gedanken zu machen und das nachzubauen, aber eine Indizierung von Text ist natürlich kein so einzigartiges Problem, dass da noch niemand drüber nachgedacht hat. Datenbanksysteme bieten meist eine integrierte Indizierung an, hier ist wohl das für mysql: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html