login  Naam:   Wachtwoord: 
Registreer je!
 Forum

MySQL query optimalisatie

Offline Wijnand - 03/03/2014 11:34
Avatar van WijnandModerator Ik heb een query die er zo uit ziet:

  1. SELECT
  2. verzamel.*
  3. FROM
  4. (SELECT
  5. a.*,
  6. b.naam,
  7. b.latitude,
  8. b.longitude
  9. FROM
  10. (
  11. SELECT
  12. c.*
  13. FROM
  14. (
  15. SELECT
  16. i.*,
  17. @rn := CASE WHEN @prev = i.item_id THEN @rn + 1 ELSE 1 END AS num,
  18. @prev := i.item_id
  19. FROM
  20. items AS i,
  21. (
  22. SELECT
  23. @prev:=0, @rn:=0
  24. ) AS d
  25. WHERE i.active=1 AND ( i.titel LIKE '%test%' OR i.tekst LIKE '%test%')
  26. ORDER BY i.item_id, id DESC
  27. ) AS c
  28. WHERE num <= 4
  29. ) AS a
  30. INNER JOIN
  31. gebruiker AS b ON (b.item_id = a.id)
  32. WHERE
  33. FLOOR(GetDistance(52.2900810,5.6260238, b.latitude, b.longitude )*1.606344) <= "25"
  34. GROUP BY
  35. a.id,b.id
  36. ) AS verzamel
  37. GROUP BY
  38. verzamel.id
  39. ORDER BY
  40. GetDistanceMeters(52.2900810,5.6260238, verzamel.latitude, verzamel.longitude), verzamel.id DESC
  41. LIMIT 0,30


Wat deze code doet:
haal van tabel items alle items op die de tekst 'test' in titel of tekst 'test' hebben, maar maximaal 4 per gebruiker. En laat dan alle gebruikers zien met maximaal 4 kortingen en een limiet van 30 items (en daarna moet hij stoppen en niets meer laten zien).

Vanwege dat limiet van 4 kortingen die voldoen aan de zoekresultaten, krijg je een nogal ingewikkelde en trage query en ik zou deze willen verbeteren.

Heeft iemand hier ideeën over?

Ik heb de bovenstaande query even gewijzigd naar normale text en een boel joins eruit gehaald, dus het kan zijn dat ik ergens een typ-foutje heb gemaakt. Kijk daar maar even overheen. De query die ik hier heb is nog wat groter en is een query die noodzakelijk is voor een redelijk grote website en die query wil ik natuurlijk niet op SiMa gaan plaatsen (niet leuk voor de klant en ook niet leuk voor jullie, aangezien het nogal ingewikkeld is en uitgebreid is met een x aantal inner/left joins).

4 antwoorden

Gesponsorde links
Offline Thomas - 03/03/2014 17:24
Avatar van Thomas Moderator Ik neem aan dat je al hebt geprobeerd de efficiëntie te meten met EXPLAIN?
Vervolgens zou je een of meer INDEXen kunnen aanbrengen op de kolommen waar je mee werkt?

Misschien vergezocht, maar wellicht zou je de LATs en LONs op kunnen slaan als BIGINT of als TEXT, indexen op reals werken volgens mij niet zo best (maar kan mij vergissen).

Daarnaast zie ik veel SELECT tabel.*, heb je echt alle data nodig van die tabel(len)? Welke data gebruik je uiteindelijk?

En dan is er natuurlijk nog een alternatieve oplossing: splits het probleem op in deelproblemen (die mogelijk individueel efficiënt(er) op te lossen zijn). Je bent niet verplicht alles in één query op te lossen hoor. Ook zou je kunnen overwegen om (deel)berekeningen over te hevelen naar PHP - je bent niet verplicht deze allemaal uit te voeren in MySQL zelf.

Dan is er nog het ophalen/afhandelen van de resultaten, mogelijk kun je hier nog dingen optimaliseren?

En tot slot, wellicht kun je de uitkomst van bepaalde berekeningen/queries cachen in aparte tabellen, zodat berekeningen maar 1x veel tijd kosten.

Mogelijk maakt het ook uit om voor dit alles stored procedures te maken?
Offline Wijnand - 10/03/2014 16:36
Avatar van Wijnand Moderator Ik zag dat ik nog niet gereageerd had.

Ik heb explain gebruikt inderdaad en de query op zich is wel goed qua snelheid, alleen omdat ik die rare subquery moet doen met nog een subquery wordt hij een stuk langzamer. Dat stuk zou ik anders willen kunnen doen, ik weet niet of je daar een idee voor hebt?

Overigens, als ik het in 2 queryies probeer op te lossen, dan kost het nog extra laadtijd.

Cachen is overigens niet mogelijk, omdat je vanaf elke gewenste locatie de resultaten moet kunnen zien, zelfs terwijl je loopt (het is de code voor onder andere een APP). Dus dat valt in ieder geval af .

Ik ben er zelf ook nog over aan het nadenken, misschien dat ik de klant kan overtuigen van een andere denkwijze, zodat de query zonder die diepere subquery kan.
Offline Thomas - 10/03/2014 17:08 (laatste wijziging 11/03/2014 14:21)
Avatar van Thomas Moderator EDIT #2: Mogelijke inspiratie

@caching
regel 11 t/m 29 zie ik alleen een afhankelijkheid op zoekterm, niet op geolocatie, dus dat is een mapping zoekterm -> itemdata+
In hoeverre verschillen zoektermen, of wellicht kun je deze beperken?

Ik ben niet echt vertrouwd met de constructie "... (SELECT ...) AS x ...". Houdt dit in dat de resultaten als een soort van verzameling worden beschouwd en worden daarbij dan nog indexen en andere tabeloptimalisaties van de binnenste query gebruikt als je elementen van set x gebruikt in vergelijkingen? Heb je al geprobeerd deze hele constructie eruit te halen?

Heb je geprobeerd regel 33 te verplaatsen naar een "HAVING" conditie?

Waarom staat trouwens "25" tussen quotes? FLOOR() retourneert een integer.
EDIT: dit gaat toevallig goed, maar let hier heel goed op!
22.347 < 3 levert false, maar "22.347" < "3" levert true!

Mogelijk worden bepaalde berekeningen (met name GetDistance) nogal vaak uitgevoerd, heb je geprobeerd deze in een SELECT of in de JOIN te zetten in plaats van in je WHERE-conditie.

Dit zou je EXPLAIN al moeten laten zien, maar mogelijk ontbreken bepaalde condities bij je JOINs, dan krijgt je performance meteen een deuk. Hoeveel records bevatten de tabellen, hoe groot zijn je (tussen)resultaten. Mogelijk kun je daar ook winst pakken. Ik denk overigens nog steeds dat het opdelen in meerdere queries kan helpen, want nu heb je nog steeds nogal veel "dimensies" door je subqueries.
Offline rredspike1 - 15/03/2014 14:32
Avatar van rredspike1 Lid Man man man....sorry dat ik even offtopic reageer maar "mannen", waar zijn we nu helemaal mee bezig, oftewel... ik heb respect!! Ik begrijp er een klein beetje van, maar dit gaat mijn petje toch nog echt iets te boven..

Ik wou dat ik van jullie training kon krijgen!!
Gesponsorde links
Je moet ingelogd zijn om een reactie te kunnen posten.
Actieve forumberichten
© 2002-2024 Sitemasters.be - Regels - Laadtijd: 0.163s