Schema mit Vererbung von Assoziationen

Ich bin gerade dabei eine Anwendung zu basteln mit der sich Videos verwalten lassen, und als Anfänger hadere damit ein passendes Schema zu finden das all meinen Ansprüchen genüge trägt. In Ermangelung nativer Unterstützung anderer DBMS läuft das ganze übrigens mit SQLite, und ich bin mir nicht sicher ob diese Frage nicht eventuell in das SQLite Unterforum gehört.

Hier mal ein Paar Details. Um genau zu sein geht es um Filmreihen, Filme (können zu Filmreihen gehören, müssen es aber nicht), Szenen/Kapitel (gehören zu einem Film), Clips (können zu einem Film gehören), TV-Serien, Staffeln (gehrören zu einer TV-Show), und Episoden (gehören zu einer Staffel), all dies soll als individuelle Entitäten erfasst werden. Verwaltet wird all dies in Bibliotheken, wobei eine Bibliothek z.B. ausschließlich aus Clips bestehen kann, oder aus Episoden und Szenen, nur aus Filreihen, oder aus allem zusammen, etc.

Allen Entitäten sollen u.a. Tags zugewiesen werden können, wobei es hier wichtig wäre sich nicht zu wiederholen, sprich ich möchte nicht eine Filmreihe sowie die zugehörigen Filme mit den gleichen Tags versehen müssen, idealerweise würden die Filme die Tags der Filmreihe "erben", könnten aber mit zusätzlichen Tags versehen werden, das gleiche bei den Serien. Außerdem haben alle Entitäten auch Dinge wie Titel, Beschreibung, Bewertung, Vorschaubild (Cover), etc. Später sollen dann evtl noch Informationen zu den mitwirkenden Schauspielern etc hinzukommen, und dann sicher auch entsprechend suchbar sein.

Ich würde die Videos gerne sowohl gruppiert (zu Filmreihen und TV-Shows, evtl auch Staffeln) anzeigen, filtern (z.B. nach Tags, Wörtern im Titel oder der Beschreibung, etc), und ordnen (z.B. nach Bewertung oder Titel) lassen, und das alles über Seiten hinweg. Genau so sollte es möglich sein die Videos ohne Gruppierung darzustellen, als z.B. einfach alle Filme und Episoden einzeln, und in der schönsten aller Welten auch noch ein zwischending, Gruppen da wo sie vorhanden sind, gemischt mit einzelnen Videos falls sie keiner Gruppe angehören.

Mal ein Beispiel. Ich such nach "Bruce Wayne" und filtere nach den Tags "Anti-Hero" und "Comic Adaption". Nehmen wir an das würde auf folgendes zutreffen, die Filmreihe "Batman - The Dark Knight Trilogy", das wären die Filme "Batman Begings", "The Dark Knight", und "The Dark Knight Rises", außerdem träfe es auf die TV-Serie "Gotham" und ein Paar ihrer Episoden zu. Das heißt ich hätte hier fünf verschiedene Typen von Treffern, Filmreihe, Film, Serie, Staffel, und Episode.

Nach vielem hin und her bin ich bei einem Schema hängen geblieben in dem sämtliche Typen in einer einzigen Tabelle gespeichert sind, und das sieht ungefähr so aus



Was ich vorhabe funktioniert damit auch, aber schön ist es irgendwie nicht. Eine Abfrage für eine Suche wie für das erwähnte Batman Beispiel mit den Tags ist irgendwie arg ineffizient, also zumindest dann wenn es darum geht z.B. Filmreihen und Filme zu finden, wobei die jenigen Filme ausgenommen werden sollen die sich in einer Filmreihe befinden, und wenn dann noch Episoden und TV-Serien dazu kommen, und Tags vererbt werden sollen, oh je...

Nehmen wir mal folgende Datensätze an:

Code:

libraries

+----+------------+
| id |   title    |
+----+------------+
|  1 | My Library |
+----+------------+

types

+----+--------------+
| id |     title    |
+----+--------------+
|  1 | Movie series |
|  2 | Movie        |
|  3 | Scene        |
|  4 | Clip         |
|  5 | TV-Show      |
|  6 | Season       |
|  7 | Episode      |
+----+--------------+

items

+----+-----------+------------+---------+----------------------------------+----------------------------------------+
| id | parent_id | library_id | type_id |              title               |              description               |
+----+-----------+------------+---------+----------------------------------+----------------------------------------+
|  1 | (null)    | 1          |       1 | Batman - The Dark Knight Trilogy | lorem ipsum dolor sit Bruce Wayne amet |
|  2 | 1         | (null)     |       2 | Batman Begins                    | lorem ipsum dolor sit Bruce Wayne amet |
|  3 | 1         | (null)     |       2 | The Dark Knight                  | lorem ipsum dolor sit Bruce Wayne amet |
|  4 | 1         | (null)     |       2 | The Dark Knight Rises            | lorem ipsum dolor sit Bruce Wayne amet |
|  5 | (null)    | 1          |       4 | Batman Clip                      | lorem ipsum dolor sit Bruce Wayne amet |
|  6 | (null)    | 1          |       5 | Gotham - TV Show                 | lorem ipsum dolor sit Bruce Wayne amet |
|  7 | 6         | (null)     |       6 | Gotham - Season 01               | lorem ipsum dolor sit Bruce Wayne amet |
|  8 | 7         | (null)     |       7 | Gotham - Season 01 - Episode 01  | lorem ipsum dolor sit Bruce Wayne amet |
+----+-----------+------------+---------+----------------------------------+----------------------------------------+

tags

+----+----------------+
| id |     title      |
+----+----------------+
|  1 | Anti-Hero      |
|  2 | Comic Adaption |
+----+----------------+

tags_items

+----+--------+---------+
| id | tag_id | item_id |
+----+--------+---------+
|  1 |      1 |       1 |
|  2 |      2 |       1 |
|  3 |      1 |       5 |
|  4 |      2 |       5 |
|  5 |      1 |       6 |
|  6 |      2 |       6 |
+----+--------+---------+

Ich sehe mich da irgendwie eher ineffiziente Abfragen benutzen, z.B.:

Code:

SELECT
	items.id, items.title
FROM
	items
LEFT JOIN
	items parent_items ON parent_items.id = items.parent_id
LEFT JOIN
	items parent_parent_items ON parent_parent_items.id = parent_items.parent_id
INNER JOIN
    libraries 
		ON (
			libraries.id = items.library_id
			OR
			libraries.id = parent_items.library_id
			OR
			libraries.id = parent_parent_items.library_id
		)
		AND libraries.id = 1
INNER JOIN
	tags_items
		ON (
			tags_items.item_id = items.id
			OR
			tags_items.item_id = parent_items.id
			OR
			tags_items.item_id = parent_parent_items.id
		)
		AND tags_items.tag_id IN (1, 2)
WHERE
	items.type_id IN (1, 2, 5, 6, 7)
	AND
	(items.title LIKE '%Bruce Wayne%' OR items.description LIKE '%Bruce Wayne%')
GROUP BY
	items.id
HAVING
	COUNT(DISTINCT tags_items.tag_id) = 2;

Damit bekomme ich das was ich erwarte

Code:

+----+----------------------------------+
| id |              title               |
+----+----------------------------------+
|  1 | Batman - The Dark Knight Trilogy |
|  2 | Batman Begins                    |
|  3 | The Dark Knight                  |
|  4 | The Dark Knight Rises            |
|  6 | Gotham - TV Show                 |
|  7 | Gotham - Season 01               |
|  8 | Gotham - Season 01 - Episode 01  |
+----+----------------------------------+

Szenen/Kapitel und Clips mit einzubeziehen würde auch funktionieren, genau so wie Sortieren und Limitieren, das Einbeziehen von weiteren Tags die z.B. nur Filmen zugeordnet sind, oder das Ausschließen von Videos die einer Gruppe angehören.

Performant, ist aber anders. Das LIKE hilft hier ungemein, so das ich bei Größenordnungeb von 300,000 Einträgen so bei 0.1 - 0.2 Sekunden heraus komme, doch ohne eine solche weitere Einschränkung (wie es z.B. der Fall wäre wenn man nur nach Tags filtert) geht die Performance aufgrund des einbeziehens der Elterntabellen böse in den Keller, dann sind nämlich 3 Sekunden und aufwärts angesagt, und das gefällt mir irgendwie so gar nicht.

Stellt sich mir die Frage ob, und wenn wo ich hier auf dem falschen Dampfer bin. Ist das ganze bereits durch das Schema zum Scheitern veruteilt?

Gruß, Timo

 

Was genau suchst Du denn für Fähigkeiten?

 

Ohne dir jetzt ein fertiges Modell hinzulegen, vllt. mal ein paar Ansätze die ich verfolgen würde:
Jedes Video/jeder Film wird einer logischen Einheit zugeordnet. Dieser logischen Einheit gibst du die ersten Tags. (Dabei unterscheidest du dann auch zwischen Serie/Film/Trilogie/etc.) Und dann gibst du den untergeordneten Filmen (also die "echten" Daten, für die es auch wirklich Videomaterial gibt) auch nocheinmal Tags...
Hier im Forum hat ich schonmal ein etwas ausführlicheres Modell angerissen, das den Grundansatz beschreiben sollte...

 

Keine speziellen, ich wollte es nur erwähnt haben, für den Fall das die Einschränkungen von SQLite bei meinem Vorhaben ein Problem darstellen sollten.

Wobei, wo du es ansprichst, eien vernünftige Volltext Suche wäre schon nett, die FTS Geschichte von SQLite ist ja gruselig, und sorgt zumindest in dem jetzigen Schema für noch viel dramatischere Performanceeinbußen sobald kein matching mehr im Spiel ist (welches ich zumindest bisher auch nur über eine einzige Spalte zum laufen bekommen habe, sobald die ganze Tabelle verwendet wird, gibt's "ambigous column" Fehler). Die obige Abfrage habe ich nach 5 Minuten Wartezeit abgebrochen

 

Danke für den Hinweis, ich werde das nachher mal antesten. Auf den ersten Blick habe ich allerdings das Gefühl das ich auch da wieder ähnliche, möglicherweise unperformante joins über mehrere Tabellen verwenden muss, oder wolltest du damit sagen das ich die Tags doppelt, bzw. im Fall von z.B. Serien > Staffeln > Episoden, dreifach vergeben soll?

 

Das ist dann aber ein Hinweis auf Fehler im SQL. Ansonsten kann ich Dir einen Blick auf PostgreSQL empfehlen. Das kann z.B. auch so Dinge wie vererbte Tabellen, und auch eine gescheite FTS.

 

1. Du wolltest ja Tag für Serien/Filmreihen vergeben und zusätzlich den einzelnen Filmen neue Tags geben können.
2. Joins sind nur unperformant, wenn man Sie unperformant macht...
Lektüre gibts genug auf Google: Indizes oder generell Indizierung

 

Wollte ich auch nicht bestreiten, war nur eine Randbemerkung Ist mit items.items MATCH ... statt nur items inzwischen auch gelößt, bringt aber eh nicht sonderlich viel, 0,2 Sekunden schneller als die LIKE Variante, das ist eher enttäauschend.

Andere DBMS zu verwenden ist wie gesagt problematisch, da keine Schnittstellen dafür vorhanden sind, das ganze läuft in Adobe AIR, und da gibt's halt nativ nur SQLite.

 

Richtig, aber ist das nun eine Antwort, eine Feststellung, oder eine Frage?

Nunja, das ist vielleicht ein bischen arg verallgemeinert. Klar, wenn man joins nicht richtig verwendet, dann können sie ineffizient sein. Meine Befürchtung ging hier aber viel mehr in die Richtung das sich aufgrund des Schemas schlicht kein effizienter join verwirklichen läßt der diese Vererbung realisiert, unabhängig von fehlenden/falsch gesetzten Indizes. Aber das war nur eine Vermutung bei kurzem überfliegen, ich werd's natürlich erstmal ausprobieren.

Bitte immer bedenken das ich was SQL angeht noch nicht ganz so versiert bin, also bitte nicht alles auf die Goldwaage legen was ich so von mir gebe

 

1. Das lässt sich mit dem Grundmodell in dem anderen Post realisieren. Das wollte ich damit sagen...
2. Du holst dir alle Tags für die logische Einheit Serie und joinst alle Tags der einzelnen Folgen dazu (die es noch nicht gibt)... Die Realisierung ist so einfach wie auch simpel... Man muss nur richtige Indizes legen, damit das ganze auch performant ist.

 

Ich kann mir vorstellen dass das eigentlich sehr einfach ist, für mich ist das allerdings recht abstrakt, ich kann mir noch so gar keinen Reim darauf machen wie genau die Vererbung da realisiert werden kann.

Wenn ich z.B. nach Episoden suche, und die Tags der zugehörigen Staffel sowie die der Serie mit einbeziehen möchte, dann benötige ich doch auch wieder Referenzen der Eltern um an deren Tags zu gelangen, was wieder auf dieses Joinen von Selbst-Referenzierungen hinaus läuft, wenn auch wohl nur eine (Staffel > Serie) statt zwei (Episode > Staffel > Serie).

Ich werde mich damit wohl erstmal ein Paar Stunden beschäftigen müssen bis es dann hoffentlich mal klick macht.

 

Ich habe hier mal ein kleines Beispiel vorbereitet, dass (wenn ich nichts falsch gemacht habe) das ganze mal anhand deiner Serie darstellt.
Also: Episode -> Staffel -> Serie

Spoiler: Code

Code:

Create Table tags_tab
(
tag_id Varchar2(12)
,bezeichnung Varchar2(12)
,kurzbeschreibung Varchar2(2000)
);

Create Table tag_reference_tab
(
reference_id Varchar2(12)
,tag_id Varchar2(12)

,Constraint tag_reference_tab_fk01 Foreign Key (tag_id) References tags_tab (tag_id)

,Constraint tag_reference_tab_pk Primary Key (reference_id, tag_id)
);

Create Table serien_tab
(
serien_id Varchar2(12)
,bezeichnung Varchar2(200)
,kurzbeschreibung Varchar2(4000)
,reference_id Varchar2(12)

,Constraint serien_tab_fk01 Foreign Key (reference_id) References tag_reference_tab (reference_id)

,Constraint serien_tab_pk Primary Key (serien_id)
);

Create Table staffeln_tab
(
staffel_id Varchar2(12)
,bezeichnung Varchar2(200)
,kurzbeschreibung Varchar2(4000)
,reference_id Varchar2(12)
,serien_id Varchar2(12)

,Constraint staffeln_tab_fk01 Foreign Key (reference_id) References tag_reference_tab (reference_id)
,Constraint staffeln_tab_fk02 Foreign Key (serien_id) References serien_tab (serien_id)

,Constraint staffeln_tab_pk Primary Key (staffel_id)
);

Create Table episoden_tab
(
episoden_id Varchar2(12)
,bezeichnung Varchar2(200)
,kurzbeschreibung Varchar2(4000)
,reference_id Varchar2(12)
,staffel_id Varchar2(12)

,Constraint episoden_tab_fk01 Foreign Key (reference_id) References tag_reference_tab (reference_id)
,Constraint episoden_tab_fk02 Foreign Key (staffel_id) References staffeln_tab (staffel_id)

,Constraint episoden_tab_pk Primary Key (episoden_id)
);

 

Danke erstmal für deine Mühe

Ich habe das gerade mal ausprobiert, es scheint mir aber langsamer zu sein als mein erster Ansatz. Und wenn ich auch noch z.B. Filme mit einbeziehen wollte, dann müßte UNION her, oder?

Der Einfachheit halber habe ich es erstmal mit MySQL probiert, da das Programm welches mir die Dummydaten generiert SQLite nicht unterstützt.

Code:

--
-- Tabellenstruktur für Tabelle `episodes`
--

CREATE TABLE IF NOT EXISTS `episodes` (
`id` int(10) unsigned NOT NULL,
  `association_id` int(10) unsigned NOT NULL,
  `season_id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=80005 ;

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

--
-- Tabellenstruktur für Tabelle `seasons`
--

CREATE TABLE IF NOT EXISTS `seasons` (
`id` int(10) unsigned NOT NULL,
  `association_id` int(10) unsigned NOT NULL,
  `tv_show_id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4003 ;

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

--
-- Tabellenstruktur für Tabelle `tags`
--

CREATE TABLE IF NOT EXISTS `tags` (
`id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10006 ;

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

--
-- Tabellenstruktur für Tabelle `tag_junctions`
--

CREATE TABLE IF NOT EXISTS `tag_junctions` (
  `association_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Tabellenstruktur für Tabelle `tv_shows`
--

CREATE TABLE IF NOT EXISTS `tv_shows` (
`id` int(10) unsigned NOT NULL,
  `association_id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1003 ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `episodes`
--
ALTER TABLE `episodes`
ADD PRIMARY KEY (`id`), ADD KEY `association_id` (`association_id`), ADD KEY `season_id` (`season_id`), ADD FULLTEXT KEY `title` (`title`), ADD FULLTEXT KEY `description` (`description`);

--
-- Indexes for table `seasons`
--
ALTER TABLE `seasons`
ADD PRIMARY KEY (`id`), ADD KEY `association_id` (`association_id`), ADD KEY `tv_show_id` (`tv_show_id`), ADD FULLTEXT KEY `title` (`title`), ADD FULLTEXT KEY `description` (`description`);

--
-- Indexes for table `tags`
--
ALTER TABLE `tags`
ADD PRIMARY KEY (`id`), ADD FULLTEXT KEY `title` (`title`);

--
-- Indexes for table `tag_junctions`
--
ALTER TABLE `tag_junctions`
ADD PRIMARY KEY (`association_id`,`tag_id`);

--
-- Indexes for table `tv_shows`
--
ALTER TABLE `tv_shows`
ADD PRIMARY KEY (`id`), ADD KEY `association_id` (`association_id`), ADD FULLTEXT KEY `title` (`title`), ADD FULLTEXT KEY `description` (`description`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `episodes`
--
ALTER TABLE `episodes`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=80005;
--
-- AUTO_INCREMENT for table `seasons`
--
ALTER TABLE `seasons`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4003;
--
-- AUTO_INCREMENT for table `tags`
--
ALTER TABLE `tags`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10006;
--
-- AUTO_INCREMENT for table `tv_shows`
--
ALTER TABLE `tv_shows`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1003;

Gefüllt mit 100 Shows, zu je 4 Staffeln mit jeweils 20 Episoden, wobei jeder Entität per Zufall 3 Tags zugewiesen werden (alle Tabellen bleiben unter 100,000 Einträgen), komme ich mit folgender Abfrage

Code:

SELECT
    episodes.id, episodes.title
FROM
    episodes
INNER JOIN
    seasons ON seasons.id = episodes.season_id
INNER JOIN
    tv_shows ON tv_shows.id = seasons.tv_show_id
INNER JOIN
    tag_junctions
        ON (
            tag_junctions.association_id = episodes.association_id
            OR
            tag_junctions.association_id = seasons.association_id
            OR
            tag_junctions.association_id = tv_shows.association_id
        )
        AND tag_junctions.tag_id IN (1, 2)
GROUP BY
    episodes.id
HAVING
    COUNT(DISTINCT tag_junctions.tag_id) = 2;

schon auf ungefähr 1.4 Sekunden. Gibt es hier noch Optimierungspotential? Ist die Art wie ich die Tags einbeziehe vielleicht der falsche Ansatz?

 

mal mit Deinen Tabellen (ohne Indexe außer die implizieten durch die PK's) und einigen tausend Testdaten Testdaten probiert (warum hast Du keine FK's?), hier das explain analyse:

Code:

test=*# explain analyse SELECT   
  episodes.id, episodes.title   
FROM   
  episodes   
INNER JOIN   
  seasons ON seasons.id = episodes.season_id   
INNER JOIN   
  tv_shows ON tv_shows.id = seasons.tv_show_id   
INNER JOIN   
  tag_junctions   
  ON (
  tag_junctions.association_id = episodes.association_id
  OR
  tag_junctions.association_id = seasons.association_id
  OR
  tag_junctions.association_id = tv_shows.association_id
  )
  AND tag_junctions.tag_id IN (1, 2)
GROUP BY
  episodes.id
HAVING
  COUNT(DISTINCT tag_junctions.tag_id) = 2;
  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=645.23..647.21 rows=99 width=24) (actual time=21.947..21.947 rows=0 loops=1)
  Group Key: episodes.id
  Filter: (count(DISTINCT tag_junctions.tag_id) = 2)
  Rows Removed by Filter: 143
  ->  Sort  (cost=645.23..645.48 rows=99 width=24) (actual time=21.546..21.585 rows=143 loops=1)
  Sort Key: episodes.id
  Sort Method: quicksort  Memory: 27kB
  ->  Merge Join  (cost=148.08..641.95 rows=99 width=24) (actual time=2.021..21.467 rows=143 loops=1)
  Merge Cond: (seasons.id = episodes.season_id)
  Join Filter: ((tag_junctions.association_id = episodes.association_id) OR (tag_junctions.association_id = seasons.association_id) OR (tag_j
  Rows Removed by Join Filter: 19857
  ->  Nested Loop  (cost=0.42..837.80 rows=16000 width=20) (actual time=0.056..2.910 rows=201 loops=1)
  ->  Nested Loop  (cost=0.42..355.28 rows=1600 width=12) (actual time=0.019..0.126 rows=21 loops=1)
  ->  Index Scan using seasons_pkey on seasons  (cost=0.28..67.28 rows=1600 width=12) (actual time=0.013..0.024 rows=21 loops=1)
  ->  Index Scan using tv_shows_pkey on tv_shows  (cost=0.14..0.17 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=21)
  Index Cond: (id = seasons.tv_show_id)
  ->  Materialize  (cost=0.00..282.55 rows=10 width=8) (actual time=0.002..0.126 rows=10 loops=21)
  ->  Seq Scan on tag_junctions  (cost=0.00..282.50 rows=10 width=8) (actual time=0.033..2.571 rows=10 loops=1)
  Filter: (tag_id = ANY ('{1,2}'::integer[]))
  Rows Removed by Filter: 12990
  ->  Sort  (cost=147.66..152.66 rows=2000 width=28) (actual time=1.822..6.754 rows=19991 loops=1)
  Sort Key: episodes.season_id
  Sort Method: quicksort  Memory: 173kB
  ->  Seq Scan on episodes  (cost=0.00..38.00 rows=2000 width=28) (actual time=0.005..0.884 rows=2000 loops=1)
 Planning time: 0.826 ms
 Execution time: 22.022 ms
(26 rows)

Hab dann noch einen Index gesetzt, um den einen Seq Scan zu eliminieren:

Code:

test=*# create index idx_tag_id on tag_junctions(tag_id);
CREATE INDEX   
Time: 31,343 ms   
test=*# explain analyse SELECT
  episodes.id, episodes.title
FROM   
  episodes   
INNER JOIN   
  seasons ON seasons.id = episodes.season_id
INNER JOIN   
  tv_shows ON tv_shows.id = seasons.tv_show_id
INNER JOIN   
  tag_junctions   
  ON (   
  tag_junctions.association_id = episodes.association_id
  OR   
  tag_junctions.association_id = seasons.association_id
  OR
  tag_junctions.association_id = tv_shows.association_id
  )
  AND tag_junctions.tag_id IN (1, 2)
GROUP BY
  episodes.id
HAVING
  COUNT(DISTINCT tag_junctions.tag_id) = 2;
  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=574.42..576.40 rows=99 width=24) (actual time=13.646..13.646 rows=0 loops=1)
  Group Key: episodes.id
  Filter: (count(DISTINCT tag_junctions.tag_id) = 2)
  Rows Removed by Filter: 143
  ->  Sort  (cost=574.42..574.67 rows=99 width=24) (actual time=13.269..13.313 rows=143 loops=1)
  Sort Key: episodes.id
  Sort Method: quicksort  Memory: 27kB
  ->  Hash Join  (cost=123.14..571.14 rows=99 width=24) (actual time=2.030..13.205 rows=143 loops=1)
  Hash Cond: (seasons.tv_show_id = tv_shows.id)
  Join Filter: ((tag_junctions.association_id = episodes.association_id) OR (tag_junctions.association_id = seasons.association_id) OR (tag_j
  Rows Removed by Join Filter: 19857
  ->  Hash Join  (cost=56.00..124.00 rows=2000 width=32) (actual time=0.424..2.963 rows=2000 loops=1)
  Hash Cond: (episodes.season_id = seasons.id)
  ->  Seq Scan on episodes  (cost=0.00..38.00 rows=2000 width=28) (actual time=0.005..0.680 rows=2000 loops=1)
  ->  Hash  (cost=36.00..36.00 rows=1600 width=12) (actual time=0.411..0.411 rows=400 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 13kB
  ->  Seq Scan on seasons  (cost=0.00..36.00 rows=1600 width=12) (actual time=0.008..0.236 rows=400 loops=1)
  ->  Hash  (cost=54.64..54.64 rows=1000 width=16) (actual time=1.492..1.492 rows=1000 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 36kB
  ->  Nested Loop  (cost=8.65..54.64 rows=1000 width=16) (actual time=0.058..1.035 rows=1000 loops=1)
  ->  Seq Scan on tv_shows  (cost=0.00..2.00 rows=100 width=8) (actual time=0.005..0.032 rows=100 loops=1)
  ->  Materialize  (cost=8.65..40.16 rows=10 width=8) (actual time=0.001..0.004 rows=10 loops=100)
  ->  Bitmap Heap Scan on tag_junctions  (cost=8.65..40.11 rows=10 width=8) (actual time=0.047..0.060 rows=10 loops=1)
  Recheck Cond: (tag_id = ANY ('{1,2}'::integer[]))
  Heap Blocks: exact=7
  ->  Bitmap Index Scan on idx_tag_id  (cost=0.00..8.64 rows=10 width=0) (actual time=0.042..0.042 rows=10 loops=1)
  Index Cond: (tag_id = ANY ('{1,2}'::integer[]))
 Planning time: 0.863 ms
 Execution time: 13.734 ms
(29 rows)

13 Millisekunden erscheinen mir akzeptabel.

 

Weil es erstmal nur um's Testen ging, und FK-Constraints nicht zur Performance beitragen, sondern nur der Integrität dienlich sind?

Die seperaten Indices in der Verknüpfungstabelle habe ich glatt vergessen, ich hätte besser erstmal ausschlafen sollen Mit denen bin auch erstmal im Millsekunden Bereich, mit etwas mehr Daten wird's aber auch wieder schnell langsamer, und noch viel langsamer wird's wenn Tags für Staffeln und Episoden mit einbezogen werden, dann sind wir wieder im Sekunden Bereich.

Code:

SELECT
    episodes.id, episodes.title
FROM
    episodes
INNER JOIN
    seasons ON seasons.id = episodes.season_id
INNER JOIN
    tv_shows ON tv_shows.id = seasons.tv_show_id
INNER JOIN
    tag_junctions
        ON (
            tag_junctions.association_id = episodes.association_id
            OR
            tag_junctions.association_id = seasons.association_id
            OR
            tag_junctions.association_id = tv_shows.association_id
        )
        AND tag_junctions.tag_id IN (1, 2, 3)
GROUP BY
    episodes.id
HAVING
    COUNT(DISTINCT tag_junctions.tag_id) = 3;

Die TV-Show hat hierbei die Tags 1 und 2, und die Staffel den Tag 3, bei folgender Anzahl Datensätze

Code:

+---------------+--------+
| episodes      | 80,004 |
| seasons       | 4,002  |
| tags          | 10,005 |
| tag_junctions | 38,036 |
| tv_shows      | 1,002  |
+---------------+--------+

komme ich schon bei 4 Sekunden raus, und mit steigender Datenmenge wird es linear lamgsamer, sprich doppelte Menge, doppelte Zeit. SQLite ist da mit 0.7 Sekunden interessanter Weise deutlich schneller, aber auch hier die lineare Verlangsamung, und mit den zwei Tags bei denen MySQL nur Millisekunden benötigt, ist SQLite auch schon bei 0.6 Sekunden, und dann habe ich ja auch bisher nur Episoden, dazu kämen dann ja noch Filmreihen, Filme, Clips, etc...