[MySQL] Query explain explained

[Verwijderd]

Ik heb 4 tabellen aan elkaar gehaakt met de onderstaande query. Te zien aan de explain is dat het met de indexes niet helemaal goed zit. Het is mij echter niet duidelijk waarom het mis gaat.


De query (dynamisch opgebouwd dmv keuzes van de gebruiker)

SQL:

SELECT
unspsc.unspsc_omschrijving as cat, 
unspsc.unspsc_omschrijving_nl as oms2, 
merken.image as img, 
products.artikelsoort, 
products.id, 
products.artikel, 
products.oem, 
products.omschrijving_nl as prodoms, 
debiteur_artikelen.debiteurartikelnummer, 
merken.omschrijving as merk 
FROM 
products 

Left Outer Join debiteur_artikelen ON 
(products.artikel=debiteur_artikelen.artikelnummer AND 
debiteur_artikelen.debiteur=012083)

Inner Join merken ON merken.merk = products.merk

Inner Join unspsc ON products.unspsc = unspsc.unspsc 

WHERE unspsc.unspsc>0 
AND 1=1 
AND ((
products.omschrijving_nl LIKE '%laser%' OR 
products.omschrijving_en LIKE '%laser%' OR 
unspsc.unspsc_omschrijving LIKE '%laser%' OR
merken.omschrijving LIKE '%laser%' OR 
products.oem LIKE '%laser%' OR 
products.artikel LIKE '%laser%' OR 
debiteur_artikelen.debiteurartikelnummer LIKE '%laser%')) 

ORDER BY 
merk,
unspsc.unspsc_omschrijving,
products.artikelsoort

En als overzichtelijk schema


Dan kom ik bij de explain functie en hier draait het allemaal om.
Ik begrijp niet waarom de roodomlijnde gegevens zijn zoals ze zijn. de indexes zouden toch moeten kloppen, zie hieronder



De tabellen:

unspsc (productgroepen)

code:

Field   Type    Allow Null  Default Value
id  int(11)     No  
unspsc  int(8)  Yes     
unspsc_omschrijving     varchar(500)    Yes     
unspsc_omschrijving_nl  varchar(500)    Yes     
unspsc_omschrijving_en  varchar(500)    Yes     
unspsc_omschrijving_de  varchar(500)    Yes     
unspsc_omschrijving_fr  varchar(500)    Yes

products

code:

Field   Type    Allow Null  Default Value
id  int(11)     No  
artikelsoort    smallint(6)     Yes     
artikel     varchar(10)     Yes     
oem     varchar(25)     Yes     
omschrijving_NL     varchar(500)    Yes     
omschrijving_DE     varchar(500)    Yes     
omschrijving_FR     varchar(500)    Yes     
omschrijving_EN     varchar(500)    Yes     
unspsc  int(8)  Yes     
merk    varchar(3)  Yes     
groep   int(11)     Yes     
status  varchar(1)  Yes

merken

code:

Field   Type    Allow Null  Default Value
id  int(11)     No  
merk    varchar(3)  Yes     
omschrijving    varchar(500)    Yes     
image   varchar(100)    No

en tot slot
debiteur_artikelen (koppeling van klantspecifieke artikelnummers aan artikelen uit products)

code:

Field   Type    Allow Null  Default Value
debiteur    varchar(6)  No  
debiteurartikelnummer   varchar(25)     No  
artikelnummer   varchar(10)     No  
dummy   text    Yes

[ Voor 5% gewijzigd door Verwijderd op 06-02-2009 12:17 ]

[P_de_B]

Een LIKE'%zoekwaarde%' kan nooit gebruik maken van indexen. Zonder er verder te diep op in te gaan, denk ik dat dat je probleem is

[MBV]

Daar heb je toch fulltext-indexes voor? Zelf nooit gebruik van hoeven maken, gelukkig.

[RobIII]

MBV schreef op woensdag 04 februari 2009 @ 15:03:
Daar heb je toch fulltext-indexes voor? Zelf nooit gebruik van hoeven maken, gelukkig.

Da's leuk, maar dan moet je ook fulltext functies gebruiken en dus geen LIKE

[ Voor 11% gewijzigd door RobIII op 04-02-2009 15:17 ]

[Verwijderd]

Ik heb wat gestoeid met de fulltext mogelijkheid, maar nadeel is dat niet gezocht kan worden met wildcards (hetgeen een vereiste is), dus ik zit toch echt vast aan de LIKE methode. Ik vraag me wel af of mijn bovenstaande query nog wat getuned kan worden.

[P_de_B]

Weet je zeker dat je niet kunt zoeken met wildcards bij fulltextindexing? Ik ken de MySQL fulltextindexing niet heel goed, maar ik kan me niet voorstellen dat het niet kan. Ik denk dat je even beter moet zoeken.

De like met een wildcard voor de zoektekst (zoals LIKE '%zoek') kan niet geoptimaliseerd worden. Je zult begrijpen dat daarbij nooit van een index gebruik gemaakt kan worden. Vergelijk het maar met de inhoudsopgave van een boek. Als je de eerste letter niet weet heeft het geen zin in de index van woorden te kijken.

[LuCarD]

P_de_B schreef op vrijdag 06 februari 2009 @ 09:43:
Weet je zeker dat je niet kunt zoeken met wildcards bij fulltextindexing? Ik ken de MySQL fulltextindexing niet heel goed, maar ik kan me niet voorstellen dat het niet kan. Ik denk dat je even beter moet zoeken.

De like met een wildcard voor de zoektekst (zoals LIKE '%zoek') kan niet geoptimaliseerd worden. Je zult begrijpen dat daarbij nooit van een index gebruik gemaakt kan worden. Vergelijk het maar met de inhoudsopgave van een boek. Als je de eerste letter niet weet heeft het geen zin in de index van woorden te kijken.

Fulltext heeft wel wildcards. Maar je kan niet zoeken op deelwoorden die eindigen op iets.

Dus LIKE '%zoek' kan niet. LIKE 'zoek%' is wel te doen.

[edit]
Als ik de handleiding tenminste goed snap
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

[ Voor 6% gewijzigd door LuCarD op 06-02-2009 09:57 ]

[Verwijderd]

Als ik zoek op print dan wil ik ook printer, laserprinter en laserprinterpapier vinden, ik krijg dit niet voor elkaar met een MATCH. Googlen levert me tot nu toe ook nog niks op. Ik kom niet verder dan dat met een match alleen op hele woorden gezocht kan worden.

code:

select * from products where match(omschrijving_nl) 
against ('printer IN BOOLEAN MODE');

geeft resultaat

code:

select * from products where match(omschrijving_nl) 
against ('print IN BOOLEAN MODE');

geeft geen resultaat

code:

select * from products where match(omschrijving_nl) 
against ('print* IN BOOLEAN MODE');

ook niet.

[curry684]

Verwijderd schreef op vrijdag 06 februari 2009 @ 10:02:
Als ik zoek op print dan wil ik ook printer, laserprinter en laserprinterpapier vinden, ik krijg dit niet voor elkaar met een MATCH.

Je vraagt hiermee ook iets aan functionaliteit wat niet in een database zit of thuishoort - dit soort zeer geavanceerde zoekfunctionaliteit vind je alleen terug in Google search appliances (vanaf ongeveer 2000 euro per 1u unit) of dedicated C++ search engines als Xapian.

Je wil namelijk "pietje is een goeie sprinter" niet vinden en zo.

[ Voor 7% gewijzigd door curry684 op 06-02-2009 10:10 ]

[daniëlpunt]

offtopic:
Je PM staat niet aan, dus vraag ik het maar zo:
Welk programma is dat op die screenshots?

[Verwijderd]

Dat programma is Navicat (en dat kun je kopen bij mij ) (ben reseller daarvan)

[Verwijderd]

Ik heb wel truukje bedacht waarmee het toch kan, al vraag ik me af of dan nog zinvol is MATCH ipv LIKE te gebruiken....
Namelijk zoeken in de omgekeerde tekst op een omgekeerd zoekcriterium

code:

select products.*,reverse(omschrijving_nl) as oms_nl_rev
from products where
match(omschrijving_nl) against ('inter*'  IN BOOLEAN MODE) OR
match(oms_nl_rev) against ('retni*'  IN BOOLEAN MODE)

Nu is het jammere dat oms_nl_rev niet bekend is in de query.... daar moet ook nog wel wat op te verzinnen zijn.

hiermee kun je dan ook laserprinter, print, printerpaper etc. allemaal vinden

[MBV]

En wat wordt je performance dan met insert-opdrachten? Dan moet je dus kolommen met elk een fulltext-index gaan vullen, zal niet echt snel zijn.

[P_de_B]

LuCarD schreef op vrijdag 06 februari 2009 @ 09:56:
[...]


Fulltext heeft wel wildcards. Maar je kan niet zoeken op deelwoorden die eindigen op iets.

Dus LIKE '%zoek' kan niet. LIKE 'zoek%' is wel te doen.

[edit]
Als ik de handleiding tenminste goed snap
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Zit er dan geen CONTAINS in?

[Noork]

De in de topicstart vermeldde query werkt dus wel? Is de performance daarvan niet goed? Anders zou ik het lekker zo laten.

P_de_B schreef op vrijdag 06 februari 2009 @ 11:13:
[...]

Zit er dan geen CONTAINS in?

Is dat geen MsSql?

[ Voor 41% gewijzigd door Noork op 06-02-2009 11:20 ]

[Remus]

curry684 schreef op vrijdag 06 februari 2009 @ 10:09:
[...]

Je vraagt hiermee ook iets aan functionaliteit wat niet in een database zit of thuishoort - dit soort zeer geavanceerde zoekfunctionaliteit vind je alleen terug in Google search appliances (vanaf ongeveer 2000 euro per 1u unit) of dedicated C++ search engines als Xapian.

Je wil namelijk "pietje is een goeie sprinter" niet vinden en zo.

Lucene is ook een goede keuze voor een losse search engine (http://lucene.apache.org/)

[Verwijderd]

MBV schreef op vrijdag 06 februari 2009 @ 11:03:
En wat wordt je performance dan met insert-opdrachten? Dan moet je dus kolommen met elk een fulltext-index gaan vullen, zal niet echt snel zijn.

Ik hoef maar weinig te inserten/deleten, hoofdzakelijk zoeken, dus het valt wel mee denk ik.

[Noork]

Remus schreef op vrijdag 06 februari 2009 @ 11:24:
[...]


Lucene is ook een goede keuze voor een losse search engine (http://lucene.apache.org/)

Xapian (van de Got search) is ook prima. Maar voor een productendatabase/webshop lijkt me dat wat overkill. Sowieso moet je dat installeren op de server, wat ook niet altijd gaat.

[Verwijderd]

Ik heb diverse query's getest en hieruit blijkt dat het gebruik van MATCH en LIKE elkaar nauwelijk ontlopen qua performance. Ik heb nog wel een flink performance probleem met de LEFT OUTER JOIN in mijn query (topicstart) Ik vraag me af hoe ik dit zou kunnen verbeteren.
Ik wil namelijk zoeken in artikel,oem en omschrijvingen, maar daarnaast ook in een tabel met klantspecifieke artikelnummers. Dit is echter een vrij grote tabel (200k+ records), waarvan per klant(debiteur) er maar een paar records van toepassing zijn (0-50). De producttabel bevat ongeveer 15.000 records. Op welke manier zou ik de query kunnen verbeteren?

[ Voor 3% gewijzigd door Verwijderd op 06-02-2009 11:38 ]

[GlowMouse]

Verwijderd schreef op vrijdag 06 februari 2009 @ 11:37:
Ik heb diverse query's getest en hieruit blijkt dat het gebruik van MATCH en LIKE elkaar nauwelijk ontlopen qua performance. Ik heb nog wel een flink performance probleem met de LEFT OUTER JOIN in mijn query (topicstart) Ik vraag me af hoe ik dit zou kunnen verbeteren.

Een index op (debiteur,artikelnummer) zou wel iets helpen, maar verwacht nog steeds geen wonderen.

En met een index op products.artikelsoort kun je de sortering uit de index halen, dat zal ook iets schelen.

Ik krijg het idee dat je niet goed weet hoe indices werken. Bedenk eens goed hoe een index kan helpen bij een JOIN (bij de eerste tabel in je EXPLAIN output helpt dat niets), en waarom je indices hebt die op meerdere kolommen tegelijk zijn gedefinieerd (en waarom de volgorde van belang is).

[ Voor 29% gewijzigd door GlowMouse op 06-02-2009 11:49 ]

[Verwijderd]

GlowMouse schreef op vrijdag 06 februari 2009 @ 11:43:
[...]

Een index op (debiteur,artikelnummer) zou wel iets helpen, maar verwacht nog steeds geen wonderen.

En met een index op products.artikelsoort kun je de sortering uit de index halen, dat zal ook iets schelen.

Ik krijg het idee dat je niet goed weet hoe indices werken. Bedenk eens goed hoe een index kan helpen bij een JOIN (bij de eerste tabel in je EXPLAIN output helpt dat niets), en waarom je indices hebt die op meerdere kolommen tegelijk zijn gedefinieerd (en waarom de volgorde van belang is).

Klopt, indexes zijn me niet helemaal duidelijk. Ik kan wel wat met je opmerkingen, ga er mee aan de slag

[GlowMouse]

Verwijderd schreef op vrijdag 06 februari 2009 @ 11:58:
[...]


Klopt, indexes zijn me niet helemaal duidelijk. Ik kan wel wat met je opmerkingen, ga er mee aan de slag

Dat sorteren kun je toch vergeten met die index, ik zag niet dat je ORDER BY uit drie velden bestond. Omdat die velden niet allemaal in dezelfde tabel staan, kun je geen index gebruiken voor de sortering.

Je kunt bij het plaatsen van je vraag beter wat meer moeite steken in de opmaak van je query. Zo is hij toch veel leesbaarder (en dan heb ik het niet alleen over de kleuren)?

SQL:

SELECT unspsc.unspsc_omschrijving as cat, unspsc.unspsc_omschrijving_nl as oms2, merken.image as img, products.artikelsoort, products.id, products.artikel, products.oem, products.omschrijving_nl as prodoms, debiteur_artikelen.debiteurartikelnummer, merken.omschrijving as merk
FROM products 
LEFT OUTER JOIN debiteur_artikelen ON (products.artikel=debiteur_artikelen.artikelnummer AND debiteur_artikelen.debiteur=012083)
INNER JOIN merken ON merken.merk = products.merk
INNER JOIN unspsc ON products.unspsc = unspsc.unspsc 
WHERE unspsc.unspsc>0 AND 1=1 AND ((
  products.omschrijving_nl LIKE '%laser%' OR 
  products.omschrijving_en LIKE '%laser%' OR 
  unspsc.unspsc_omschrijving LIKE '%laser%' OR
  merken.omschrijving LIKE '%laser%' OR 
  products.oem LIKE '%laser%' OR 
  products.artikel LIKE '%laser%' OR 
  debiteur_artikelen.debiteurartikelnummer LIKE '%laser%')) 
ORDER BY merk,unspsc.unspsc_omschrijving,products.artikelsoort