[MySQL] basistabel forceren

[P.O. Box]

Ik heb een vreemd probleempje met MySQL wat vast logisch te verklaren is, maar ik kan het niet vinden...

ik heb 3 tabellen:
- statecodes
- invoices
- invoice_lines

de tabel statecodes bevat zo'n 12 statussen waarvan er 5 het vinkje "end_status" op false hebben staan.
de tabel invoices bevat zo'n 50.000 records
de tabel invoice_lines bevat zo'n 300.000 records

statecodes tabel heeft een vrij nutteloze index (in een wanhopige poging aangemaakt) op end_status
invoices heeft meerdere indexen, maar ook 1 op statecodeid
invoice_lines heeft een index op invoiceid

nu wil ik een overzicht van alle invoices (+ wat info uit de invoice_lines) die een statuscode hebben waarvoor de end_status = false... dit zijn zo'n 100 orders max.

dus een simpele query:

select bla
from statecodes
left join invoices on statecodes.id = invoices.statecodeid
left join invoice_lines on invoices.id = invoice_lines.invoiceid
where statecodes.end_status = 0

niet al te spannend...

als ik echter nu een EXPLAIN doe op deze query, zie ik dat MySQL uit zichzelf invoices als basistabel neemt en dus feitelijk de query zo uitvoert:

select bla
from invoices
left join statecodes on statecodes.id = invoices.statecodeid
left join invoice_lines on invoices.id = invoice_lines.invoiceid
where statecodes.end_status = 0

als ik de invoice_lines join weghaal, dan neemt hij wel keurig statecodes als basistabel...

ik krijg het vermoeden dat het iets te maken heeft met het aantal records in invoices... dat die tabel daarom voorrang krijgt en als basistabel wordt gebruikt... maar dat wil ik helemaal niet...
ik weet zeker dat er maar max 100 orders zullen zijn met een end_status = 0... dus de query zou, als hij hem uitvoert zoals ik hem origineel intyp, gewoon snel moeten zijn...

mijn vragen:
- maak ik een denkfout?
- is er een manier om MySQL te zeggen welke tabel de basistabel moet zijn (behalve de volgorde van de aanroep van de tabellen.. want dat werkt blijkbaar niet).

[TheNephilim]

Dat doet de query optimizer: http://dev.mysql.com/doc/...uery-plan-evaluation.html

Die kun je niet zomaar uitzetten voor een bepaalde query, misschien op deze manier: http://dev.mysql.com/doc/...chable-optimizations.html

Maar... als MySQL je query op deze manier optimized, dan is dat vaak de beste manier. Dus dat doet hij niet alleen in je EXPLAIN maar ook op de plek waar je deze query gebruikt om gegevens op te halen.

[P.O. Box]

TheNephilim schreef op woensdag 14 november 2012 @ 13:54:

Maar... als MySQL je query op deze manier optimized, dan is dat vaak de beste manier. Dus dat doet hij niet alleen in je EXPLAIN maar ook op de plek waar je deze query gebruikt om gegevens op te halen.

dat dacht ik ook... maar kan me haast niet voorstellen dat deze manier ook echt de beste manier is... wil het dus zelf testen


heb nu het volgende gedaan:

alle optimizer_switches stonden op "on", heb ze allemaal op "off" gezet en daarna een nieuwe explain gedaan... maar nog steeds pakt hij de "verkeerde" tabel als basis...

[ Voor 18% gewijzigd door P.O. Box op 14-11-2012 14:29 ]

[frG]

Ik snap het probleem niet echt, je wilt toch juist de invoices hebben? een join daarbij op statecodes is prima, zodat de range van de invoicelines beperkt wordt tot alleen de invoices met de end_status = false

Ik vermoed dat de reden dat de query optimizer invoices als eerst pakt is omdat daarmee de range het kleinst wordt gemaakt, wat resulteerd in de snelste query.

[ Voor 28% gewijzigd door frG op 14-11-2012 14:45 ]

[P.O. Box]

frG schreef op woensdag 14 november 2012 @ 14:40:
Ik snap het probleem niet echt, je wilt toch juist de invoices hebben? een join daarbij op statecodes is prima, zodat de range van de invoicelines beperkt wordt tot alleen de invoices met de end_status = false

het probleem is dat er nu 50.000 records worden doorlopen, terwijl dat, door gebruik van de indexen er slechts 100 hoeven te zijn...
ik krijg het resultaat wel.. dat is mijn probleem niet... de query duurt alleen "lang"...
er is inmiddels geheugen bijgeprikt in de database-server (er zat maar 2Gb RAM in).. dat heeft voor de snelheid wel geholpen.. de query is nu snel... maar ja... het gevoel blijft mij bekruipen dat er een snellere manier moet zijn....

frG schreef op woensdag 14 november 2012 @ 14:40:
IIk vermoed dat de reden dat de query optimizer invoices als eerst pakt is omdat daarmee de range het kleinst wordt gemaakt, wat resulteerd in de snelste query.

dat zal idd best kunnen... maar het gaat tegen mijn gevoel in.. en daarom wil ik toch op z'n minst de mogelijkheid hebben om te testen wat er met de snelheid van de query gebeurt als hij wordt uitgevoerd onder mijn voorwaarden... nu kom ik daar dus niet achter...

[ Voor 25% gewijzigd door P.O. Box op 14-11-2012 14:46 ]

[frG]

Dan zul je een inner join moeten gebruiken..

[Hydra]

Invoices koppelt naar 2 andere tabellen dus dan is het m.i. logisch dat hij probeert deze als eerste te beperken.

[P.O. Box]

frG schreef op woensdag 14 november 2012 @ 14:48:
Dan zul je een inner join moeten gebruiken..

dat klinkt niet echt logisch toch?

Hydra schreef op woensdag 14 november 2012 @ 14:53:
Invoices koppelt naar 2 andere tabellen dus dan is het m.i. logisch dat hij probeert deze als eerste te beperken.

dat kan logisch zijn... het is niet wat ik wil

[Hydra]

Zit er een index op statecodes.id?

[T-MOB]

Een inner join is wel logisch. Voor het joinen net statecodes-tabel omdat alle rijen die je terug wilt hebben sowieso een rij in de statecode tabel moeten hebben. Voor invoice_lines-tabel heb je alleen een LEFT join nodig als er ook facturen zonder factuurregels kunnen bestaan.


De grote vraag is waarom je invloed wil hebben op wat mysql precies doet. In de meeste gevallen zal MySQL het prima doen.. Oh, dat had je wel uitgelegd, je wil testen of het op een andere manier sneller kan.

Je kunt de join ook omschrijven naar een subquery. Dat geeft je meer controle over wat er eerst gebeurt.

code:

select bla
from invoices
left join invoice_lines on invoices.id = invoice_lines.invoiceid
where invoices.statecodeid IN (SELECT id FROM statecodes where end_status = 0)

[ Voor 32% gewijzigd door T-MOB op 14-11-2012 15:28 ]