[mysql] Index op (bijna) elke kolom?

[pim]

Ik heb een mysql tabel met 20 kolommen, en +/- 100.000 records.
De tabel krijgt zelden nieuwe INSERT's, maar regelmatig een kleine update waarbij word bijgehouden hoe vaak de 'row' bekeken is.. (update tabel set views=views+1 where id = #id)

De tabel draait op een babynamen website, en nu ga ik een namen zoekfunctie maken waarbij letterlijk elke kolom in de where clause kan voorkomen.

Een zoekquery zou er zo uit kunnen zien:

MySQL:

SELECT * FROM tabel WHERE
kolom1 like '%a%' AND kolom2 like '%a%' AND kolom3 like '%a%' AND kolom4 like '%a%' AND
kolom5 like '%a%' AND kolom6 like '%a%' AND kolom7 like '%a%' AND kolom8 like '%a%' AND 
kolom9 like '%a%' AND kolom10 like '%a%' AND kolom11 like '%a%' AND kolom12 like '%a%' AND
kolom13 like '%a%' AND kolom14 like '%a%' AND kolom15 like '%a%' AND kolom16 like '%a%' AND
kolom17 like '%a%' AND kolom18 like '%a%' AND kolom19 like '%a%' AND kolom20 like '%a%';

Is het verstandig om op 19 van de 20 kolommen een index te zetten?

[ Voor 3% gewijzigd door pim op 07-03-2012 15:24 ]

[Verwijderd]

Met zoveel like clauses performt het toch al niet meer, dus bespaar je de moeite.
Als ik dit soort dingen zie is er reden om te twijfelen aan je databasemodel. Misschien moet je overigens gebruik gaan maken van bijvoorbeeld Sphinx Search.

[Gomez12]

like '%a%' gebruikt uberhaupt al geen indexes, dus daarvoor hoef je het niet te doen.

Als je fulltext over tig kolommen wilt doen dan is het verstandiger om eens te kijken naar een fulltext-searcher (Sphinx / SolR / Lucene)

Maar zoals Cheatah al zegt : denk eens goed na of je db-model wel het juiste is.

[leuk_he]

Nee,

Like (zeker als het niet like 'a%' is ) zal geen (gewone) index gebruiken. Dan kun je beter een of andere full text indexer gebruiken.

En een full table scan is sneller dan 19 full index scans.

Enige wat je nog kunt bekijken is om die statistieken(accessed) in een aparte tabel vast te houden. Maar dan moet je bijhouden (de updates) een bottleneck zijn, dat is wel na te zoeken in je statistieken per statement.... ja of je database model om te gooien. ... zodat het geoptimaliseerd is voor de zoekvragen.

[ Voor 9% gewijzigd door leuk_he op 04-03-2012 11:21 ]

[pim]

Op dit moment is ie zonder indexen nog snel...
Bovenstaande query: Showing rows 0 - 29 ( 106,689 total, Query took 0.0018 sec)

Thanx voor de tips.. Ik ga me eens inlezen in betere methodes, voor het geval mijn site het drukker krijgt.

De voorbeeld query was wellicht trouwens iets te simpel, het kan er ook zo uit zien:

MySQL:

SELECT * FROM tabel WHERE 
kolom1  > 0 AND kolom2 >  '5' AND kolom3 = 'test' AND kolom4 = '1' AND 
kolom5 like 'a%' AND kolom6 like '%a' AND kolom7 like 'aaa' AND kolom8 not like '%abc%' AND  
kolom9 != 1 AND kolom10 like 'bla' AND kolom11 like '%a%' AND kolom12 like '%a%' AND 
kolom13 like '%a%' AND kolom14 like '%a%' AND kolom15 like '%a%' AND kolom16 like '%a%' AND 
kolom17 like '%a%' AND kolom18 like '%a%' AND kolom19 like '%a%' AND kolom20 like '%a%';
 

[ Voor 54% gewijzigd door pim op 04-03-2012 11:29 ]

[Gomez12]

leuk_he schreef op zondag 04 maart 2012 @ 11:20:
En een full table scan is sneller dan 19 full index scans.

Gevaarlijke uitspraak als je het aantal tablerows niet weet. Bij kleine tabellen zal het vast waar zijn, maar op het moment dat je table niet meer in memory past en je indexen nog wel kan je raar staan te kijken...

[leuk_he]

Gomez12 schreef op zondag 04 maart 2012 @ 11:27:
[...]

Gevaarlijke uitspraak als je het aantal tablerows niet weet. Bij kleine tabellen zal het vast waar zijn, maar op het moment dat je table niet meer in memory past en je indexen nog wel kan je raar staan te kijken...

In dit geval weet je daar iets over, dat geeft de topic starter immers aan.

En alle kolommen indexeren kost je vrijwel altijd meer geheugen dan de originele tabel. Want elke geïndexeerde item moet niet alleen het veld vasthouden, maar ook verwijzingen naar de andere items in de index boom (m-arey boom index is gebruikelijk), en een verwijzing naar het item in de uiteindelijke tabel.

Bovendien... als iemand roept.. 19 indexen op een tabel, dan gaat er toch al alarmbellen rinkelen. Toch? Waarom denk je dat meerder mensen roepen dat ze zich afvragen of het db modelhet juiste is hier een paar keer voorbij komt.

Overigens heb ik een gruwelijke hekel te optimaliseren voor gevallen "past in geheugen". Je bent dan zo detaillistisch aan het optimaliseren terwijl je eigenlijk de grote lijn in de gaten moet houden. Voor gevallen waar het wel relevant is, Datawarehouse met miljoenen records enzo, is je ontwerp erop gemaakt.

[ Voor 10% gewijzigd door leuk_he op 04-03-2012 11:59 ]

[GlowMouse]

pim schreef op zondag 04 maart 2012 @ 11:24:
Op dit moment is ie zonder indexen nog snel...
Bovenstaande query: Showing rows 0 - 29 ( 106,689 total, Query took 0.0018 sec)

Die snelheid komt door de LIMIT 30 in je query. Draai de query in de OP eens met %a% vervangen door %komtnooitvoor%

[MSalters]

Bedoel je met kolom1 like '%a%' AND kolom2 like '%a%' overigens dat je dezelfde zoekstring "a" in beide kolommen zoekt? Of is het eerder kolom1 like '%a%' AND kolom2 like '%b%'

[The Eagle]

Waarom niet een aparte tabel waarin het aantal views bijgehouden wordt