[MYSQL] Trage query door join

[Erpenator2]

Beste Tweakers,

Ik heb een query die er veel te lang over doet om de gegevens op te halen. De betreffende query:

code:

SELECT 
    SQL_CALC_FOUND_ROWS
    t1.storyid,
    t1.subject,
    t1.addeddate,
    t1.content,
    t1.location,
    DATE_FORMAT(t1.addeddate, "%d %b %Y  %H:%i") AS onlinedatetime,
    t2.name AS sitename,
    t2.country AS sitecountry,
    t2.websiteid,
    t2.text,
    cc.cnn AS country,
    cc.ci AS countryid
FROM 
    t1
JOIN t2 ON t1.websiteid = t2.websiteid
LEFT JOIN cc ON cc.ci = t1.countryid
WHERE t1.autosave = 0
AND     LENGTH(t2.password) = 0
LIMIT 5

De join met t2 is het probleem geval. Wanneer je deze weghaalt uit de query gaat alles goed en werkt deze op normale snelheid. Op t1.websiteid ligt een index net als op t2.websiteid. Ik denk dat het probleem te maken heeft met het leggen van een juiste index of het iets omschrijven van de query. Wanneer ik deze query uitbreid met een where statement waarin ik filter op websiteid dan wordt de index op t1.websiteid aangeroepen en is de query super snel.

Een explain van de query geeft:

code:

1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4744 Using where 
1 SIMPLE t1 ref websiteid websiteid 5 t2.websiteid 16 Using where 
1 SIMPLE cc eq_ref PRIMARY PRIMARY 1 t1.countryid 1

Zoals je ziet loopt hij alle records van t2 na terwijl ik dat erg vreemd vind. Hij zou toch eerst gewoon de laatste 5 records van t1 moeten ophalen en daarna joinen met die resultaten die hij gevonden heeft?

Wanneer ik op wat voor manier een index wil laten gebruiken (met use of force index in de query) heeft dat geen resultaat. Iemand een idee waarom deze query zo langzaam is of wat ik kan veranderen om met één statement toch sneller dezelfde informatie op te halen?

[RobIII]

Waar hoort mijn topic?
SEA >> PRG

[Kalentum]

Die LIMIT wordt pas als allerlaatste uitgevoerd.

De table scan op t2 wordt uitgevoerd op alle records omdat je een functie gebruikt. Je zou kunnen kijken wat er gebeurt als je LENGTH(t2.password) vervangt door NOT ( t2.password = '' OR t2.password IS NULL). Als je een index heb op t2.password zal MySQL die index gebruiken.

[jbdeiman]

Waarom gebruik je tussen t1 en t2 eigenlijk "join" en geen "left join"?

[Erpenator2]

Het weghalen of het veranderen van LENGTH(websites.password) lijkt geen effect te hebben. Ook de explain geeft dezelfde resultaten terug met of zonder de length. Toch zou het kunnen dat daar de problemen liggen, aangezien dat wel het laatste is wat ik aan de query heb aangepast.

Edit:
Ik gebruik geen left join omdat er altijd een relatie moet zijn tussen de t1 en t2, bij de andere joins is dat niet het geval. Ik heb van de join ook al een right en straight join gemaakt (gewoon om te kijken of het hielp).

[ Voor 29% gewijzigd door Erpenator2 op 29-06-2010 10:38 ]

[sopsop]

Zoals rutgerw al stelt: de LIMIT staat niet op tabel T1. Hij bepaalt de limit over het resultaat van de query. Als je feitelijk echt alleen de laatste 5 records van T1 wilt hebben, dan zou je kunnen overwegen om er een subquery van te maken. Een echt nette oplossing is het echter niet:

SQL:

SELECT 
    SQL_CALC_FOUND_ROWS
    t1a.storyid,
    t1a.subject,
    t1a.addeddate,
    t1a.content,
    t1a.location,
    DATE_FORMAT(t1a.addeddate, "%d %b %Y  %H:%i") AS onlinedatetime,
    t2.name AS sitename,
    t2.country AS sitecountry,
    t2.websiteid,
    t2.text,
    cc.cnn AS country,
    cc.ci AS countryid
FROM 
    (SELECT top 5 storyid, subject, addeddate, content, location, websiteid, countryid, autosave 
     FROM T1
     WHERE autosave = 0
     ORDER BY addeddate desc) t1a
INNER JOIN t2 ON t1a.websiteid = t2.websiteid
LEFT JOIN cc ON cc.ci = t1a.countryid
WHERE LENGTH(t2.password) = 0

[Voutloos]

Mysql moet toch alles joinen dankzij SQL_CALC_FOUND_ROWS. Die limit scheelt hoogstens in het versturen van je result, maar in dit geval wordt het uitvoeren van de query dus niet echt goedkoper.

[Erpenator2]

De suggestie van sobsob brengt de query van ruim 7 sec terug naar 0.2! Dus er ligt nu in ieder geval een oplossing voor het probleem die ik zou kunnen gebruiken. Maar het enige wat ik niet begrijp is waarom mysql geen index gebruikt voor de join, is dat omdat deze er niet is, of omdat mysql er geen kan vinden?

[Voutloos]

Als die autosave en/of password conditie de meeste rows filtert moet je juist zorgen dat je daarop een index kan gebruiken. Je hebt nu het executie plan van alles joinen en dan pas filteren in je hoofd, maar dat is meestal niet het snelst.

[Erpenator2]

Er liggen al indexen op de autosave (in combinatie met websiteid ) en op de password. Deze lijken echter niet getriggert te worden, dus mogelijk zijn ze verkeerd. Hier zou ik dan even moeten spelen om te kijken wat wel en niet werkt.

[eamelink]

Het uitvoeren van twee queries (éénmaal met de limit en de velden die je wilt selecteren, en éénmaal zonder de limit en alleen een count(*)) is overigens in de meeste gevallen sneller dan het gebruik van SQL_CALC_FOUND_ROWS