[MySQL] trage query

[Verwijderd]

Hi,

ik zit op dit moment met een query waarbij ik middels meerdere left joins enkele tabellen aan elkaar knoop en vervolgens nog voorzie van een Group By en een Order


Text tabel en textdetails bevatten +- 800k records
Copy tabel en copydetails bevatten +- 200k records
De andere tabellen zijn een stuk kleiner.

Ik heb foreign keys op iedere kolom waarop ik de left join uit voer. Daarnaast heb ik indexes op de overige kolom die ik in de where statement heb zitten. De query draait echter nog steeds 40 seconden. Als ik de Group By weg laat verbeterd het al iets. Als ik vervolgens de Order weg laat gaat het weer wat sneller.

code:

SELECT * 
FROM  `copy` 
LEFT JOIN  `domain` ON domain.domain_id = copy.copy_domain_id
LEFT JOIN  `domaincategory` ON copy.copy_domain_id = domaincategory.domaincategory_domain_id
AND domaincategory.domaincategory_account_id = copy.copy_account_id
LEFT JOIN  `text` ON text.text_id = copy.copy_text_id
LEFT JOIN  `textdetails` ON textdetails.textdetails_text_id = text.text_id
LEFT JOIN  `channel` ON channel.channel_domain_id = domain.domain_id
AND channel.channel_account_id = copy.copy_account_id
LEFT JOIN  `feed` ON feed.feed_id = text.text_feed_id
WHERE (
feed.feed_account_id =96
)
AND (
feed.feed_flag_delete IS NULL
)
AND (
text.text_flag_delete IS NULL
)
AND (
copy.copy_flag_delete IS NULL
)
AND (
copy.copy_tracking_date_found IS NOT NULL
)
AND (
channel.channel_active =1
)
GROUP BY  `copy`.`copy_id` 
ORDER BY  `copy`.`copy_tracking_date_found` DESC 
LIMIT 50

Ik heb een EXPLAIN uitgevoerd maar die kan ik onvoldoende begrijpen.
Kan iemand mij wat wegwijs maken om een en ander te optimaliseren?

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	feed	ref	PRIMARY,fk_feed_account_id,feed_flag_delete	fk_feed_account_id	4	const	1	Using where; Using temporary; Using filesort
1	SIMPLE	text	ref	PRIMARY,fk_text_feed_id,text_flag_delete	text_flag_delete	2	const	2628	Using where
1	SIMPLE	textdetails	ref	fk_textdetails_text_id	fk_textdetails_text_id	5	text.text_id	1
1	SIMPLE	copy	ref	fk_copy_account_id,fk_copy_domain_id,fk_copy_text_...	fk_copy_text_id	4	text.text_id	2	Using where
1	SIMPLE	domain	eq_ref	PRIMARY	PRIMARY	4	copy.copy_domain_id	1	Using where
1	SIMPLE	domaincategory	ref	fk_domaincategory_account_id,fk_domaincategory_dom...	fk_domaincategory_domain_id	4	domain.domain_id	1
1	SIMPLE	channel	ref	fk_channel_account_id,fk_channel_domain_id,channel...	fk_channel_domain_id	4	copy.copy_domain_id	2	Using where

[ Voor 15% gewijzigd door Verwijderd op 28-10-2015 16:30 ]

[MarcoC]

Wellicht handig om de query hier ook even te plaatsen?

Staat er inmiddels al bij.

Als ik even heel snel kijk, lijken die WHERE clauses mij overbodig. Volgens mij kan je die samenvoegen met een JOIN clause. Volgens mij moet dit ook kunnen:

code:

SELECT * 
FROM  `copy` 
LEFT JOIN  `domain` ON domain.domain_id = copy.copy_domain_id
LEFT JOIN  `domaincategory` ON copy.copy_domain_id = domaincategory.domaincategory_domain_id
AND domaincategory.domaincategory_account_id = copy.copy_account_id
LEFT JOIN  `text` ON text.text_id = copy.copy_text_id AND text.text_flag_delete IS NULL
LEFT JOIN  `textdetails` ON textdetails.textdetails_text_id = text.text_id
LEFT JOIN  `channel` ON channel.channel_domain_id = domain.domain_id AND channel.channel_active =1
AND channel.channel_account_id = copy.copy_account_id
LEFT JOIN  `feed` ON feed.feed_id = text.text_feed_id AND feed.feed_account_id =96 AND feed.feed_flag_delete IS NULL
WHERE
copy.copy_flag_delete IS NULL
AND 
copy.copy_tracking_date_found IS NOT NULL
GROUP BY  `copy`.`copy_id` 
ORDER BY  `copy`.`copy_tracking_date_found` DESC 
LIMIT 50

[ Voor 110% gewijzigd door MarcoC op 28-10-2015 16:35 ]

[emnich]

We hebben te weinig informatie om er echt iets van te maken, daarvoor heb je ook de query nodig en de structuur van de tables.

Het wordt in elk geval langzaam omdat het sorteren niet via een index gaat (using filesort). Je kan proberen je index aan te passen maar zoals gezegd, met deze info kan je onmogelijk een goede optimalisatie doen.

[wha]

Wel eens explain gebruikt daarmee kan je dit soort dingen zien?

[emnich]

Verwijderd schreef op woensdag 28 oktober 2015 @ 16:29:

code:

SELECT * 
FROM  `copy` 
LEFT JOIN  `domain` ON domain.domain_id = copy.copy_domain_id
LEFT JOIN  `domaincategory` ON copy.copy_domain_id = domaincategory.domaincategory_domain_id
AND domaincategory.domaincategory_account_id = copy.copy_account_id
LEFT JOIN  `text` ON text.text_id = copy.copy_text_id
LEFT JOIN  `textdetails` ON textdetails.textdetails_text_id = text.text_id
LEFT JOIN  `channel` ON channel.channel_domain_id = domain.domain_id
AND channel.channel_account_id = copy.copy_account_id
LEFT JOIN  `feed` ON feed.feed_id = text.text_feed_id
WHERE (
feed.feed_account_id =96
)
AND (
feed.feed_flag_delete IS NULL
)
AND (
text.text_flag_delete IS NULL
)
AND (
copy.copy_flag_delete IS NULL
)
AND (
copy.copy_tracking_date_found IS NOT NULL
)
AND (
channel.channel_active =1
)
GROUP BY  `copy`.`copy_id` 
ORDER BY  `copy`.`copy_tracking_date_found` DESC 
LIMIT 50

Maak eens een indexes op:
text.text_id, text.text_feed_id, text.flag_delete
copy.copy_text_id,copy.copy_id
en plaats dan de EXPLAIN nogmaals

[DJMaze]

Uitgaande van de where naar joins lijkt dit er meer op

code:

SELECT * 
FROM  `copy` 
INNER JOIN  `domain` ON domain.domain_id = copy.copy_domain_id
INNER JOIN  `text` ON text.text_id = copy.copy_text_id
    AND text.text_flag_delete IS NULL
INNER JOIN  `channel` ON channel.channel_domain_id = domain.domain_id
    AND channel.channel_account_id = copy.copy_account_id
    AND channel.channel_active =1
INNER JOIN  `feed` ON feed.feed_id = text.text_feed_id
    AND feed.feed_account_id =96
    AND feed.feed_flag_delete IS NULL
LEFT JOIN  `domaincategory` ON domaincategory.domaincategory_domain_id = copy.copy_domain_id
    AND domaincategory.domaincategory_account_id = copy.copy_account_id
LEFT JOIN  `textdetails` ON textdetails.textdetails_text_id = text.text_id
WHERE copy.copy_flag_delete IS NULL
  AND copy.copy_tracking_date_found IS NOT NULL
GROUP BY  `copy`.`copy_id` 
ORDER BY  `copy`.`copy_tracking_date_found` DESC 
LIMIT 50

Je indices dan aanpassen in:

code:

CREATE INDEX i_copy_tracking_delete ON copy (copy_flag_delete, copy_tracking_date_found);
CREATE INDEX i_text_id_flag_delete ON text (text_id, text_flag_delete);
CREATE INDEX i_channel_ids_active ON channel (channel_domain_id, channel_account_id, channel_active);
CREATE INDEX i_feed_ids_delete ON feed (feed_id, feed_account_id, feed_flag_delete);
CREATE INDEX i_domaincategory_ids ON domaincategory (domaincategory_domain_id, domaincategory_account_id);

P.S. Upgraden naar de nieuwste MySQL is ook een optie, die doet een union op indices

[ Voor 5% gewijzigd door DJMaze op 29-10-2015 15:12 ]

[Verwijderd]

Dank voor de reacties!

Ik heb dit voor 2 accounts getest.
- het eerste account heeft 1 feed, 40k text_id's en 10k copy_id's
- het tweede account heeft 5 feeds, 70k text_id's en 150k copy_id's

Voor het eerste account duurde de query nog maar 1.5 sec.
Voor het tweede account duurde de query nog steeds 40+ sec.

Daar kom ik echter nog niet helemaal uit. Ik heb er o.a. ook even http://www.codeproject.co...presentation-of-SQL-Joins bij gepakt. Ik moet me zeker nog verder inlezen!

Zou je mij een korte toelichting willen geven DJMaze?

[ Voor 37% gewijzigd door Verwijderd op 29-10-2015 21:25 ]

[Verwijderd]

Verwijderd schreef op donderdag 29 oktober 2015 @ 20:43:


- het tweede account heeft 5 feeds, 70k text_id's en 150k copy_id's

150k op 200k dus geen index op copy gebruiken zou ik zeggen.

[ Voor 31% gewijzigd door Verwijderd op 29-10-2015 23:39 ]

[DJMaze]

Ik neem aan dat je nu het verschil weet tussen de verschillende JOIN opties, dan rest dus de rest.

Analyseer je queries goed en in welke volgorde de EXPLAIN ze zet.
Kijk ook goed welke WHERE/ON conditions er zijn.

Voor de feed tabellen kijk je naar:
feed.feed_id = text.text_feed_id
feed.feed_account_id = 96
feed.feed_flag_delete IS NULL

Je kan dan 3 indices maken (1 voor elke kolom) en hopen dat MySQL een union/intersect doet https://dev.mysql.com/doc...x-merge-optimization.html
Dit hoeft niet, en is per MySQL/MariaDB versie verschillend (vandaar die andere indices van mij hierboven).
Ik werk zelf met MariaDB 5.6.26 en krijg bij sommige EXPLAIN queries netjes een sort_union, maar op de productie server een "Bad index used in query/prepared statement".

Het is dus vooral spelen met je structuur.

Wat er dan overblijft is de "Using temporary; Using filesort"
Soms is het handiger om dan gewoon niet MySQL te laten sorteren, maar jouw eigen code.
Is dit geen optie, lees dan dit eens goed door: http://s.petrunia.net/blog/?p=24

Daarnaast kan je de "Using temporary" sneller maken door de tmp folder die wordt gebruikt te plaatsen op een SSD of in RAM.

Gebruik je PHP doe dan eens:

code:

mysqli_report(MYSQLI_REPORT_ALL);

[ Voor 5% gewijzigd door DJMaze op 30-10-2015 00:09 . Reden: mysqli_report() toegevoegd ]

[emnich]

Verwijderd schreef op donderdag 29 oktober 2015 @ 20:43:
Dank voor de reacties!

Ik heb dit voor 2 accounts getest.
- het eerste account heeft 1 feed, 40k text_id's en 10k copy_id's
- het tweede account heeft 5 feeds, 70k text_id's en 150k copy_id's

Voor het eerste account duurde de query nog maar 1.5 sec.
Voor het tweede account duurde de query nog steeds 40+ sec.

Ook die eerste lijkt me nog veel te lang.

Enkele algemene tips:
1. Er is niet één magische methode waardoor alles snel gaat, je zal dus per query en per geval moeten kijken waarom iets langzaam is.

2. Bekijk altijd de EXPLAIN, dus verander de query, bekijk EXPLAIN, verander query, etc.

3. Kijk naar de grootte van je velden. Je flag_delete is waarschijnlijk een tiny int NULL. Waarom is de flag_delete, 0 of 1 of NULL? Al je andere _id velden lijken INT te zijn terwijl je misschien ook uit komt met een small INT. Hoe kleiner je de velden maakt (en zeker de velden in een index) hoe meer van die index er in het geheugen past.

4. Je gebruikt nu SELECT * maar heb je werkelijk alle velden van alle tabellen nodig?

5. Hierboven wordt door meerdere mensen geopperd om de WHERE te verplaatsen naar de JOIN maar ik zou dat gewoon in de WHERE laten staan. MySQL is slim genoeg om daar het beste uit te halen en het houdt het imo overzichtelijker.

6. Je hoeft niet altijd alles in één query te stoppen. Soms helpt het om het op te delen in meerdere query's

DJMaze schreef op vrijdag 30 oktober 2015 @ 00:01:

Daarnaast kan je de "Using temporary" sneller maken door de tmp folder die wordt gebruikt te plaatsen op een SSD of in RAM.

Using temporary geeft alleen aan dat er een tijdelijke tabel gemaakt wordt, niet dat deze ook op een disk gemaakt wordt. Het kan dus prima zijn dat dit gewoon in memory gedaan wordt. Verder ben ik het met je eens maar je moet proberen om dat soort tweaks pas toe te passen als de basis goed is. Een langzame query sneller maken door je hardware te verbeteren zou pas een laatste optie moeten zijn. Ik kan me haast niet voorstellen dat beide query's niet binnen 1s kunnen draaien.

[DJMaze]

emnich schreef op vrijdag 30 oktober 2015 @ 08:33:
Using temporary geeft alleen aan dat er een tijdelijke tabel gemaakt wordt, niet dat deze ook op een disk gemaakt wordt. Het kan dus prima zijn dat dit gewoon in memory gedaan wordt. Verder ben ik het met je eens maar je moet proberen om dat soort tweaks pas toe te passen als de basis goed is. Een langzame query sneller maken door je hardware te verbeteren zou pas een laatste optie moeten zijn. Ik kan me haast niet voorstellen dat beide query's niet binnen 1s kunnen draaien.

Je hebt gelijk dat dit een allerlaatste redmiddel is.
De 1.5s vs 40s geeft wel aan dat het daar ergens goed mis zit mede door de select * (die jij al aankaart).

Zodra je weet welke kolommen vs * kan je inderdaad prima opsplitsen

[incaz]

Ik weet trouwens niet of de SELECT * nog uitmaakt, maar bij zoveel velden is dat in elk geval flink wat extra geheugen. En als je bepaalde gegevens niet eens nodig hebt, dan kun je misschien voor sommige tabellen zelfs de hele tablelookup overslaan, omdat de informatie uit de index dan genoeg is.

[emnich]

incaz schreef op vrijdag 30 oktober 2015 @ 09:34:
Ik weet trouwens niet of de SELECT * nog uitmaakt, maar bij zoveel velden is dat in elk geval flink wat extra geheugen. En als je bepaalde gegevens niet eens nodig hebt, dan kun je misschien voor sommige tabellen zelfs de hele tablelookup overslaan, omdat de informatie uit de index dan genoeg is.

De SELECT * kan zeker wel uitmaken als er TEXT of BLOB velden bij zitten, dan wordt er standaard een temporary table gebruikt.

Makkelijkste is natuurlijk om even te testen door alleen copy_id op te halen....

[DJMaze]

Oeh ik las hier overheen

emnich schreef op vrijdag 30 oktober 2015 @ 08:33:
5. Hierboven wordt door meerdere mensen geopperd om de WHERE te verplaatsen naar de JOIN maar ik zou dat gewoon in de WHERE laten staan. MySQL is slim genoeg om daar het beste uit te halen en het houdt het imo overzichtelijker.

Er is wel degelijk een verschil tussen WHERE en JOIN ... ON, maar dit is afhankelijk van het feit of het een INNER JOIN of LEFT JOIN is.

Bij een LEFT JOIN .. ON worden alle velden van die tabel NULL als er geen match is.
Plaats je bij een LEFT JOIN het ON gedeelte in de WHERE dan is het eigenlijk een INNER JOIN.
Het resultaat is dan ook veschillend.

Neem dan even het voorbeeld van:

code:

copy.copy_flag_delete IS NULL

Als copy_flag_delete NULL kan zijn, hoe weet je dan bij een LEFT JOIN of het een match is met de waarde NULL of geen match (en dan dus ook NULL)

Daarom is ook altijd mijn motto: CREATE TABLE name (field NOT NULL)
Oftewel, een veld is altijd NOT NULL of je moet een hele goede reden waarom hij NULL mag zijn

[ Voor 8% gewijzigd door DJMaze op 04-11-2015 12:24 ]

[emnich]

DJMaze schreef op woensdag 04 november 2015 @ 12:21:
Oeh ik las hier overheen

[...]


Er is wel degelijk een verschil tussen WHERE en JOIN ... ON, maar dit is afhankelijk van het feit of het een INNER JOIN of LEFT JOIN is.

Bij een LEFT JOIN .. ON worden alle velden van die tabel NULL als er geen match is.
Plaats je bij een LEFT JOIN het ON gedeelte in de WHERE dan is het eigenlijk een INNER JOIN.
Het resultaat is dan ook veschillend.

Het ging hier over performance en ik stel alleen dat het qua performance niet uit maakt of je de WHERE naar de join verplaatst of aan het eind laat staan (bij een identiek resultaat).

Neem dan even het voorbeeld van:

code:

copy.copy_flag_delete IS NULL

Als copy_flag_delete NULL kan zijn, hoe weet je dan bij een LEFT JOIN of het een match is met de waarde NULL of geen match (en dan dus ook NULL)

Daarom is ook altijd mijn motto: CREATE TABLE name (field NOT NULL)
Oftewel, een veld is altijd NOT NULL of je moet een hele goede reden waarom hij NULL mag zijn

Ik ben het met je eens dat je vrijwel nooit NULL nodig hebt maar als je op dat soort dingen wilt controleren kan je dat veel beter op de primary key doen (copy_id) dan op een willekeurig veld.

[GlowMouse]

Zal ik het antwoord dan maar geven? Het enige wat je voor de copy-tabel nodig hebt is één index op de velden copy_tracking_date_found en copy_id. Daarnaast wijzig je de GROUP BY en ORDER BY zodat ze deze index kunnen gebruiken:

code:

GROUP BY  `copy`.`copy_tracking_date_found` DESC, `copy`.`copy_id` DESC
ORDER BY  `copy`.`copy_tracking_date_found` DESC

[Verwijderd]

Dank voor in de input!

Ik heb er tijdens een welverdiende vakantie even afstand van kunnen nemen en ben nu weer met goede moed bezig.

De query heb ik ondertussen gewijzigd.

Ik had zowiezo nooit de SELECT * maar ik dacht voor het overzicht naar jullie even sneller te zijn.

code:

SELECT `copy`.*, `domain`.`domain_hostname`, `domain`.`domain_info_rating`, `domain`.`domain_info_category`, 
`domain`.`domain_info_pageviews`, `domain`.`domain_location_longitude`, `domain`.`domain_location_latitude`, `domain`.`domain_address_city`, 
`domain`.`domain_address_country`, `text`.`text_info_edition`, `text`.`text_title`, 
`text`.`text_info_category`, `text`.`text_info_url`, `text`.`text_feed_id`, `text`.`text_author`, `channel`.`channel_id`, `channel`.`channel_info_rating`, 
`feed`.`feed_type`, `feed`.`feed_subtype`, `feed`.`feed_info_title`, `feed`.`feed_id`, `domaincategory`.`domaincategory_category`, `textdetails`.`textdetails_content`, `textdetails`.`textdetails_abstract`  FROM `feed` 
INNER JOIN `text` ON text.text_feed_id = feed.feed_id 
AND text.text_flag_delete IS NULL 
INNER JOIN `copy` ON copy.copy_text_id = text.text_id 
AND copy.copy_flag_delete IS NULL 
AND copy.copy_tracking_date_found IS NOT NULL
INNER JOIN `domain` ON domain.domain_id = copy.copy_domain_id 
INNER JOIN `channel` ON channel.channel_domain_id = domain.domain_id AND channel.channel_account_id = copy.copy_account_id AND channel.channel_active = 1 
LEFT JOIN `domaincategory` ON copy.copy_domain_id = domaincategory.domaincategory_domain_id AND domaincategory.domaincategory_account_id = copy.copy_account_id 
LEFT JOIN `textdetails` ON textdetails.textdetails_text_id = text.text_id 
WHERE (feed.feed_account_id = 162) 
ORDER BY `copy`.`copy_tracking_date_found` DESC 
LIMIT 50

Met EXPLAIN krijg ik het volgende resultaat:

id	select_type	table	type	possible_keys	key	key_len	ref
1	SIMPLE	feed	ref	PRIMARY,i_feed_ids_delete	i_feed_ids_delete	4	const	9	Using temporary; Using filesort
1	SIMPLE	text	ref	PRIMARY,i_text_id_flag_delete	i_text_id_flag_delete	5	feed.feed_id	2792	Using where
1	SIMPLE	copy	ref	fk_copy_account_id,fk_copy_domain_id,fk_copy_text_...	fk_copy_text_id	4	text.text_id	2	Using where
1	SIMPLE	channel	ref	fk_channel_account_id,i_channel_ids_active	i_channel_ids_active	10	copy.copy_domain_id,	1	Using where
1	SIMPLE	domaincategory	ref	fk_domaincategory_account_id,i_domaincategory_ids	i_domaincategory_ids	8	copy.copy_domain_id,	1
1	SIMPLE	domain	eq_ref	PRIMARY,i_domain_id	PRIMARY	4	copy.copy_domain_id	1
1	SIMPLE	textdetails	ref	i_textdetails_text_id	i_textdetails_text_id	5	copy.copy_text_id	1

Zonder de ORDER BY gaat ie vlot.
Met de ORDER BY gaat blijft het toch traag ondanks de index op 'copy_tracking_date_found'.

Heeft iemand nog een advies?

[incaz]

Je loopt hier denk ik zo langzamerhand wel tegen de grenzen op van wat eenvoudig kan. Als ik het goed heb, heb je namelijk een aantal hele grote tabellen die je moet joinen (feed, text, copy) en pas na de laatste join met copy en het ophalen van alle rijen (en dat lijken er nogal veel te zijn) kan de db pas gaan ordenen (en dus bepalen wat de eerste 50 zijn.) Hoe groot is de dataset zonder limit?

Maar er zijn wellicht wel een paar dingen die je nog kunt proberen:

Die index op text_id_flag_delete lijkt me niet efficient genoeg en kan wellicht combined flag_delete en feed_id worden. (Tegelijkertijd - dit lost het order by-probleem niet op voor zover ik kan inschatten.)

De copy-tabel lijkt de belangrijkste tabel te zijn, (gezien je alle velden ophaalt en erop ordent.) Ik zou dus eens proberen of mysql de zaken anders organiseert als je die tabel centraal stelt. Dus SELECT ... FROM copy INNER JOIN text, feed ipv andersom. Voor het uiteindelijke resultaat maakt dat niet uit, voor de weg ernaartoe wellicht wel. Misschien dat MySQL dan anders optimaliseert.

Dan heb je daar volgens mij in elk geval al een samengestelde index nodig van text_id en tracking_date_found.

Ook kun je overwegen om wat denormalisatie toe te passen en de feed_id en wellicht account_id op te slaan in je copy-tabel. (En ook opnemen in je samengestelde index.)

Maar vooral zou ik gewoon eens experimenteren omdat eea best specifiek is voor hoe je data is opgebouwd. Kun je tabellen weglaten uit de query, en die eventueel apart ophalen? Bepaalde stappen makkelijk overlaten aan je code? Op een of andere manier caching toepassen? Wanneer is een query nog goed te doen en wanneer niet? Wat heb je nou eigenlijk precies nodig? Kun je je tracking_date_found wellicht op voorhand al beperken?

[Hydra]

Freubelen met indices als die het probleem van de sort in een tijdelijke tabel niet oplossen gaat je niet helpen. Hij doet een sort op een tijdelijke tabel en die is zo groot dat het niet puur in geheugen gedaan wordt. Je enige opties zijn dan de resultset veel kleiner maken, het doen zonder sort, of gewoon 't resultaat prefetchen + cachen zodat de user er geen last van heeft.

[emnich]

Hydra schreef op donderdag 26 november 2015 @ 15:22:
Freubelen met indices als die het probleem van de sort in een tijdelijke tabel niet oplossen gaat je niet helpen. Hij doet een sort op een tijdelijke tabel en die is zo groot dat het niet puur in geheugen gedaan wordt. Je enige opties zijn dan de resultset veel kleiner maken, het doen zonder sort, of gewoon 't resultaat prefetchen + cachen zodat de user er geen last van heeft.

Natuurlijk gaat een goede index wel helpen. Als je er voor kan zorgen dat de index gebruikt wordt voor het sorteren dan ben je al van het grootste probleem af.

Daarnaast zegt using temporary helemaal niets over of het in het geheugen past of niet, alleen dat er een tijdelijke tabel wordt gebruikt.

[Hydra]

emnich schreef op donderdag 26 november 2015 @ 16:13:
Natuurlijk gaat een goede index wel helpen. Als je er voor kan zorgen dat de index gebruikt wordt voor het sorteren dan ben je al van het grootste probleem af.

Ik zei letterlijk dat een index die die sort niet oplost geen effect gaat hebben!

Daarnaast zegt using temporary helemaal niets over of het in het geheugen past of niet, alleen dat er een tijdelijke tabel wordt gebruikt.

En ook dat is niet wat ik zei.

[emnich]

Hydra schreef op vrijdag 27 november 2015 @ 09:45:
[...]


Ik zei letterlijk dat een index die die sort niet oplost geen effect gaat hebben!

Misschien is het handig dan om uit te leggen wat je wel bedoeld. Zeggen dat iets doen wat het niet oplost niet gaat helpen is een zinloze reactie want dat snappen we allemaal wel.

[...]


En ook dat is niet wat ik zei.

Ook hierbij is het dan wellicht handig om uit te leggen waar ik je niet goed snap.
Je zegt

Hij doet een sort op een tijdelijke tabel en die is zo groot dat het niet puur in geheugen gedaan wordt.

. Waar haal je vandaan dat het niet in het geheugen gedaan kan worden?

[incaz]

Hydra schreef op donderdag 26 november 2015 @ 15:22:
Freubelen met indices als die het probleem van de sort in een tijdelijke tabel niet oplossen gaat je niet helpen. Hij doet een sort op een tijdelijke tabel en die is zo groot dat het niet puur in geheugen gedaan wordt. Je enige opties zijn dan de resultset veel kleiner maken, het doen zonder sort, of gewoon 't resultaat prefetchen + cachen zodat de user er geen last van heeft.

Als je op een of andere manier kunt zorgen dat de sort al plaats vindt voor het in de temptable komt en die temptabel dus niet alle records hoeft te bevatten maar alleen de eerste 50 (of desnoods de eerste 100 en dan gefilterd worden) kan het natuurlijk wel degelijk heel veel uitmaken.

Query optimizers zijn redelijk slim, maar het zijn lastige opgaven en soms is een verkeerde volgorde van afhandelen gewoon een groot probleem. Soms kan het het verschil zijn tussen 1 record meteen teruggeven, of een temptable bouwen van een paarhonderdduizend records, dat vervolgens te sorteren, en dan pas dat ene record te vinden en de overige honderdduizenden weer weg te gooien. En dat hangt niet slechts van de structuur af, maar vooral ook van de data zelf, en dat weet de db pas als die data is opgehaald. Manual tuning richting een andere optimalisatie kan dus zeker uitmaken, zonder dat het inhoudt dat je je sort moet overslaan of je recordset verkleinen.

Er is alleen geen kant en klaar altijd-juist antwoord voor (want dan hadden db-bouwers het zelf wel in hun optimisers gestopt.)