Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Schema mit Vererbung von Assoziationen

Dieses Thema im Forum "Datenmodellierung, Datenbank-Design" wurde erstellt von timofs, 18 Juni 2015.

  1. timofs

    timofs Benutzer

    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

    diagramm.jpg

    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 :)
     
  2. akretschmer

    akretschmer Datenbank-Guru

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

    Distrilec Datenbank-Guru

    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...
     
    Zuletzt von einem Moderator bearbeitet: 18 Juni 2015
  4. timofs

    timofs Benutzer

    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 :)
     
  5. timofs

    timofs Benutzer

    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?
     
    Zuletzt von einem Moderator bearbeitet: 18 Juni 2015
  6. akretschmer

    akretschmer Datenbank-Guru

    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.
     
  7. Distrilec

    Distrilec Datenbank-Guru

    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
     
  8. timofs

    timofs Benutzer

    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.
     
  9. timofs

    timofs Benutzer

    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 :)
     
  10. Distrilec

    Distrilec Datenbank-Guru

    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.
     
  11. timofs

    timofs Benutzer

    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.
     
  12. Distrilec

    Distrilec Datenbank-Guru

    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

    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)
    );
     
  13. timofs

    timofs Benutzer

    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?
     
  14. akretschmer

    akretschmer Datenbank-Guru

    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.
     
    Distrilec gefällt das.
  15. timofs

    timofs Benutzer

    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...
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden