[SQL]Index op varchar

[van.der.schulting]

Ik kom het regelmatig tegen dat ik een index nodig heb op een varchar. Dat is natuurlijk veel trager dan een integer, dus een integer zou veel idealer zijn.
Om er een integer van te maken kan ik twee dingen doen:
1. De tabel normaliseren en de varchar verplaatsen naar een koppeltabel. Zo kom ik natuurlijk relatief simpel aan een integer, maar normaliseren is lang niet altijd handig.
2. In de tabel met integers werken en in PHP de integers toewijzen aan de benodigde waardes. Dan ben ik volledig afhankelijk van mijn bijbehorende PHP aspplicatie en dat vind ik helemaal verre van ideaal.

Wat ik nu doe is toch maar een varchar gebruiken als de veruit de meeste waardes hetzelfde zijn en ik probeer de lengte van het varchar-veld zo kort mogelijk te houden.

Er een integer van maken is mijns inziens geen ideale situatie.

Zijn er misschien nog andere ideeen / oplossingen die ik over het hoofd zie om een varchar veld snel te doorzoeken??

[whoami]

Euh, wat probeer je juist te doen ? Waarom zou je een *hack* gaan doen om toch maar een index op een integer veld te kunnen plaatsen, en zo je applicatie / datamodel verkrachten, terwijl de performance-winst toch waarschijnlijk maar marginaal zal zijn imho.

Hoe zoek je op dat varchar veld ?

[ Voor 6% gewijzigd door whoami op 23-03-2012 13:53 ]

[pedorus]

Ik snap eigenlijk niet wat het probleem is van een index op een varchar? Als iets daadwerkelijk een varchar is, dan is dit toch normaal gesproken een probleem voor de database-engine om op te lossen. Pas bij performance-problemen en meerdere gelijke waardes zou je dan voor zo'n workaround kunnen kiezen?

Of gaat het om bijvoorbeeld een tag-systeem, waarbij de waardes eigenlijk een aparte entiteit vormen?

[NMe]

Een varchar domweg verplaatsen naar een andere tabel is niet per definitie hetzelfde als normaliseren trouwens. In de derde normaalvorm bestaan je tabellen niet ineens uit alleen maar integers hoor.

[Cyphax]

van.der.schulting schreef op vrijdag 23 maart 2012 @ 13:37:
Ik kom het regelmatig tegen dat ik een index nodig heb op een varchar. Dat is natuurlijk veel trager dan een integer, dus een integer zou veel idealer zijn.

Wat bedoel je hier? Die index is ervoor om te zorgen dat je sneller kunt zoeken (zolang je niet teveel met like '%' aan de gang gaat), je kunt dat niet optimaliseren door te zeggen "dan zoek ik wel op een integer".

Om er een integer van te maken kan ik twee dingen doen:
1. De tabel normaliseren en de varchar verplaatsen naar een koppeltabel. Zo kom ik natuurlijk relatief simpel aan een integer, maar normaliseren is lang niet altijd handig.

Dat niet alleen: je haalt het nut van je index volledig onderuit.

2. In de tabel met integers werken en in PHP de integers toewijzen aan de benodigde waardes. Dan ben ik volledig afhankelijk van mijn bijbehorende PHP aspplicatie en dat vind ik helemaal verre van ideaal.

Je bedoelt alle voordelen van je database opzijzetten en zelf iets in elkaar gaan hacken?
Dat is inderdaad verre van ideaal. Wat idealer is, is de db gebruiken waar ie voor bedoeld is.

Zijn er misschien nog andere ideeen / oplossingen die ik over het hoofd zie om een varchar veld snel te doorzoeken??

Ja: zet er een index op.

Er zijn wel wat optimalisatiemogelijkheden trouwens, maar die zijn contextafhankelijk. Als je nou beschrijft wat je precies probeert te doen, dan kun je afhankelijk van de situatie optimaliseren.

NMe schreef op vrijdag 23 maart 2012 @ 15:14:
Een varchar domweg verplaatsen naar een andere tabel is niet per definitie hetzelfde als normaliseren trouwens. In de derde normaalvorm bestaan je tabellen niet ineens uit alleen maar integers hoor.

Als wat NMe hier zegt niet logisch klinkt, zorg dan even dat je goed in de gaten krijgt wat het normaliseren nou precies inhoudt en waarom je het wel of niet zou willen doen.

[ Voor 13% gewijzigd door Cyphax op 23-03-2012 15:18 ]

[Hydra]

van.der.schulting schreef op vrijdag 23 maart 2012 @ 13:37:
Ik kom het regelmatig tegen dat ik een index nodig heb op een varchar. Dat is natuurlijk veel trager dan een integer, dus een integer zou veel idealer zijn.

Waarop baseer je dat? Je maakt hier volgens mij een foute aanname waar je de rest van je verhaal op baseert.

Of een extra integer key of een varchar natural key sneller is, is voornamelijk afhankelijk van het specifieke probleem dat je op probeert te lossen. Het verschil, vooral bij korte varchars, is marginaal. Maar het verschil tussen 0 joins en 1 joins kan zomaar wel een impact hebben.

Daarnaast; meten is weten en premature optimization is the root of all evil.

[gorgi_19]

Sowieso zal een query met '%' + zoekopdracht + '%' traag zijn omdat er geen index gebruikt kan worden; bij zoekopdracht + '%' kan (vaak) wel een index gebruikt worden.

[Big Womly]

Je zou hash waarden van je varchar kunnen berekenen en die in je kolom steken als integer.

Maar dan ben je eigenlijk je eigen indexen aan het creëren

[van.der.schulting]

whoami schreef op vrijdag 23 maart 2012 @ 13:53:
Euh, wat probeer je juist te doen ? Waarom zou je een *hack* gaan doen om toch maar een index op een integer veld te kunnen plaatsen, en zo je applicatie / datamodel verkrachten, terwijl de performance-winst toch waarschijnlijk maar marginaal zal zijn imho.

Hoe zoek je op dat varchar veld ?

Voorbeeldje:
Ik heb de volgende tabel:

code:

| advertisements | CREATE TABLE `advertisementss` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product` varchar(255) DEFAULT '',
}

Laten we zeggen, ik heb 10.000 advertenties en misschien 100 producten. De 20 grootste producten beslaan 98% van mijn tabel. Dan lijkt me een index wel interessant om te gebruiken, als ik op producten wil zoeken...
Nu lees ik op Internet dat een varchar best traag is in vergelijking met een integer als het gaat om een index. Dus daar baseer ik het op.

Dus vandaar dat ik me afvroeg of er een betere manier is dan een varchar op een index te zetten...?
Maar ik begin het idee te krijgen dat de snelheidsverlies t.o.v. een integer relatief is en dat ik appels met peren aan het vergelijken ben... Zolang ik geen LIKE gebruik (en dat gebruik ik niet) is een index op een varchar best interessant, mits ik niet al teveel verschillende waardes in het veld heb waarop de index. Dus maw dat niet in vrijwel elke rij het specifieke veld een andere waarde heeft.

[Styxxy]

van.der.schulting schreef op vrijdag 23 maart 2012 @ 17:47:
Nu lees ik op Internet dat een varchar best traag is in vergelijking met een integer als het gaat om een index. Dus daar baseer ik het op.

Pro tip:

Hydra schreef op vrijdag 23 maart 2012 @ 15:34:
Daarnaast; meten is weten en premature optimization is the root of all evil.

Zolang het geen problemen oplevert, zou ik me er absoluut geen zorgen over maken.

[NMe]

Puur op basis van die ene dump hierboven heb je inderdaad niet goed genormaliseerd en moet je eigenlijk gewoon een product-id gebruiken. In welke mate dat de moeite waard is, is afhankelijk van de verdere huidige situatie en vooral hoeveel problemen het nu (en in de toekomst) voor je oplevert.

[Gomez12]

van.der.schulting schreef op vrijdag 23 maart 2012 @ 17:47:
[...]
Voorbeeldje:
Ik heb de volgende tabel:

code:

| advertisements | CREATE TABLE `advertisementss` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product` varchar(255) DEFAULT '',
}

Laten we zeggen, ik heb 10.000 advertenties en misschien 100 producten. De 20 grootste producten beslaan 98% van mijn tabel. Dan lijkt me een index wel interessant om te gebruiken, als ik op producten wil zoeken...

Je benadert het verkeerd om. Je hebt 20 producten, die zet je in een tabel, die geef je een id en daarna zet je in je advertisements tabel enkel je product id's.

Nu lees ik op Internet dat een varchar best traag is in vergelijking met een integer als het gaat om een index. Dus daar baseer ik het op.

Dat gaat over het algemeen over heel andere dingen. Als jij vanuit het niets een product-dbase moet opzetten en je hebt de keuze voor productnrs hoe die ingevuld moeten worden, dan kan je kiezen voor een numeriek veld (integer) of een leuke mogelijkheid om artikelnrs met letters etc toe te staan (varchar).
Dan is een integer sneller dan een varchar. Een kleiner datatype past namelijk sneller / vaker in het geheugen en vereist minder hashes etc. waardoor het sneller is.

Dus als de uitkomst van de keuze geen verdere gevolgen heeft dan altijd het kleinste datatype pakken wat er toepasselijk voor is.
Een varchar-index presteert ook niet slecht, een integer-index presteert echter beter. En met 10.000 producten zal het verschil niet eens te merken zijn (denk ik). Echter heb je 10 miljoen producten verspreid over 300 miljoen advertenties tja dan gaat die varchar 255 je al redelijk snel de nek kosten omdat waarschijnlijk alle index-lookups etc vanaf schijf moeten gaan komen.

[Cyphax]

gorgi_19 schreef op vrijdag 23 maart 2012 @ 17:26:
Sowieso zal een query met '%' + zoekopdracht + '%' traag zijn omdat er geen index gebruikt kan worden; bij zoekopdracht + '%' kan (vaak) wel een index gebruikt worden.

Je kunt als je heel graag omgekeerd ook wilt zoeken een computed column gebruiken (als je DBMS dat ondersteunt tenminste) en die automatisch je varchar achterstevoren opslaan. Dan kun je via die omweg ook zoeken op %zoekopdracht en toch je index gebruiken.

[cariolive23]

Waarom gebruik je geen EXPLAIN om te kunnen zien of een index wordt gebruikt? En je kunt de index ook zo weer weggooien om eens te zien wat EXPLAIN oplevert zónder de index.

Zonder de resultaten van EXPLAIN is het een beetje een kansloos topic, zeker omdat je er zelf vrij weinig van afweet. Een BTREE index, de standaard in vele DBMSen, is een gesorteerde index en dus ook razendsnel met een VARCHAR.