30. maj 2008 - 10:57Der er
15 kommentarer og 2 løsninger
Optimering af kæmpe SQL-kald
Jeg har en jobportal med et temmelig omfattende SQL-kald i match-filen, der sender e-mails ud om nye jobs. Det ser sådan her ud:
SELECT jobannonce.id, jobsoeger.id, jobsoeger.email, jobannonce.ejer_fk, soegerbrancher.branche, jobannonce.titel, jobannonce.tekst from jobannonce, virkbrancher, soegerbrancher, jobsoeger, virkkommuner, soegerkommuner WHERE jobsoeger.id=soegerbrancher.ejer_fk AND soegerbrancher.branche=virkbrancher.branche AND jobannonce.id=virkbrancher.annonce_fk AND jobannonce.deaktiveret=0 AND jobannonce.godkendt=1 and virkkommuner.kommune=soegerkommuner.kommune and virkkommuner.annonce_fk=jobannonce.id AND soegerkommuner.ejer_fk=jobsoeger.id AND jobannonce.tid_start<$time AND jobannonce.tid_slut>$time GROUP BY jobannonce.id, jobsoeger.id
Alt der sammenlignes er INT datatyper, og der er index på dem alle.
Udførslen af ovennævnte tager cirka 7-8 sekunder på vores halvgamle dedikerede server, med ~200 aktive jobs i databasen. Det går an for nu, men vi skal ikke have ret mange flere før vi kommer op i noget fuldstændig uacceptabelt.
Så, SQL-eksperter, hvordan får jeg det her lavet bedre? Vil f.eks. prepared statements kunne give mig noget? Kan jeg lave strukturen om så det går hurtigere?
Jeg har prøvet at omskrive forespørgslen lidt for at gøre den lidt mere gennemskuelig (i det mindste for mig selv ;.)):
SELECT jobannonce.id, jobsoeger.id, jobsoeger.email, jobannonce.ejer_fk, soegerbrancher.branche, jobannonce.titel, jobannonce.tekst FROM jobannonce INNER JOIN virkkommuner ON jobannonce.id=virkkommuner.annonce_fk INNER JOIN virkbrancher ON jobannonce.id=virkbrancher.annonce_fk INNER JOIN soegerbrancher ON soegerbrancher.branche=virkbrancher.branche INNER JOIN jobsoeger ON jobsoeger.id=soegerbrancher.ejer_fk INNER JOIN soegerkommuner ON soegerkommuner.ejer_fk=jobsoeger.id WHERE jobannonce.deaktiveret=0 AND jobannonce.godkendt=1 AND jobannonce.tid_start<$time AND jobannonce.tid_slut>$time and virkkommuner.kommune=soegerkommuner.kommune GROUP BY jobannonce.id, jobsoeger.id
Er branche og kommune felterne også indexerede? - for jeg går ud fra (baseret på feltnavne), at de ikke er integer felter, men snarere streng felter. De bliver imidlertid også brugt til at finde og sammenligne rækker fra flere tabeller. Hvis de ikke er indexerede, bør du overveje at oprette indexes på disse felter.
Det er ikke meget at gå efter, og omskrivningen i sig selv forventer jeg mig ikke meget af.
Jeg ved godt Using temporary; Using filesort og type=>ALL er dårligt, men hvordan undgår jeg det? soegerkommuner tabellen indeholder kun tre INT-værdier, og der er index på alle tre.
kjulius: Nej, de er INT-felter, og ja de er indekserede. Branche og kommune henviser til andre tabeller for at få navnene - her er der kun tale om sammenligninger af tal.
Det gjorde desværre ingen forskel at flytte det ud. Jeg prøvede at få den til at oprette en temp-tabel med kun talværdierne før den kørte, og det gik også fint og hurtigt med at oprette den - men selve query'en tager samme tid som med den almindelige, hvilket nu er oppe på 25-30+ sekunder; altså uacceptabelt.
EXPLAIN siger det samme: jobannonce_temp er synderen med Using temporary; using filesort. Aner stadig ikke hvorfor det opstår, eller præcis hvad det betyder.
Nogle råd til hvor jeg kunne gå hen med det? Det er et stort problem, da min arbejdsgiver nok ikke vil blive alt for begejstret for at høre, at hans jobportal ikke længere kan køre. Jeg vil helt sikkert høre "Jamen jobindex osv virker jo fint, og de er meget større". Jeg er ikke en dårlig programmør, men jeg aner ikke hvad jeg gør forkert her.
SELECT * FROM a,b,c WHERE a.x=b.x AND b.y=c.y AND a.z=77
til:
SELECT * FROM (a INNER JOIN b ON a.x=b.x AND a.z=77) INNER JOIN c ON b.y=c.y
hvor de joins der returnerer færrest rækker puttest inderst.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.