Archief - query duurt 33sec :(

hallo,
ik heb ne site me filmkes en de gegevens enzo (gelijk tmdb.com).
als ik nu een film verwijder, moeten de persone die enkel in die bepaalde film speelde mee verwijderen.

voor deze query zijn 2 tabellen nodig: "movie_person" en "person":
- tabel "person" is een tabbel waar elke persoon uniek in voorkomt
- tabel "movie_person" komen sommige mensen meerdere keren voor (voor de films waar zij in meespelen)

als ik "select distinct person_id from movie_person" uitvoer, dan geeft mysql alle personen weer die meedoen aan een film die nog op mijn site staat

als ik "select * from person" uitvoer, dan geeft mysql alle personen weer, ook de die die meespelen in films die niet op de site staan

nu als ik "select * from person where id not in (select distinct person_id from movie_person)" uitvoer, dan worden alle personen weer gegeven die niet meer nodig zijn (dus in films die niet meer op de site staan).
het probleem is: deze query duurt 33sec.

als ik de "NOT" er uit laat duurt de query terug 2sec, hoe kan ik dit oplossen? Het probleem zit blijkbaar bij de "NOT"-selector...

Het probleem zal eerder liggen aan het ontbreken van indexen/relaties en aan het gebruiken van een subquery.

Vervang je query al eens door de volgende :

select p.* from person p left join movie_person mp on mp.person_id = p.id where mp.person_id is null

Klik om te vergroten...

Als je de (volledige) create statements van je tabellen hier neerzet dan kunnen die desnoods ook verder geoptimaliseerd worden.

of de NOT EXIST operator. Dan moet je geen join leggen. Wel is dan een FK nodig anders werkt die operator niet.

Best ook idd naar de indexes kijken of die bestaan.

mja, djeez, uwe query duurt ook een eeuwigheid zenne

passero: de not exist dan moe ge toch nog verwijze naar 2 tabellen?

--
-- Tabelstructuur voor tabel `person`
--

CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`popularity` int(11) DEFAULT NULL,
`name` text,
`also_known_as` text,
`biography` longtext,
`known_movies` int(11) DEFAULT NULL,
`birthday` text,
`birthplace` text,
`url` text,
`version` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1088573 ;

--
-- Tabelstructuur voor tabel `movie_person`
--

CREATE TABLE `movie_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) DEFAULT NULL,
`person_id` int(11) DEFAULT NULL,
`cast_id` int(11) DEFAULT NULL,
`order` int(11) DEFAULT NULL,
`character` text,
`department` text,
`job` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28310 ;

ben is aan het kijken naar die foreign key, is precies wel iets goe... kan het zo misschien oplosse

hm, de query is van 33sec terug gebracht naar 0.5sec door aan movie_person.person_id een index te zette. wat is dit en hoe komt da mijn query ineens zo snel gaat hierdoor?

Wat je eigenlijk probeert is een ON DELETE CASCADE

CASCADE
Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update).

Klik om te vergroten...

Wanneer je een relatie legt van een master tabel naar een child tabel, zet men (meestal) hierop een index. Gezien het normaal de bedoeling is om aan de hand van deze relatie data op te halen.

But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, you'll often need to lookup a relating table and extract certain rows based on a single value or a range of values.

Klik om te vergroten...

sql server - Does a foreign key automatically create an index? - Stack Overflow

Dus zoals door djeez & passero gezegd zet je FK's en indexen in orde.

Voor veel DB's (MS SQL, Oracle) bestaan er tools zoal Explain Plan die de kost van uw query uitleggen. (bestaat dit eigenlijk ook voor MySQL?)

In uw geval gaat die waarschijnlijk n+1 full table scans doen waarbij n het aantal records is in movie_person. De reden waarom is omdat je met een subquery zit. Die subquery wordt uitgevoerd voor elk record om te zien of de ID daarin voorkomt. Vandaar ook een full table scan.

Als je nu een index plaatst op die tabel dan moet de DB engine geen full table scan meer doen maar spreekt die gewoon de index aan. Aangezien indexen geordend zijn kan die veel vlugger weten of uw subquery resultaten gaat geven.

Door gebruik te maken van een outer join of een (NOT) EXIST zorg je ervoor dat de DB exact op die index gaat zoeken.

Het lijkt erop dat je het concept van FK's en indexen niet echt goed kent... Best eens wat meer in verdiepen want die zorgen voor integriteit in uw data en performantie.

MySQL en PostgreSQL hebben net zoals elke SQL compliant database support voor het EXPLAIN commando ja. Je krijgt dan een mooi rapportje terug van welke indexen gebruikt worden, waar je geen indexen gebruikt, waar je n+1 scans doet etc.

Heel wat testframeworks hebben trouwens slow query detection van waaruit je dan een explain op de laatste query kan gaan triggeren.