[mysqll] results limiteren en pagen met left joins

[js303]

hoi ik kom er niet uit: ik heb een query die een selectie van Objects toont, met daaraan gekoppeld per Objects-record een reeks files. het aantal files per object kan van 0 tot oneindig - zeg 100 - oplopen. van de files wil ik de eerste 10 tonen. Ik wil per pagina max 100 Objects tonen.

dus:
tabel Objects
tabel Files - 1 op veel relatie met Objects

probleem: ik kan limit niet gebruiken om het aantal objecten te beperken, bijv. geef mij alle objecten van 101 t/m 200, want de limit werkt op rows en per row kan ik meerdere keren hetzelfde object maar telkens een nieuwe bijbehorende file krijgen.

vanwege mysql 3.23 waar ik mee werk, kan ik geen group_concat gebruiken om de eerste 10 files te bundelen als 1 string zodat ik netjes per row een object terugkrijg.

hoe pak ik dit in godsnaam - netjes - aan? moet ik dit clientside - lees: in php - oplossen, waardoor ik een huge recordset terugkrijg en vervolgens in php dan maar moet gaan tellen ("he een nieuw object: objcount++... he objcount > 100: break!") 't lijkt me performance-wise zeer onverstandig gewoon maar alles op te vragen en dan clientside te gaan bepalen van waar tot waar ik records uit de set toon...

als laatste kom ik er ook niet uit om in m'n query het aantal gekoppelde files te limiteren, stel er hangen 100 files aan een object, dan krijg ik middels die leuke left join ook 100 rows terug, terwijl ik maar de eerste 4 nodig heb....

na uren rondzoeken op got en mysql.com kom ik er nog niet uit! ben ik dan echt noodgedwongen naar mysql 4.x te upgraden??

[ Voor 7% gewijzigd door js303 op 09-02-2005 23:18 ]

[Verwijderd]

Met 1 query lukt dat ook niet. Wanneer je eerst je objects selecteer met een limit, dan een query maakt met alle files die daar onder hangen, dan moet je er wel komen denk ik.

[js303]

Verwijderd schreef op woensdag 09 februari 2005 @ 23:23:
Met 1 query lukt dat ook niet. Wanneer je eerst je objects selecteer met een limit, dan een query maakt met alle files die daar onder hangen, dan moet je er wel komen denk ik.

ok dat klinkt eigenlijk wel het handigst. echter de 2e query, ga ik dan alle bijbehorende files selecteren "where objectid = 1 or objectid = 3 or objectid = 9 or" enzovoort? is dat wel snel?

[ Voor 9% gewijzigd door js303 op 10-02-2005 11:20 ]

[thomaske]

js303 schreef op donderdag 10 februari 2005 @ 11:17:

ok dat klinkt eigenlijk wel het handigst. echter de 2e query, ga ik dan alle bijbehorende files selecteren "where objectid = 1 or objectid = 3 or objectid = 9 or" enzovoort? is dat wel snel?

Jahoor, als je een index/pk op dat veld hebt, dan wel.
Makkelijker is dan trouwens om het zo te doen:

code:

.. WHERE objectid IN (1,3,9,..)

[T-MOB]

js303 schreef op donderdag 10 februari 2005 @ 11:17:
[...]
ok dat klinkt eigenlijk wel het handigst. echter de 2e query, ga ik dan alle bijbehorende files selecteren "where objectid = 1 or objectid = 3 or objectid = 9 or" enzovoort? is dat wel snel?

Dat is iets dat je zou moeten benchmarken denk ik. Je hebt volgens mij de keuze uit twee alternatieven:
• 1 query per object met een limit op 10. Dat worden dus 100 queries wat niet echt lekker klinkt qua performance.
• In 1 keer alle data op halen en met php limitten op 10. Zolang het aantal files per object niet al te groot is zal dit wel sneller zijn. Maar in de TS heb je het over 100 files per object. Dan haal je dus voor 90% nutteloze data op. Deze methode is absoluut niet schaalbaar, als het aantal files per object nog kan groeien dan groeit de traagheid van je applicatie gezellig mee.

Een klein testje op mijn eigen server:
Het ophalen van 10.000 records duurt 1,2s.
Het 100x ophalen van 10 records duurt 5,0s
Het omslagpunt zou volgens deze gegevens dus ongeveer liggen bij 420 files per object. Als je dat haalt zijn losse queries interessanter. Maar goed, je kunt het beter even testen op je eigen server.

[js303]

thomaske schreef op donderdag 10 februari 2005 @ 14:00:
[...]


Jahoor, als je een index/pk op dat veld hebt, dan wel.
Makkelijker is dan trouwens om het zo te doen:

code:

.. WHERE objectid IN (1,3,9,..)

ik heb eens gelezen dat mysql geen gebruikmaakt van indexes bij OR operators. uit de documentatie wordt ik niet helemaal wijs (http://dev.mysql.com/doc/mysql/en/mysql-indexes.html)...

[ Voor 13% gewijzigd door js303 op 10-02-2005 14:30 ]

[_js_]

Als ik het mij goed herinner gaat en key hier niet veel bij helpen. MySQL gaat altijd een tablescan doen. Heeft van doen met hoe MySQL met indices werkt (een paar maanden geleden voor het laatst getest op een MySQL 4 punt nog wat)

[js303]

ik heb het in 3 queries opgelost, moet veel clientside (php) oplossen, maar op zich gaat tot toe snel. heb bij een result van >1100 objecten met per object tussen de 1 - 10 files een totale parsetime van max 0.04 sec, dat is incl. php-code uitvoeren - op een pentium-M @ 1.6 GHz.

query 1: tel totaal aantal records
SELECT count(obj.id) FROM ws_objects obj WHERE enzovoorts

query 2: selecteer range van objecten
SELECT obj.id (etc) FROM ws_objects LIMIT $offset, $resperpage

query 3: selecteer alle bestanden kleiner of gelijk aan 10 kb en die bij object 1, 5, 10 etc horen
SELECT r.object_id 'id', f.filename, f.source_id FROM ws_files_rel r, ws_files f WHERE f.id = r.file_id AND f.filesize <= 10 AND (r.id = 1 or r.id = 5 or r.id = 10 etc)

ben benieuwd hoe dit zal gaan bij 10.000 of 100.000 results cq. records...

[ Voor 14% gewijzigd door js303 op 10-02-2005 19:36 ]