[MySQL] Optimalisatie van query met sortering

[.oisyn]

Ik heb een relatief simpele tabel met miljoenen rijen. Voor mijn probleem zijn in feite 2 kolommen relevant: een integer foreign key categoryId en een integer timestamp. De timestamps zijn gewoon UNIX timestamps en lopen altijd op naarmate rijen worden toegevoegd aan de tabel.

De data die ik wil opvragen is: de laatste 500 rijen met categoriën die voorkomen in een lijst van meerdere categoryId's. De SQL query is dus alsvolgt:

SQL:

SELECT * FROM tabel
WHERE categoryId IN (/* lijst met N categoryId's */)
ORDER BY timestamp DESC
LIMIT 500

Verder heb ik gewoon een index op (categoryId ASC, timestamp ASC), en ter indicatie, zonder LIMIT zouden iets van 10% van de resultaten uit de tabel (dus in de orde van grootte van 100.000en rijen) geretourneerd worden.

Het probleem zit 'm in die ORDER BY. Als ik die weghaal is de query gewoon vlot, zoals je zult verwachten. Ook bij het ophalen van de laatste X rijen van een specifieke categoryId is gewoon snel. De index doet bij deze twee usecases dus gewoon waarvoor het gemaakt is. Maar bij het ophalen van meerdere categoryId's kan hij de sortering zelf logischerwijs niet meer uit de index halen en past hij dus wat MySQL een "filesort" noemt toe op het resultaat, met als gevolg dat de query tot tientallen seconden kan duren. Idealiter wil ik dat ie er maar max 2 seconden over doet.

Nou heb ik best wel wat kennis van de achterliggende algoritmiek, maar niet zo heel veel van relationele databases . Ik vind het niet heel vreemd dat domweg het ophalen van alle records en dan vervolgens de sortering toepassen een trage operatie is. Het is echter wel een beetje een domme implementatie. Feitelijk zou hij van elke categoryId snel de laatste 500 rijen goed gesorteerd uit de index kunnen halen en die dan vervolgens gaan sorteren met een merge sort tot je uiteindelijk het gewenste aantal hebt bereikt. Ik zou het in code ook zo op kunnen lossen, maar ergens is het jammer dat ik dan nog wel 500*N resultaten op moet halen.

Ik vraag me dan ook af of het niet op een of andere manier gewoon in de database opgelost kan worden.

[OnTracK]

Wat doet mysql bij N=1 en N=2 waarbij je wel de IN-syntax gebruikt? En waarbij je een OR syntax gebruikt? Ik neem aan dat dat wel bijna hetzelfde moet zijn. Maar misschien is er een omslagpunt waarbij N>2. Dat is in ieder geval goed om te weten. Ik weet alleen dat PostgreSQL wat intelligenter probeert met statistiek te voorspellen wanneer het goed is een andere tactiek te proberen.

Verder is je ORDER BY in omgekeerde richting als je index. Voor zover ik weet kan je helemaal geen richting in indexes aangeven in MySQL. Heb je al eens gekeken of het omkeren hiervan helpt? In je query dan dus, en dan in je code nog een keer omkeren.

Verder zeg je: "Ik zou het in code ook zo op kunnen lossen". Ik weet niet of je hiermee bedoelt "echt in code", of in MySQL-code. Want dat kan natuurlijk ook, en zou er ongeveer als volgt uitzien.

SQL:

SELECT * FROM
(
    SELECT * from tabel 
    WHERE categoryId=N1
    LIMIT 500
) UNION ALL (
    SELECT * from tabel 
    WHERE categoryId=N2
    LIMIT 500
) UNION ALL (
    etc...

)
ORDER BY timestamp DESC
LIMIT 500

[ Voor 3% gewijzigd door OnTracK op 23-12-2013 13:18 ]

[The Eagle]

Waarom maak je niet een 2e identieke index maar dan descending?
Dan wordt die descending index ineens gebruikt ten faveure van de huidige index en kan de sort/order ook sneller verlopen. Dikke kans dat het dan retesnel is

[ValHallASW]

Je index is op (categoryId ASC, timestamp ASC) -- dat wil dus zeggen dat je alleen snel kunt sorteren op timestamp als je maar één categoryId hebt; anders moet je namelijk twee (of meer...) gesorteerde lijsten toevoegen. En ja, dat is traag als 'ie dat met een filesort moet doen.

De meeste simpele oplossing is volgens mij gewoon een extra index (op timestamp) toevoegen.

Maar om nog even een stap terug te doen: wat zegt een EXPLAIN op je query? Dan weet je precies of je index wel/niet wordt gebruikt, en hoe.

[ Voor 27% gewijzigd door ValHallASW op 23-12-2013 13:21 ]

[storeman]

Met ValHallASW, dat was ook mijn eerste ingeving.

[Voutloos]

The Eagle schreef op maandag 23 december 2013 @ 13:19:
Waarom maak je niet een 2e identieke index maar dan descending?
Dan wordt die descending index ineens gebruikt ten faveure van de huidige index en kan de sort/order ook sneller verlopen. Dikke kans dat het dan retesnel is

Zal niet helpen, het probleem zit hem in de range op de 1e kolom, niet in de richting van de 2e.

Dus inderdaad een nieuwe index met timestamp als 1e kolom, en mogelijk categoryId als 2e. Alternatief is een subquery om enkel op tijd te sorteren en dan in de outer query pas te filteren, maar dan heb je weer misschien een trage query als je eens een set categorieën hebt met relatief weinig rows...

[.oisyn]

OnTracK schreef op maandag 23 december 2013 @ 13:17:
Wat doet mysql bij N=1 en N=2 waarbij je wel de IN-syntax gebruikt? En waarbij je een OR syntax gebruikt? Ik neem aan dat dat wel bijna hetzelfde moet zijn. Maar misschien is er een omslagpunt waarbij N>2. Dat is in ieder geval goed om te weten.

OR of IN maakt zoals verwacht niets uit. Uiteraard maakt het wel uit als N=1, want dan kan ie gewoon direct de index gebruiken. Vanaf N=2 wordt het een filesort.

Verder is je ORDER BY in omgekeerde richting als je index.

Dat maakt niets uit, met hetzelfde gemak kan hij een index in omgekeerde richting doorlopen.

Verder zeg je: "Ik zou het in code ook zo op kunnen lossen". Ik weet niet of je hiermee bedoelt "echt in code", of in MySQL-code. Want dat kan natuurlijk ook, en zou er ongeveer als volgt uitzien.

SQL:

SELECT * FROM
(
    SELECT * from tabel 
    WHERE categoryId=N1
    LIMIT 500
) UNION ALL (
    SELECT * from tabel 
    WHERE categoryId=N2
    LIMIT 500
) UNION ALL (
    etc...

)
ORDER BY timestamp DESC
LIMIT 500

Hmm dat is evt. een optie ja, al zal ie dan denk ik nog steeds niet voor de efficientere merge sort kiezen. Ik wist eerlijk gezegd niet dat je LIMIT kon gebruiken in de subqueries van een UNION (aangezien je ORDER BY ook niet kan gebruiken). Op deze manier is het overigens wel van belang om de index van timestamp om te draaien.
.edit: oh wacht je doet een UNION van subqueries, niet gewoon een UNION van queries. Hmmm dat biedt op zich perpsectieven voor een andere ongerelateerde usecase die ik heb

The Eagle schreef op maandag 23 december 2013 @ 13:19:
Waarom maak je niet een 2e identieke index maar dan descending?

Ik kan net zo goed de eerste vervangen, maar zoals gezegd, kun je eens in detail proberen uit te leggen waarom jij denkt dat dat uitmaakt? Een index van (A, B) wil zeggen dat alle rijen gegroepeerd worden op A, en binnen alle rijen met dezelfde A worden ze nog eens gegroepeerd op B. Als je verschillende A's opvraagt kunnen alle B's dus niet automagisch ineens gesorteerd zijn in de totale resultlijst.

ValHallASW schreef op maandag 23 december 2013 @ 13:20:
De meeste simpele oplossing is volgens mij gewoon een extra index (op timestamp) toevoegen.

Dat is helemaal geen oplossing. Het is niet dat omdat er een index van timestamp bestaat hij daar ook gebruik van kan maken als hij al van een andere index gebruik moet maken om de categoriën op te zoeken. Met een index op timestamp kan hij snel een op timestamp gesorteerd resultaat teruggeven, maar als je binnen dat resultaat dan vervolgens moet zoeken op categoriën dan wordt het gewoon feitelijk een tablescan. De query is dan alleen snel als de gevraagde categoriën veel gebruikt worden (want dan kan ie stoppen zodra hij er 500 heeft gevonden). Zijn er minder dan 500 in de hele tabel dan is het dus een complete scan van de table.

Een index op (timestamp, categoryId) is ook compleet nutteloos gezien het feit dat timestamps redelijk uniek zijn. Per timestamp zal hij dus in de regel maar 1 categoryId kunnen opslaan.

Ik heb overigens een index op timestamp, daar maakt ie (heel logisch) geen gebruik van bij deze query. Kan ie ook niet.

@Voutloos: zie boven.

[Sircuri]

Volgens mij moet je IN altijd vervangen door een WHERE EXISTS clausule. Die zijn over algemeen sneller.

[.oisyn]

Er is geen enkel semantisch verschil tussen de twee en een goede optimizer behandelt ze dan ook als gelijk.
.edit: sterker nog, ik denk dat het in dit geval gewoon trager is, want in plaats van een constante lijst die voor de hele query gelijk blijft heb je een subquery die per regel uitgevoerd moet worden. Ook zie ik niet helemaal in hoe je die subquery dan moet maken zonder daarbinnen weer een WHERE categoryId IN (...) te gebruiken

[ Voor 61% gewijzigd door .oisyn op 23-12-2013 15:16 ]

[ValHallASW]

.oisyn schreef op maandag 23 december 2013 @ 15:08:
Dat is helemaal geen oplossing. Het is niet dat omdat er een index van timestamp bestaat hij daar ook gebruik van kan maken als hij al van een andere index gebruik moet maken om de categoriën op te zoeken. Met een index op timestamp kan hij snel een op timestamp gesorteerd resultaat teruggeven, maar als je binnen dat resultaat dan vervolgens moet zoeken op categoriën dan wordt het gewoon feitelijk een tablescan.

Shit. Ja, je hebt gelijk.

Wat is precies de output van je EXPLAIN?

Verder ken je Use the index, Luke vast al, maar voor de zekerheid:
http://use-the-index-luke...grouping/indexed-order-by
en
http://use-the-index-luke...er-by-asc-desc-nulls-last

[Douweegbertje]

Wat even handig is, zoals hier boven staat: EXPLAIN SELECT. Dan worden veel dingen duidelijk, echter is het probleem al duidelijk zichtbaar

code:

SELECT * FROM tabel 
WHERE categoryId IN (/* lijst met N categoryId's */) 
ORDER BY timestamp DESC 
LIMIT 500

Je doet een WHERE op categoryId (wat een index is) terwijl de ORDER BY op een andere index is, waardoor je dus in de problemen komt. Domweg een ORDER BY op categoryId zou alles al oplossen (mocht dat het zelfde resultaat geven?)

Dus.. wat misschien de makkelijkste oplossing is, is om een view te maken. Geen idee of dit nu ook echt sneller gaat worden maar dan zou je wel een ORDER BY kunnen uitvoeren op je view wat dan technisch gezien een betere index heeft. Het is niet de optimale situatie maar je kunt nou eenmaal geen ORDER BY doen met verschillende keys.


Overigens misschien een hele dirty fix, maar ik heb dit niet getest is iets zoals dit:

code:

SELECT * FROM tabel 
WHERE categoryId IN (/* lijst met N categoryId's */) 
AND timestamp IS NOT NULL
ORDER BY timestamp DESC, categoryId
//eventueel even zonder de order by categoryId proberen nog 
LIMIT 500

[ Voor 16% gewijzigd door Douweegbertje op 23-12-2013 20:16 ]

[.oisyn]

Zoals je wel in de topicstart kunt lezen vind ik het niet vreemd dát het slecht performt, ik wil alleen weten wat ik kan doen om het wél goed te laten performen. De UNION oplossing van OnTracK lijkt me nog de meest zinnige, anders dan iets vergelijkbaars in programmacode te doen.

Wat betreft je suggestie, timestamp is sowieso een not null column, en wat zou die extra sortering toevoegen als de buitenste sortering al een probleem oplevert?

[Douweegbertje]

.oisyn schreef op maandag 23 december 2013 @ 21:04:
Zoals je wel in de topicstart kunt lezen vind ik het niet vreemd dát het slecht performt, ik wil alleen weten wat ik kan doen om het wél goed te laten performen. De UNION oplossing van OnTracK lijkt me nog de meest zinnige, anders dan iets vergelijkbaars in programmacode te doen.

Wat betreft je suggestie, timestamp is sowieso een not null column, en wat zou die extra sortering toevoegen als de buitenste sortering al een probleem oplevert?

Omdat zoals ik het lees tenminste(sql faq), SQL op zijn muil gaat op het moment dat er 'iets' word gedaan met een ID, en vervolgens weer niet. Nu kan ik dit zo 123 niet even checken dus daarom had ik het even zo neergezet dat je het misschien zelf kon testen. Wat ik dus bedoel is:

edit; werkt dus niet

Het enige wat ik dan nog een beetje kan bedenken naast die JOIN is een temp table als in;

code:

CREATE TEMPORARY TABLE IF NOT EXISTS temp AS (SELECT * FROM tabel 
WHERE categoryId IN (/* lijst met N categoryId's */) 
LIMIT 500
)

Om vervolgens daarop dan je order te doen. Eventueel dat je nog een ID moet omdraaien. Je moet alleen nog even checken wat de performance is, aangezien even een temp table aanmaken ook niet super snel is.. echter wel met veel informatie.

[ Voor 24% gewijzigd door Douweegbertje op 23-12-2013 22:30 ]

[BHR]

Volgens mij heeft het te maken met de DESC in order by, en dat mysql indexes ascending opslaat.

[ Voor 245% gewijzigd door BHR op 23-12-2013 22:43 . Reden: toch een poging ]

[glrfndl]

SQL:

SELECT * FROM
(
    SELECT * from tabel 
    WHERE categoryId=N1
    LIMIT 500
) UNION ALL (
    SELECT * from tabel 
    WHERE categoryId=N2
    LIMIT 500
) UNION ALL (
    etc...

)
ORDER BY timestamp DESC
LIMIT 500

Dit garandeert toch nooit dat je de laatste 500 entries krijgt? Kent mysql ook ranking functies? Daarmee zou ik het geloof ik in TSQL doen, in combinatie met een common table expression.

[hommer]

Los van de beste aanpak qua optimalisatie kun je mogelijk ook nog winst halen in de sort. Ik weet niet hoe groot de result set is, maar heb je enig idee of MySQL de sort in memory doet of via temp file?
Als MySQL een tmp file gebruikt is de sort vreselijk traag, een memory sort valt vaak nog wel mee. Je kunt hier eventueel je MySQL config tunen met de sort_buffer_size, zorg wel dat de heap_size even groot is.
Om snel te testen of het uitmaakt kun je bij een linux MySQL installatie ook de tmp dir even naar /dev/shm wijzen.

[Gomez12]

Douweegbertje schreef op maandag 23 december 2013 @ 19:40:
Dus.. wat misschien de makkelijkste oplossing is, is om een view te maken. Geen idee of dit nu ook echt sneller gaat worden maar dan zou je wel een ORDER BY kunnen uitvoeren op je view wat dan technisch gezien een betere index heeft. Het is niet de optimale situatie maar je kunt nou eenmaal geen ORDER BY doen met verschillende keys.

Views met indexen zijn door de meeste db-engines losgelaten (of moeilijk bereikbaar gemaakt) omdat het over het algemeen meer tijd kost om de indexen up to date te houden dan het oplevert.

En alhoewel ik zo snel even geen idee heb of mysql het ondersteunt toch nog even een methode die ik nog weleens toepas op andere dbases. Zet een index op : date(timestamp) desc - Categoryid asc - timestamp desc
Dan krijg je een index die wel beter gevuld is mits je eerst ordered op date(timestamp)

Of alternatief is : gewoon ruim schatten wanneer je minimaal 500 entries hebt is dit bijv binnen een dag dan een extra where conditie toevoegen die zorgt dat hij enkel maar de gegevens van de laatste dag pakt voordat hij gaat order by'en.

[Voutloos]

glrfndl schreef op maandag 23 december 2013 @ 22:11:
Dit garandeert toch nooit dat je de laatste 500 entries krijgt?

Klopt, bij de UNION aanpak moet je ook in de subqueries de volgorde expliciet maken.

BHR schreef op maandag 23 december 2013 @ 22:10:
Volgens mij heeft het te maken met de DESC in order by, en dat mysql indexes ascending opslaat.

Het komt door de RANGE op de 1e kolom.

Om een populair voorbeeld te geven: Een telefoonboek, met per dorp de achternamen met nummers. Voor 1 dorp is het triviaal om alle gegevens op volgorde van achternaam op te noemen. Moet je dat echter voor een paar dorpen doen, wordt t al gauw een rottaak.
Het boek is in ASC volgorde geprint, maar je kan je wellicht inbeelden dat het weinig significant is als je bovenstaande in descending order moet doen.

Overigens klopt het wel dat mysql indexen in 1 volgorde opslaat, maar dat merk je alleen als je in een order by wisselt tussen ASC en DESC. Is wellicht trivia tot je een keer zo'n query spot.

[ Voor 5% gewijzigd door Voutloos op 23-12-2013 22:51 ]

[pedorus]

.oisyn schreef op maandag 23 december 2013 @ 15:08:
Een index op (timestamp, categoryId) is ook compleet nutteloos gezien het feit dat timestamps redelijk uniek zijn. Per timestamp zal hij dus in de regel maar 1 categoryId kunnen opslaan.

Dit is een ernstige denkfout. Met een index op (timestamp, categoryId) kun je aan de hand van de index zien welke rijen je terug moet geven als database, zonder die rijen zelf te bekijken. Je hoeft dan dus niet steeds de rijen zelf af, die in principe verspreid in het geheugen of zelfs op disk kunnen staan. Aangezien je in de TS zegt zo'n 10% terug te verwachten, zou dit prima moeten werken. Voor queries waarbij je dan wel zo'n beetje de complete index af zou moeten gaan scannen, zou het kunnen dat mysql zo slim is om de (categoryId, timestamp) index te gebruiken. Nu weet ik dit laatste van mysql niet zeker.

[Voutloos]

Jep, dat laatste doet mysql prima, als op basis van cardinaliteit die beslissing gemaakt kan worden.

[.oisyn]

pedorus schreef op dinsdag 24 december 2013 @ 00:59:
[...]

Dit is een ernstige denkfout.

Nou zo ver zou ik ook niet willen gaan. Het blijft hoe dan ook een O(n) operatie. Alleen is de constante factor per n inderdaad lager . Wellicht dat het nu te doen is, maar die n blijft in de loop der tijd alleen maar toenemen. Maar goed punt inderdaad, ik zal eens kijken of dat wat uithaalt.

Voor queries waarbij je dan wel zo'n beetje de complete index af zou moeten gaan scannen, zou het kunnen dat mysql zo slim is om de (categoryId, timestamp) index te gebruiken. Nu weet ik dit laatste van mysql niet zeker.

Op basis waarvan zou hij een dergelijke beslissing moeten maken dan?

[pedorus]

.oisyn schreef op dinsdag 24 december 2013 @ 10:34:
Nou zo ver zou ik ook niet willen gaan.

offtopic:
Ik chargeerde een beetje op basis van mijn toestand en de gebruiker waartegen ik het had

Op basis waarvan zou hij een dergelijke beslissing moeten maken dan?

Zie http://dev.mysql.com/doc/...tatistics-estimation.html (uitgegaan van InnoDB)

Dus het werkt waarschijnlijk goed als de categorieën ongeveer even vaak voorkomen, en de statistieken up to date zijn. Het werkt niet goed als je een aantal categorieën tegelijkertijd op vraagt die allen zeer weinig tot niet voorkomen. In dat geval, als je dat van te voren weer, zou je een hint over de juiste index kunnen geven.

[OnTracK]

.oisyn schreef op maandag 23 december 2013 @ 15:08:

[...]

Dat maakt niets uit, met hetzelfde gemak kan hij een index in omgekeerde richting doorlopen.

Ik zat bij PostgreSQL waarbij je bij meerdere clauses in je ORDER BY geen gebruik kan maken van je index als je eerst in dezelfde richting, en daarna in tegengestelde richting van je index order't, of omgekeerd. Maar dat is hier inderdaad niet van toepassing.

glrfndl schreef op maandag 23 december 2013 @ 22:11:

SQL:

SELECT * FROM
(
    SELECT * from tabel 
    WHERE categoryId=N1
    LIMIT 500
) UNION ALL (
    SELECT * from tabel 
    WHERE categoryId=N2
    LIMIT 500
) UNION ALL (
    etc...

)
ORDER BY timestamp DESC
LIMIT 500

Dit garandeert toch nooit dat je de laatste 500 entries krijgt? Kent mysql ook ranking functies? Daarmee zou ik het geloof ik in TSQL doen, in combinatie met een common table expression.

Nu ik daar over nadenk heb je gelijk. Ik had hem wat snel opgeschreven.

[ Voor 29% gewijzigd door OnTracK op 24-12-2013 11:29 ]

[Janoz]

.oisyn schreef op maandag 23 december 2013 @ 13:10:
...Ik zou het in code ook zo op kunnen lossen, maar ergens is het jammer dat ik dan nog wel 500*N resultaten op moet halen.

Je hoeft niet alle resultaten op te halen natuurlijk. Afhankelijk van je platform zou je je resultset ook via een cursos uit kunnen lezen en dan zelf de mergesort toepassen. Als je aan de 500 zit lees je je resultsets gewoon niet meer verder uit. Dit is natuurlijk wel geheel afhankelijk van hoeveel cursors je naast elkaar kunt hebben en hoe groot N kan worden.

[.oisyn]

Afhankelijk van je platform zou je je resultset ook via een cursos uit kunnen lezen en dan zelf de mergesort toepassen.

MySQL zoals in de titel staat. Computer says "no"

[ACM]

.oisyn schreef op maandag 23 december 2013 @ 15:08:
Hmm dat is evt. een optie ja, al zal ie dan denk ik nog steeds niet voor de efficientere merge sort kiezen. Ik wist eerlijk gezegd niet dat je LIMIT kon gebruiken in de subqueries van een UNION (aangezien je ORDER BY ook niet kan gebruiken). Op deze manier is het overigens wel van belang om de index van timestamp om te draaien.

Je kan zowel LIMIT als ORDER BY gebruiken als je domweg een UNION van queries doet. Als je die queries als subquery ergens anders in stopt gaat MySQL ineens klagen

Het voorstel van OnTrack geeft je niet dezelfde resultaten. Maar je kan wel gewoon een pure union doen en het resultaat daarvan sorteren:

SQL:

(
SELECT ... FROM ... = N1 ORDER BY timestamp LIMIT 500
UNION ALL
SELECT ... FROM ... = N2 ORDER BY timestamp LIMIT 500
UNION ALL
...
)
ORDER BY timestamp LIMIT 500

Dan zijn er geen subqueries meer. Het kan zijn dat de haakjes zo niet helemaal goed staan maar er moet dus geen "SELECT * FROM" voor.

De query is dan alleen snel als de gevraagde categoriën veel gebruikt worden (want dan kan ie stoppen zodra hij er 500 heeft gevonden). Zijn er minder dan 500 in de hele tabel dan is het dus een complete scan van de table.

Dus is aan jou de vraag of dat het geval is

Als je vooraf weet dat e.e.a. in de eerste paar procent lukt, dan kan het prima zijn om een index op timestamp te gebruiken en de rest te filteren. Als er in totaal maximaal 499 resultaten zijn, dan ga je in de worst case inderdaad de hele tabel moeten scannen.

[ Voor 3% gewijzigd door ACM op 24-12-2013 12:53 ]

[.oisyn]

ACM schreef op dinsdag 24 december 2013 @ 12:52:
Je kan zowel LIMIT als ORDER BY gebruiken als je domweg een UNION van queries doet.

Ah ik zie het ja. Het was SQLite die dat niet kan

Dus is aan jou de vraag of dat het geval is

Ik zei toch juist dat dat het geval was

Maar voor de volledigheid, ik had eerst een index alleen op timestamp. Queries met relatief veelvoorkomende categoriën waren gewoon snel, maar een query met weinig voorkomende categoriën waren retetraag. Toen de index op categorie toegevoegd, en toen was het gedrag precies andersom (waardoor ik me eerlijk gezegd wel afvraag of die cardinaliteitsinschatting dan wel goed verloopt)

[ Voor 11% gewijzigd door .oisyn op 24-12-2013 13:01 ]

[ACM]

.oisyn schreef op dinsdag 24 december 2013 @ 12:56:
Ah ik zie het ja. Het was SQLite die dat niet kan

Nou... MySQL kan het ook niet altijd hoor

In sommige typen subqueries kan het wel en dan ineens bij een kleine variant erop niet meer. Maar met die variant van mij is er uberhaupt geen subquery, dus dan heb je sowieso geen last van die beperkingen voor het gebruik van ORDER en LIMIT bij de modernere MySQL's.

Maar voor de volledigheid, ik had eerst een index alleen op timestamp. Queries met relatief veelvoorkomende categoriën waren gewoon snel, maar een query met weinig voorkomende categoriën waren retetraag. Toen de index op categorie toegevoegd, en toen was het gedrag precies andersom (waardoor ik me eerlijk gezegd wel afvraag of die cardinaliteitsinschatting dan wel goed verloopt)

Als beide scenario's voorkomen wordt het lastiger om de "perfecte" index te krijgen

En het is inderdaad zonde dat MySQL blijkbaar die sortering van deelresultaten niet kan hergebruiken bij de index op (categoryId, timestamp). Je kan die union's proberen. De best case is wat slechter dan select+index (aka hij haalt maximaal 500*M op ipv 500), maar de worst case is een stuk beter (aka, hij haalt nog steeds 500*M op ipv N).

Hoewel dat vast weer nieuwe problemen introduceert zodra het lijstje categorien lang wordt. Ik heb daarbij geen idee wat 'lang' is, wellicht dat dat pas in de tientallen problematisch kan worden... Maar met tientallen kan je wellicht beter weer gewoon je originele query met enkel de index op timestamp nemen.

[ Voor 13% gewijzigd door ACM op 24-12-2013 13:06 ]

[.oisyn]

Voor de volledigheid, dat lijstje is gebaseerd op een

SQL:

SELECT categoryId
FROM categories
WHERE description LIKE '%somesubstring%'

Kan dus veel zijn ("a") of weinig ("Hele Specifieke Categorie"), al ben ik geneigd om minder dan 3 tekens sowieso niet te ondersteunen

[ACM]

.oisyn schreef op dinsdag 24 december 2013 @ 15:28:
Kan dus veel zijn ("a") of weinig ("Hele Specifieke Categorie"), al ben ik geneigd om minder dan 3 tekens sowieso niet te ondersteunen

Je zou 't kunnen testen met (bijna) alle categorieen. Test het met 'a' of 'e'.

Als MySQL dan stikt in de union-variant kan je een punt opzoeken waar het gevoelsmatig goed gaat en schakelen tussen twee queries. Als MySQL ook met een groot aantal categorieen goed genoeg werkt... dan zou ik de query die in de meeste gevallen goed werkt gebruiken

Ik zou zelf niet zo gauw de mogelijkheden voor zoektermen inperken. In Tweakers' zoekmachine zijn dit bijvoorbeeld gebruikelijke woorden: pc, hp, ms, wd. Maar als je er echt last van hebt, dan kan het natuurlijk een nuttige keuze zijn.

[.oisyn]

Minder dan 3 tekens is in deze context ook niet zo relevant. We hebben het sowieso over een systeem dat überhaupt maar door 2 mensen gebruikt wordt (mijzelf en iemand anders), en ik kan best met die beperking leven . Ik ben het ermee eens dat zoiets op een grote site als tweakers een enorm nadeel is.

[ Voor 30% gewijzigd door .oisyn op 24-12-2013 16:08 ]

[P.O. Box]

misschien heb ik het gemist, maar het vullen van een temporary table met eerste 500 resultaten per categoryId is geen optie (zoals voorgesteld door http://gathering.tweakers.net/forum/view_message/41474122)? Op die temp-table kun je dan ook nog een index toevoegen om daarna alles te sorteren?

[.oisyn]

Want jij denkt dat het creëren van een index van data sneller is dan het sorteren van diezelfde data?