[MSSQL] index voor een LIKE op groot aantal records?

[Verwijderd]

Situatie is als volgt: We gebruiken hier MSSQL, en we hebben een tabel met daarin alle adressen in Nederland. Kolommen zijn:

- Straat
- Plaats
- Huisnummer
- Postcode

En nog wat extra dingetjes die niet echt ter zake doen.

Het gaat hier om circa 8 miljoen records, en daar moet nogal wat in gezocht worden. We hebben daarom allerlei indexen gespecificeerd om het geheel een stuk sneller te maken. Tot zover alles goed omdat we bijv. gebruik maken van query's als:

code:

SELECT * FROM adressen
WHERE postcode = '1234AB'

Binnen luttele miliseconden hebben we het resultaat.

Echter, nu willen we de volgende query gaan gebruiken:

code:

SELECT * FROM adressen
WHERE straat LIKE '%hoofd%'
AND plaats = 'Amsterdam'

De wildcards in deze query maken het geheel verschikkelijk traag. Vooral in de grote steden (A'dam, R'dam etc.)

Mijn vraag is: Is er een speciale manier om een index te specificeren voor dit soort query's? Ik las al het eea over full-text indexen, maar het werd me niet precies duidelijk hoe dit werkt, of wat dit is. Hoe zou je bovenstaande query zo snel mogelijk uit kunnen laten voeren?

[zneek]

Kijk hier maar eens: http://www.sql-server-performance.com/

Niet alleen nuttige tips voor indexes en full-text, maar voor zo'n beetje alles wat met MSSQL te maken heeft.

[edit] doe eens in SQL Enterprise Manager: rechtermuisclick: help Geen geintje, staat veel uitleg.

[ Voor 27% gewijzigd door zneek op 23-07-2003 15:38 ]

[ekoopman]

Wat je ook kan doen is de straatnaam vervangen door een straatID (id. voor plaatsnaam) en dan 2 extra tabellen maken met straatnaam-straatID en plaatsnaam-plaatsID, hierdoor hoef je veel minder records door te zoeken met de LIKE '%bla%' constructie.

[Verwijderd]

Dubbele index? Dus Indexen op straat EN stad

[Crazy D]

Verwijderd schreef op 23 July 2003 @ 23:23:
Dubbele index? Dus Indexen op straat EN stad

Als ik Kroxigor begrijp, bedoelt dat je zeg maar een tabel hebt met alle plaatsen
1 - Rotterdam
2 - Lutjebroek

Een tabel met straatnamen
1 - Straat1
2 - Straat2

En in de adressen-tabel zet je niet de straatnaam, maar het ID.
Dan hoef je "alleen maar" de straatnamen tabel met een like te query'en, krijg je 1 of meerdere ID's terug, en kun je op basis van die ID's de adressen er verder bijhalen.
Als Straat1 huisnummers heeft van 1 t/m 10, staat in je adressen-tabel dus 10 keer Straat1. Als je zoekt op 'straat%', worden er 10 records vergeleken. Als je de straatnamen in een aparte tabel hebt, wordt die like maar 1 keer gedaan, en haal je met behulp van de ID's de gegevens uit de adressentabel op.

Zo ongeveer, volgens mij vergeet ik nog wat, maar dat idee in ieder geval

[Verwijderd]

code:

SELECT * FROM adressen
WHERE straat LIKE '%hoofd%'
AND plaats = 'Amsterdam'

Ik heb echt de ballen verstand van deze shit. Dit zal ook wel niet de eaxacte query zijn, en ik heb geen idee hoe mssql optimaliseert, maar dit lijkt me een stuk sneller:

code:

SELECT * FROM adressen
WHERE plaats = 'Amsterdam' AND straat LIKE '%hoofd%'

[terrapin]

Je database model is niet correct.. Zet het om naar een database met 3 tabellen zoiets als dit (heel erg snel in elkaar gekloot, zal ook niet 100% correct zijn, maar iig beter.. ik weet ook niet meer hoe postcode en straatnaam/nummer precies gelinkt zijn...):

code:

tabel adres:

huisnummer
postcode_id

tabel postcode:

id
postcode
straatnaam
plaatsnaam_id

tabel plaatsnaam:

id
plaatsnaam

En dan met de juiste querys en indexen op de id's zou het best snel moeten zijn, een like op een paar honderd plaatsen of straatnamen is niet zo zwaar..

Lees ook eens wat over normaliseren en database modellen, dat zal je hierbij heel erg helpen!

[ Voor 20% gewijzigd door terrapin op 24-07-2003 15:13 ]