[database] indexen

[HenkS]

heb een vraag,

ik weet dat indexen bv het zoeken sneller maken, en het updaten / inserten weer langzamer, maar nu zit ik dus met een vraag:

ik heb een site over auto's je kunt echt zowat overal op zoeken :

prijs, aantal deuren, kleur, dealer/particulier, bouwjaar, aantal kilometers en zo zijn er nog wat categorien...

het is belangrijk dat de zoek snel is, en iets minder belangrijk dat het invoeren snel is, en wijzigingen zijn er niet of nauwelijk...

dus dan zeg je maak veel indexen aan, maar kan ik in dit geval, echt op ieder veld waar op gezocht kan worden een index zetten??? of is dat iets teveel van het goede??? (en ik neem aan dat het allemaal aparte indexen worden, en geen gecombineerde) (is trouwens mysql)

[Verwijderd]

ligt er onder andere aan hoe je zoekt,
als je zoekt : Merk like '%opel%'
of upper(Merk)='OPEL'
dan heb je niks aan een index op merk.

[edit]
maar op de numerieke velden kan je wel een index zetten, mits er op gequeried wordt.

het ligt ook een beetje aan de hoeveelheid updates van de geindexeerde velden.

[HenkS]

het is een query die zich dynamisch opbouwt afhankelijk van wat in het formulier wordt ingevuld dus de query kan bv zo uitzien:

SELECT a.a_id, a.p_d, a.k_id, a.aantal_deuren, a.kleur, a.brandstof, a.invoer_datum, a.merk, a.type, a.kmstand, a.prijs, a.bouwjaar, a.bovag FROM auto a WHERE 1=1 AND a.merk = 'Alfa Romeo' AND a.categorie = 'Sedan' ORDER BY a.invoer_datum DESC LIMIT 0, 25

maar bv ook zo als er opties worden geselecteerd:
SELECT a.a_id, a.p_d, a.k_id, a.aantal_deuren, a.kleur, a.brandstof, a.invoer_datum, a.merk, a.type, a.kmstand, a.prijs, a.bouwjaar, a.bovag FROM auto a , auto_opties_koppel k WHERE k.a_id = a.a_id AND k.o_id in (6,2) AND a.merk = 'Alfa Romeo' AND a.categorie = 'Sedan' GROUP BY a.a_id HAVING COUNT(k.o_id) = 2 ORDER BY a.invoer_datum DESC LIMIT 0, 25



maar kan dus ook nog VEEL langer zijn als er meer opties worden meegegeven..... dus is het dan verstand op ieder veld een index te zetten?

[Super_ik]

neej, indexen zijn vooral makkelijk als je meerder tabbelllen aan elkaar wil knopen
like
tabel merken:
1 opel
2 citroen
3 mazda

tabel auto
1 XM 2
2 ZX 2
3 Picasso 2

[Verwijderd]

WHERE 1=1 AND a.merk = 'Alfa Romeo' AND a.categorie = 'Sedan' ORDER BY a.invoer_datum DESC LIMIT 0, 25

WHERE
a.merk = 'Alfa Romeo' -> index
a.categorie = 'Sedan' -> index
ORDER BY
a.invoer_datum -> zeker een index

[HenkS]

Op maandag 11 maart 2002 10:03 schreef deur het volgende:

[..]

WHERE
a.merk = 'Alfa Romeo' -> index
a.categorie = 'Sedan' -> index
ORDER BY
a.invoer_datum -> zeker een index

ja dat snap ik, maar het WHERE statement kan dus nog VEEL langer worden als er op meer gezocht wordt... dus dan zou je zeggen in dat geval op alle velden een index, MAAR als het WHERE statement niet lang is, worden veel indexen niet gebruikt, dus wat doe ik nu???

[Verwijderd]

je kan op ieder veld dat in de where of in de order by kan komen een index zetten (mits er nog geen op zit zoals de pk)
mits je er geen functie of like '% bij gebruikt, angezien je weinig mutaties uitvoert zal dit weinig negatieve impact hebben op de snelheid, en de ruimte-toename is ook te verwaarlozen

[pistole]

Op maandag 11 maart 2002 09:59 schreef HenkS het volgende:
AND a.categorie = 'Sedan'

foei!

[HenkS]

LOL

ja er is maar 1 like veld, de rest komt allemaal uit dropdowns, dus op al die velden een index pleuren? en dan wel of niet op het LIKE % veld?

[Verwijderd]

je moet in dat veld natuurlijk wel enige variatie aan waardes hebben wil en index nut hebben.
stel je hebt een kolom Totalloss, met de waarden true/false, en je hebt bv 5000 records in je tabel, dan heeft een index weinig nut, omdat de waardes niet kenmerkend genoeg zijn.
als je dan een query op die index zou uitvoeren moet je nog bijna de hele tabel door.

[ACM]

Op maandag 11 maart 2002 10:07 schreef deur het volgende:
en de ruimte-toename is ook te verwaarlozen

dat hangt vrij sterk van je tabel af

de reply-tabel van GoT heeft toch zo'n 500MB-1GB aan indices meende ik

[Edit] wat je misschien ook nog kan doen om het zoeken te versnellen, is het gebruik maken van integers voor velden die weinig verschillende waarden kennen (bijvoorbeeld type auto, sedan=1, hatchbak=2 etc)
integer vergelijkingen gaan vele malen sneller dan string vergelijkingen...
En het is trouwens ook kleiner in opslag -> weer snelheidswinst door kleinere resultaten.

[Verwijderd]

Op maandag 11 maart 2002 10:11 schreef HenkS het volgende:
LOL

ja er is maar 1 like veld, de rest komt allemaal uit dropdowns, dus op al die velden een index pleuren? en dan wel of niet op het LIKE % veld?

op een like '%bla conditie wordt de index niet gebruikt vanwege het kenmerk van de index (sortering),
dus kan je hem beter weglaten

[Super_ik]

Op maandag 11 maart 2002 10:16 schreef ACM het volgende:

[..]

dat hangt vrij sterk van je tabel af

de reply-tabel van GoT heeft toch zo'n 500MB-1GB aan indices meende ik

[Edit] wat je misschien ook nog kan doen om het zoeken te versnellen, is het gebruik maken van integers voor velden die weinig verschillende waarden kennen (bijvoorbeeld type auto, sedan=1, hatchbak=2 etc)
integer vergelijkingen gaan vele malen sneller dan string vergelijkingen...
En het is trouwens ook kleiner in opslag -> weer snelheidswinst door kleinere resultaten.

en heb je minder kans op van die wrong case gevallen
like type=citroen != type=Citroen
met de integer manier heb je daar geen last van aangezien het woord Citroen maar 1 keer voorkomt in een andere tabel

[drm]

Super_ik:
en heb je minder kans op van die wrong case gevallen
like type=citroen != type=Citroen
met de integer manier heb je daar geen last van aangezien het woord Citroen maar 1 keer voorkomt in een andere tabel

Maakt natuurlijk geen drol uit, want je moet nog steeds selectie op tekst doen.

[ACM]

Op maandag 11 maart 2002 16:28 schreef drm het volgende:
Maakt natuurlijk geen drol uit, want je moet nog steeds selectie op tekst doen.

Niet als je een pulldown/selectie menu geeft

Dan krijg je gewoon een array van getallen aangereikt (als je het handig aanpakt)

[Super_ik]

Op maandag 11 maart 2002 16:35 schreef ACM het volgende:

[..]

Niet als je een pulldown/selectie menu geeft

Dan krijg je gewoon een array van getallen aangereikt (als je het handig aanpakt)

Op maandag 11 maart 2002 16:28 schreef drm het volgende:

[..]

Maakt natuurlijk geen drol uit, want je moet nog steeds selectie op tekst doen.

idd volgens mij snap jij t nie helemaal
je hebt dan een tabel (id,type)
en een andere tabel (id,auto,typeid)
elk type komt maar 1 keer voor, als je daar dan een spelfaut in maakt, tja

[drm]

Da's waar, maar dan is 'Citroen' != 'citroen' ook geen issue meer

[Super_ik]

jij bent t wel heel snel met mij eens

[drm]

Super_ik:
jij bent t wel heel snel met mij eens

zie ondertitel Ik antwoordde op ACM, en was het met hem eens, sorry

De vraag is natuurlijk wat je input is. Als dat een stel ID's is, dan heb je sowieso met het zoeken helemaal niets meer met de tekst te maken, want voor mijn part is het ID dan gekoppeld aan een tabel van hier tot tokio.

Daarnaast is er een heel eenvoudige stelregel die zegt dat je altijd indexen op je foreign keys moet zetten, omdat dat altijd ten goede komt van je performance. (Over het algemeen wordt een database vaker uitgelezen dan ingevuld )

dus

[HenkS]

de zoek bestaat allemaal uit dropdowns, en diezelfde dropdowns worden gebruikt bij het invoeren van een auto dus er kunnen geen vergelijkinsfouten worden gemaakt, kan het wel laten zien, maar dan maak ik spam, of niet???

[Super_ik]

hoe voeg jij dan een nieuw type toe, of lees jij heel je autotabel uit ben een select type from autoos group by type?
en zet je die in een dropdown
of heb je een aparte tabel met alle types en doe je
select id, type from types;
die tabel is dan ook veel kleiner

[drm]

Normaliter zou ik het zo aanpakken:
Bij het formulier:

code:

# ------------------
# MySQL
SELECT
   id,
   name
FROM
   types
# evt. nog wat WHERE meuk

<!-------------------->
<!-- HTML -->
<select name="typeID[]" multiple="multiple">
<!-- elke row: -->
   <option value="hier id">hier naam</option>
</select>

bij resultaat pagina:

code:

//-------------------
// PHP
$clauses = array ();

if ( !empty ( $typeID ) && count ( $typeID) > 0 )
   $clauses [] = " ( auto.typeID IN (" 
           . implode ( "," $typeID )
           . ")";

// ...
if ( count ( $clauses ) > 0 )
   $selection = "WHERE " . implode ( $andor ? " AND " : " OR " ), $clauses );
else
   $selection = "";
# -------------------
# MySQL
SELECT
   meuk
FROM
   autos
$selection

ongeveer

Heb eigenlijk nooit getest hoe snel die IN (...) eigenlijk is, maar hij is iig heel eenvoudig coden.

[Johannes]

Op maandag 11 maart 2002 17:02 schreef HenkS het volgende:
maar dan maak ik spam, of niet???

Als het relevant is voor het topic(en dat is het volgens mij wel), dan mag dat AFAIK gewoon.

[HenkS]

nou ik vul iedere dropdown gewoon vanuit de database, zo kan ik in het onderhoudssysteem dus ook makkelijk types enzo toevoegen..

de reden dat ik dit alles vraag, is omdat de site pas in de lucht is en er nu zo'n 600 auto's inzitten en de zoektijd best omhoog is gegaan (die schrijf ik per zoek ook weg) vandaar. en zo ziet het zoekform er dus uit:

http://www.limburgmobiel.nl dan bij zoek en dan auto's...

en ik heb dus zowat op ieder veld nu een index gezet, behalve op bovag omdat dat maar 3 mogelijkheden zijn en op het veld type... is dat dan goed?

[drm]

Als je op text zoekt, en je gebruikt LIKE of REGEXP heeft indexen zetten op die velden volgens mij helemaal geen zin. Dan kun je overwegen zelf een indexering te maken van de database.

code:

tabel indices
-------------
->  indexID: INT primary key
->  word varchar(30)
->  itemID KEY 
    (foreign id: in welke entries het woord in voorkomt)
->  times INT
    ( Het aantal keer dat het woord er 
    in voorkomt, voor sorteren)
->  itemTypeID KEY 
    ( eventueel het soort entry 
    waar het woord in voorkomt, bijvoorbeeld
    verschillende tabellen en/of verschillende 
    velden in tabellen )

Dan om de zoveel tijd, evt. met een cronjob, of wanneer er nieuwe gegevens toegevoegd worden (al naar gelang hoe vaak je de database update), de database indexeren (de indices tabel updaten)

Alleen wanneer je = gebruikt heeft het zin een index op textvelden te zetten.
_{Volgens mij krijg je ook, hoe meer indexen je op 1 tabel zet, hoe minder effect zo'n index krijgt.}

Verder gewoon altijd op foreign keys een index zetten. Dan heb je de max performance wel aardig te pakken

[HenkS]

via een vriend van me kwam ik aan 2 zeer interessante artikelen:

http://www.zdnetindia.com/techzone/coding/stories/20351.html
en:
http://www.wdvl.com/Authoring/DB/SQL/Optimize/

hierin wordt de 'explain' mogelijkheid van MYSQL uitgelegd, echt super interessant, hiermee analyzeer je je indexen en krijg je ook nog eens een soort 'advies' terug over de kwaliteit van je indexen!!!

maar nu komt het, ik heb deze 'EXPLAIN' eens uitgevoerd op mijn queries en volgens het artikel kunnen mijn indexen nog een stuk beter, MAAR ik zou sommige dingen niet weten op te lossen, bv: (aub artikel eerst ff lezen, anders is het volgende wat lastig te begrijpen)

- bij 'type' krijg ik 'ref' terug, en dit moet beter kunnen volgens die analyzer, want er zijn beterer types, maar de vraag is, hoe maak je er dan een ander type van???

- bij 'extra' krijg ik filesort terug, dit is dus NIET goed, maar hoe zorg je ervoor dat je dit niet krijgt, dit zie ik nergens staan

- Als ik een select doe met een WHERE en een AND dus op 2 velden zoek, krijg ik bij 'possible_keys' 2 indexen terug (dit is juist), MAAR bij 'key' (welke dus echt wordt gebruikt, geeft hij maar 1 index terug, dit klopt toch niet??

nou ik heb nog wel meer, maar ik hoop dat wat mensen dit artikel lezen, zodat we het er hier eens over kunnen hebben, is toch wel zeer interessant lijkt me

[HenkS]

stilte alom zei de ongeduldige man