Optimering af kæmpe SQL-kald

Glemte at sige, jeg har kun MySQL 4.0.24 til rådighed.

Det lyder helt absurdt at det skulle tage 7-8 sekunder med 200 raekker.

Prepared statement kan hjaelpe nogen gange. Men jeg mener ikke at det er tilfaeldet
for MySQL 4.0.

Proev og se hvad en EXPLAIN paa den query siger.

EXPLAIN ser sådan ud:

Array
(
    [table] => soegerkommuner
    [type] => ALL
    [possible_keys] => ejer_fk,kommune
    [key] =>
    [key_len] =>
    [ref] =>
    [rows] => 1374
    [Extra] => Using temporary; Using filesort
)
Array
(
    [table] => jobsoeger
    [type] => eq_ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 4
    [ref] => soegerkommuner.ejer_fk
    [rows] => 1
    [Extra] =>
)
Array
(
    [table] => virkkommuner
    [type] => ref
    [possible_keys] => kommune,annonce_fk
    [key] => kommune
    [key_len] => 1
    [ref] => soegerkommuner.kommune
    [rows] => 18
    [Extra] =>
)
Array
(
    [table] => jobannonce
    [type] => eq_ref
    [possible_keys] => PRIMARY,godkendt,deaktiveret,tid_start,tid_slut
    [key] => PRIMARY
    [key_len] => 4
    [ref] => virkkommuner.annonce_fk
    [rows] => 1
    [Extra] => Using where
)
Array
(
    [table] => virkbrancher
    [type] => ref
    [possible_keys] => branche,annonce_fk
    [key] => annonce_fk
    [key_len] => 4
    [ref] => jobannonce.id
    [rows] => 3
    [Extra] =>
)
Array
(
    [table] => soegerbrancher
    [type] => ref
    [possible_keys] => ejer_fk,branche
    [key] => ejer_fk
    [key_len] => 4
    [ref] => jobsoeger.id
    [rows] => 10
    [Extra] => Using where
)

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.

Nå, jeg bliver nødt til at gå i seng, men jeg sætter meget stor pris på jeres hjælp. Aner simpelthen ikke hvor jeg ellers skulle gå hen med det.

Hvordan set tabel og index definition ud for soegerkommuner ?

CREATE TABLE `soegerkommuner` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ejer_fk` int(10) unsigned NOT NULL default '0',
  `kommune` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `ejer_fk` (`ejer_fk`),
  KEY `kommune` (`kommune`)
) TYPE=MyISAM AUTO_INCREMENT=2323;

MySQL optimizeren gør et elendigt stykke arbejde her !

Prøv og byt lidt om på rækkefølgen af tabellerne i FROM og se om optimizeren får
et skub i den rigtige retning.

Okay, jeg aner ikke hvorfor, men pludselig ser den helt anderledes ud. Men kører stadig ikke hurtigere:

Array
(
    [table] => jobannonce
    [type] => ref
    [possible_keys] => PRIMARY,godkendt,deaktiveret,tid_start,tid_slut
    [key] => deaktiveret
    [key_len] => 1
    [ref] => const
    [rows] => 168
    [Extra] => Using where; Using temporary; Using filesort
)
Array
(
    [table] => virkbrancher
    [type] => ref
    [possible_keys] => branche,annonce_fk
    [key] => annonce_fk
    [key_len] => 4
    [ref] => jobannonce.id
    [rows] => 3
    [Extra] =>
)
Array
(
    [table] => virkkommuner
    [type] => ref
    [possible_keys] => kommune,annonce_fk
    [key] => annonce_fk
    [key_len] => 4
    [ref] => jobannonce.id
    [rows] => 17
    [Extra] =>
)
Array
(
    [table] => soegerkommuner
    [type] => ref
    [possible_keys] => ejer_fk,kommune
    [key] => kommune
    [key_len] => 1
    [ref] => virkkommuner.kommune
    [rows] => 14
    [Extra] =>
)
Array
(
    [table] => jobsoeger
    [type] => eq_ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 4
    [ref] => soegerkommuner.ejer_fk
    [rows] => 1
    [Extra] =>
)
Array
(
    [table] => soegerbrancher
    [type] => ref
    [possible_keys] => ejer_fk,branche
    [key] => ejer_fk
    [key_len] => 4
    [ref] => jobsoeger.id
    [rows] => 9
    [Extra] => Using where
)

Nu er der pludselig ingen type=>ALL, og det er ´jobannonce´ der er synderen.
Den ser således ud:

CREATE TABLE `jobannonce` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ejer_fk` int(10) unsigned NOT NULL default '0',
  `profil_fk` int(10) unsigned NOT NULL default '0',
  `titel` varchar(64) NOT NULL default '',
  `tekst` text NOT NULL,
  `tid_start` int(10) unsigned NOT NULL default '0',
  `tid_slut` int(10) unsigned NOT NULL default '0',
  `oprettet` int(10) unsigned NOT NULL default '0',
  `godkendt` tinyint(2) unsigned NOT NULL default '0',
  `deaktiveret` tinyint(2) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `godkendt` (`godkendt`),
  KEY `deaktiveret` (`deaktiveret`),
  KEY `tid_start` (`tid_start`),
  KEY `tid_slut` (`tid_slut`),
  KEY `profil_fk` (`profil_fk`),
  KEY `ejer_fk` (`ejer_fk`)
) TYPE=MyISAM AUTO_INCREMENT=286;

... vil det hjælpe at flytte titel og tekst hen i en anden tabel mon?

Hvis I ikke kan hjælpe mig mere, må I gerne smide svar så I i hvert fald kan få lidt for forsøget.

Det var værd at prøve med at flytte felter af typen TEXT ud i separat tabel.

og et svar fra mig

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.

Der er stadig visse ting der kan prøves.

1)  Er det muligt at opdatere MySQL ? 4.0.12 er fra marts 2003, hvilket er meget gammelt !

2)  Kan du give MySQL mere memory ? (diverse buffer sizes i mysql.ini/cfg)

3)  Måske kan den SQL omskrives til at "narre" MySQL.

re 3)

Eksempel:

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.