[mySQL] verschil in snelheid query

[ikvanwinsum]

Ik ben bezig een applicatie te bouwen die ook op grotere datasets efficiënt draait. Daarom kijk ik ook naar de snelheid van SQL-queries om te bepalen wat er geoptimaliseerd kan worden. Om het een en ander te testen heb ik een tabel met dummydata met 10100 records.

Zo doet deze query er ~69ms over:

SQL:

SELECT *
FROM `activities`
INNER JOIN `group_relations` on `activities`.`id` = `model_id`
WHERE `model_type` = 'activities' and `relation_type` = 'visible' and `group_id` in ('1', '21')
ORDER BY `id` DESC
LIMIT 101

Dat terwijl onderstaande query er wel 844ms over doet:

SQL:

SELECT *
FROM `activities`
WHERE exists (
  SELECT *
  FROM `group_relations`
  WHERE `activities`.`id` = `group_relations`.`model_id` and `group_relations`.`model_type` = 'activities' and `group_id` in ('1', '21')
)
ORDER BY `id` DESC
LIMIT 101

Weten jullie waar dit verschil vandaan komt? In feite ben ik bij de tweede query niet meer data aan het ophalen. (toch?) Dat terwijl op internet lijkt alsof exists sneller is.

[ Voor 1% gewijzigd door RobIII op 03-04-2019 18:00 . Reden: Queries leesbaar gemaakt ]

[dev10]

Heb je al gekeken wat het verschil in execution plan als je 'EXPLAIN' zet voor je queries? Dat is de beste manier om te verklaren waarom de ene query sneller is dan de andere.

[RobIII]

ikvanwinsum schreef op woensdag 3 april 2019 @ 16:25:
Weten jullie waar dit verschil vandaan komt?

Nee, daar is in de verste verte niets zinnigs over te zeggen zonder te weten wat je aan indexen hebt (if any) en hoe je tabellen er uit zien. En dan spelen er mogelijk nog meer factoren mee.
Zoals @dev10 al zegt: kijk eens naar de EXPLAIN (of EXPLAIN EXTENDED) van je query.

[ Voor 13% gewijzigd door RobIII op 03-04-2019 17:42 ]

[ikvanwinsum]

Die EXPLAIN en EXPLAIN EXTENDED kende ik nog niet. Thanks daarvoor!

Ik had een index op group_id zitten, maar nog niet op model_id. Stom. Nu ik die heb toegevoegd is de snelheid inderdaad weer normaal. Dat had ik moeten weten

[RobIII]

Sowieso zijn, over het algemeen, dat soort 'optimalisaties' onzin. Schrijf gewoon een leesbare query en pas als je tegen problemen aanloopt (en je hebt alles uit je indexen gehaald en gekeken naar andere mogelijke orozaken) kun je eens kijken of het 'creatief herschrijven van een query' nut heeft. Een beetje query planner maakt zelf heus wel een fatsoenlijk executionplan. En ja, daar zijn uitzonderingen op (en zeker bij MySQL ) maar over het algemeen moet je gewoon de query schrijven zoals je 'm bedoelt en je niet in bochten (moeten) wringen om een query snel(ler) te krijgen. Het probleem ligt 99 v.d. 100 keer in indexen, storage, memory, caches, dat soort zaken.

[ikvanwinsum]

Ja, dat blijkt. Ik gebruik ook liever gewoon de Eloquent relaties (ik gebruik hier Laravel). Ik heb Clockwork geinstalleerd, wat ook helpt om n+1 problemen te detecteren. Daar viel het me op dat die query zo onmogelijk lang duurde, en op de 1 of andere manier had ik nog niet aan mn indexen gedacht.

[reddevil]

Best is gewoon nadat je een query schrijft even een explain plan te doen om te kijken of de indexen worden gebruikt waarvan je ze verwacht... Of als je van te voren weet dat tabellen zeer groot kunnen worden, dummy data er in gooien en echt testen.
Zeker bij queries: op een gegeven moment worden die (zonder optimalisering) ineens langzaam door joins die niet meer in memory kunnen etc.

[ Voor 46% gewijzigd door reddevil op 04-04-2019 12:15 ]

[RobIII]

reddevil schreef op donderdag 4 april 2019 @ 12:14:
[sarcasme]Prachtig uitgangspunt... beste manier van programmeren en iets maken... op hoop van zegen? [/sarcasme]

[sarcasme]Volgens mij heb je mijn posts héél goed gelezen en begrepen[/sarcasme]

[ Voor 45% gewijzigd door RobIII op 04-04-2019 12:39 ]

[reddevil]

RobIII schreef op donderdag 4 april 2019 @ 12:15:
[...]

[sarcasme]Volgens mij heb je mijn posts héél goed gelezen en begrepen[/sarcasme]

Hah, duurde ff... te veel meetings, te weinig koffie... had meteen ook aangepast . Waar ik ook meer op doelde is dat het eigenlijk altijd zinvol is de executionplan te checken... zeker bij tabellen die groot kunnen worden. Regelmatig meegemaakt dat executionplans niet optimaal zijn en je zelfs indexen moet forceren.