[SQL - MySQL] textfields in aparte kolom?

[Verwijderd]

Haaaj

Is het "verstandig" om texfields (xxxTEXT en xxxBLOB) op te slaan in een aparte tabel? Mij lijkt dit het voordeel te hebben dat je sneller kan bladeren doorheen je gegevens, als je niet echt geïnteresseerd bent in de teksten zelf. Maar wat van de andere voordelen? En de nadelen? Iemand ervaring mee?

En wat doe je het beste: per "doel" textfield een aparte tabel aanmaken, of gewoon alles door elkaar gebruiken? Ik bedoel dus ofwel bijvoorbeeld aparte tabellen als "news_text, forumpost_text, mail_text,..." of die gewoon samenvoegen in 1 tabel "text"?

Deze tabellen bevatten dus (lijkt me) slechts deze 3 velden: text_id (key), text_field, text_timestamp. De waarde van een text_id veld sla je dus op daar waar je normaal een textfield zou plaatsen (en maak je dus een index).

dank

[ Voor 5% gewijzigd door Verwijderd op 19-12-2002 23:45 ]

[_Thanatos_]

Ik denk dat het verstandiger is om alles in één grote tabel op te slaan. Zo kun je veel makkelijker je data eruit selecteren. Een grote tabel is ook sneller, omdat MySQL niet geen table-scans of index-scans (of hoe dat ook heet) hoeft te doen op meerdere tabellen.

Goeie indices leggen op die tabel is dan natuurlijk een vereiste.

[Verwijderd]

_Thanatos_ schreef op 20 december 2002 @ 00:02:
Ik denk dat het verstandiger is om alles in één grote tabel op te slaan. Zo kun je veel makkelijker je data eruit selecteren. Een grote tabel is ook sneller, omdat MySQL niet geen table-scans of index-scans (of hoe dat ook heet) hoeft te doen op meerdere tabellen.

Goeie indices leggen op die tabel is dan natuurlijk een vereiste.

data redundancy? anomalies?

[Verwijderd]

_Thanatos_ schreef op 20 December 2002 @ 00:02:
Ik denk dat het verstandiger is om alles in één grote tabel op te slaan. Zo kun je veel makkelijker je data eruit selecteren. Een grote tabel is ook sneller, omdat MySQL niet geen table-scans of index-scans (of hoe dat ook heet) hoeft te doen op meerdere tabellen.

Dat wil ik van jou aannemen Op zich is het zoiezo "vreemd" om x-keren eenzelfde tabel aan te maken met alleen een verschillende naam.

Goeie indices leggen op die tabel is dan natuurlijk een vereiste.

Indices? Waar zou jij dan je indices op leggen? Ik dacht simpelweg aan een primary key op text_id? Of dacht jij aan indices op text_field en text_timestamp (lijkt mij alleen nuttig als je search-functies gaat ondersteunen... en zelfs dan nog)?

[ Voor 11% gewijzigd door Verwijderd op 20-12-2002 00:08 ]

[_Thanatos_]

Indices ja, eentje inderdaad als primary key, eentje op de timestamp (want daar wil je waarschijnlijk op sorteren) en een fulltext index voor de tekst.

3 dus

[Tom-Eric]

Ik ben zelf bezig met een database voor een dupecheck site ala isonews, en aangezien er nogal veel releases zijn per dag heb ik besloten om de tekstbestanden afgeschermd te houden van de tabel waarin de releases staan. De meeste mensen kijken naar een paar van die tekstbestanden en komen toch vooral om te zien of er iets nieuws is. Aangezien na een paar test runs de database na een week al rond de 5000 releases had en hij redelijk groot werd heb ik bij mezelf besloten om ze uitelkaar te houden.

Het lijkt me toch na een paar miljoen toevoegingen dat de database redelijk langzaam wordt als hij ook steeds die tekstbestanden moet behandelen, maar het kan ook zijn dat ik het fout heb.

[_Thanatos_]

Ik denk dat als je applicatie langzamer van een 'bredere' tabel wordt, dat dat eerder aan je applicatie ligt, dan aan de database. Een "select * from tabel" uit een tabel met 3 velden is praktisch even snel als een "select veld1,veld2,veld3 from tabel" uit een tabel met 100 velden. En op het moment dat de tekstvelden nodig zijn, moet de database server toch weer extra werk gaan verrichten voor het bij elkaar zoeken van de juiste records, ook al heb je goeie indices op de tabellen gelegd.

[Goodielover]

Groooote BLOB's en textvelden kunnen wel degelijk een negatieve performance invloed hebben. Een beetje afhankelijk van de grootte van je tabel zal het op een gegeven moment sneller worden om een aparte tabel te gebruiken voor de BLOB-achtige velden.

Dit gaat helemaal op als je ook veel alleen de overige velden nodig hebt of als je daar veel in wilt zoeken.
Je DBMS moet dan namelijk meer harde schijfruimte afzoeken.
Vogens mij zijn er geen negatieve performance aspecten aan het splitsen van de tabel. Je zult alleen wat meer moeten programmeren.

[Verwijderd]

Oké! Hier ben ik wat bij Het was dus een goed plan. Dank voor de uitleg!

Alleen ben ik nog aan het twijfelen of ik een extra enum veld zou adden, die dan bijvoorbeeld volgende waarde zou kunnen hebben: 'NEWS','FORUMPOST','MAIL',... Op dit moment zie ik er geen voordelen van in, maar wie weet ...

[Verwijderd]

_Thanatos_ schreef op 20 December 2002 @ 03:09:
Indices ja, eentje inderdaad als primary key, eentje op de timestamp (want daar wil je waarschijnlijk op sorteren) en een fulltext index voor de tekst.

3 dus

Dus:

· primary key op text_id
· foreign key (index) op text_timestamp
· fulltext index op text_field

Maar hoe leg je een fulltext index op een TEXT veld? En wat voor nut heeft dat? Waarschijnlijk voor het zoeken in teksten, maar zijn er nog andere redenen?

Uit de mysql manual (http://www.mysql.com/doc/en/Fulltext_Search.html):

Full-text searching is performed with the MATCH() function.

The MATCH() function performs a natural language search for a string against a text collection (a set of of one or more columns included in a FULLTEXT index). The search string is given as the argument to AGAINST(). The search is performed in case-insensitive fashion. For every row in the table, MATCH() returns a relevance value, that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

When MATCH() is used in a WHERE clause (see example above) the rows returned are automatically sorted with highest relevance first. Relevance values are non-negative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

[ Voor 59% gewijzigd door Verwijderd op 20-12-2002 10:32 ]

[_Thanatos_]

Nee, timestamp is geen foreign key... een gewone index zou ik ervan maken.

Ik moet zeggen dat ik geen verstand heb van mysql's fulltext engine, maar een fulltext index op een textveld zorgt er meestal voor dat er, net als achterin een boek, een lijst gemaakt wordt met alle voorkomende woorden in alle teksten en dat daar vervolgens doorheen gezocht wordt.

Ik weet niet of het in mysql ook kan, maar in mssql kun je dan nog andere truukjes uithalen zoals "woord 1 moet in de buurt van woord 2 staan" of zoeken naar woorden die op elkaar lijken zoals "sleep" en "slept"...

[Sybr_E-N]

In MySQL kun je ook zoek naar worden die ergens op lijken, mbv LIKE. Door wel of geen wildcard(s) te gebruiken.

[Verwijderd]

Ah! Want ik dacht dat full-text search alleen kon via de "match() functie", omdat ik in de manual las:

Full-text searching is performed with the MATCH() function.

Niet dus?

[_Thanatos_]

Een LIKE gebruiken zou ik altijd afraden. Ten eerste omdat dit geen full-text search doet en dus niet de hoge snelheid ervan kan behalen. Ten tweede omdat LIKE (net als REGEXP) nooit en te nimmer een index zal gebruiken en dus ook bij kleine tabellen al relatief erg traag is.

[Verwijderd]

_Thanatos_ schreef op 23 December 2002 @ 15:52:
Een LIKE gebruiken zou ik altijd afraden. Ten eerste omdat dit geen full-text search doet en dus niet de hoge snelheid ervan kan behalen. Ten tweede omdat LIKE (net als REGEXP) nooit en te nimmer een index zal gebruiken en dus ook bij kleine tabellen al relatief erg traag is.

Zoals ik zei dus

De MATCH() functie moet ik dus gebruiken?

Waar ik het ook nog moeilijk mee heb: hoe moet je juist indices "opstellen".. Soms zie ik dat men indices gebruikt bestaande uit meer dan 1 veld, ipv. van 2 aparte indices te maken, een voor veld1 en een voor veld2. Is daar een verschil tussen? En zo ja, wat is dat verschil en hoe moet ik het gebruiken opdat ze zo efficiënt mogelijk benut worden? Ivm. dat soort vragen vind ik de mysql.com-docs echt te beknopt en te technisch :\

[ACM]

Verwijderd schreef op 19 december 2002 @ 23:42:
Is het "verstandig" om texfields (xxxTEXT en xxxBLOB) op te slaan in een aparte tabel? Mij lijkt dit het voordeel te hebben dat je sneller kan bladeren doorheen je gegevens, als je niet echt geïnteresseerd bent in de teksten zelf. Maar wat van de andere voordelen? En de nadelen? Iemand ervaring mee?

Je wilt dus als je dit aan gegeven hebt:
message: (messageid, topicid, userid, messagecontent, messagedate);
dat opslaan als:
(messageid, topicid, userid, messagedate) en (messageid, messagecontent) ?
of als:
(messageid, topicid, userid, messagedate, textid) en (textid, messagecontent) ?

Ik denk dat de performance winst die je haalt, op het moment dat je die messagecontent niet nodig hebt, nihil is en waarschijnlijk teniet gedaan wordt door het performanceverlies dat je krijgt omdat je nu altijd je tabellen moet joinen.
Wil je echt weten hoe het performed, ga het dan uitgebreid testen
Ik zou het iig niet zomaar doen.

En wat doe je het beste: per "doel" textfield een aparte tabel aanmaken, of gewoon alles door elkaar gebruiken? Ik bedoel dus ofwel bijvoorbeeld aparte tabellen als "news_text, forumpost_text, mail_text,..." of die gewoon samenvoegen in 1 tabel "text"?

Sowieso zou ik geen functies van verschillende onderdelen samenvoegen in 1 tabel, tenzij je het zo doet:
text = (textid, textdata)
met een message tabel die zoiets doet:
message = (messageid, userid, topicid, messagedate, textid)
(met een foreign key dus)

Deze tabellen bevatten dus (lijkt me) slechts deze 3 velden: text_id (key), text_field, text_timestamp. De waarde van een text_id veld sla je dus op daar waar je normaal een textfield zou plaatsen (en maak je dus een index).

Waar is de text_timestamp voor dan

_Thanatos_ schreef op 20 december 2002 @ 03:09:
Indices ja, eentje inderdaad als primary key, eentje op de timestamp (want daar wil je waarschijnlijk op sorteren) en een fulltext index voor de tekst.

3 dus

Euh, het timestamp field is totaal overbodig, dus dat moet weg. Je wilt _niet_ sorteren op een relatie die totaal geen waarde heeft (als je alle teksten door elkaar smijt heeft je timestamp totaal geen betekenis meer voor sortering) en dan heeft een index erop ook geen nut.
Een fulltext index domweg op je tekst smijten is een van de domste dingen die je kan doen, dit heeft een enorme performance hit en alleen voordeel als je de teksten ook wilt doorzoeken.

Als je dus zo'n tabel maakt hou je 1 index over en dat is de primary key, meer dan voldoende.

_Thanatos_ schreef op 20 december 2002 @ 16:32:
Ik moet zeggen dat ik geen verstand heb van mysql's fulltext engine, maar een fulltext index op een textveld zorgt er meestal voor dat er, net als achterin een boek, een lijst gemaakt wordt met alle voorkomende woorden in alle teksten en dat daar vervolgens doorheen gezocht wordt.

Ik weet niet of het in mysql ook kan, maar in mssql kun je dan nog andere truukjes uithalen zoals "woord 1 moet in de buurt van woord 2 staan" of zoeken naar woorden die op elkaar lijken zoals "sleep" en "slept"...

Sja, maar als het niet gebruikt gaat worden moet je het absoluut niet zomaar erop smijten...

_Thanatos_ schreef op 23 december 2002 @ 15:52:
Een LIKE gebruiken zou ik altijd afraden. Ten eerste omdat dit geen full-text search doet en dus niet de hoge snelheid ervan kan behalen. Ten tweede omdat LIKE (net als REGEXP) nooit en te nimmer een index zal gebruiken en dus ook bij kleine tabellen al relatief erg traag is.

Het doet wel degelijk fulltext search, maar op een andere manier. Als je een klein genoege subset van de tekstvelden ophaalt is like ook geen probleem meer...

[edit]
Btw, ik meen zelfs ooit gelezen te hebben dat mysql de text velden standaard al niet inline met de tabel opslaat maar op een aparte plek, het kan ook geweest zijn dat dat in de postgresql manual stond en dus niet over mysql ging
Dat zal bij postgres geweest zijn

[ Voor 4% gewijzigd door ACM op 23-12-2002 16:12 ]

[Verwijderd]

ACM schreef op 23 december 2002 @ 16:05:
[...]

Je wilt dus als je dit aan gegeven hebt:
message: (messageid, topicid, userid, messagecontent, messagedate);
dat opslaan als:
(messageid, topicid, userid, messagedate) en (messageid, messagecontent) ?
of als:
(messageid, topicid, userid, messagedate, textid) en (textid, messagecontent) ?

Goede vraag ALS ik het ga doen (ik ga het eerst allemaal eens uittesten, zoals je zelf hierna aangeeft), zou ik het volgens de methode 2 doen. Waarom? Weet ik eigenlijk niet Het lijkt mij het makkelijkste om mee te werken, omdat je nu "rechtstreeks" aan je text-velden kan via dat ID dat je gegeven hebt. Hoe zou jij het doen? Ik kan geen situatie inbeelden waar methode 1 voordeel zou hebben op methode 2.

Ik denk dat de performance winst die je haalt, op het moment dat je die messagecontent niet nodig hebt, nihil is en waarschijnlijk teniet gedaan wordt door het performanceverlies dat je krijgt omdat je nu altijd je tabellen moet joinen.
Wil je echt weten hoe het performed, ga het dan uitgebreid testen
Ik zou het iig niet zomaar doen.


[...]

Sowieso zou ik geen functies van verschillende onderdelen samenvoegen in 1 tabel, tenzij je het zo doet:
text = (textid, textdata)
met een message tabel die zoiets doet:
message = (messageid, userid, topicid, messagedate, textid)
(met een foreign key dus)

Zo wou ik het dus doen! 1 tabel met 2 velden: text_id, text_data.

Waar is de text_timestamp voor dan

Achteraf bekeken hoort die timestamp inderdaad niet in mijn text-tabel thuis. Simpel voorbeeldje: als je een forum maakt, en je wil de topics van de laatste maand neerhalen (en je timestamp staat dus in de text-table), moet hij zoiezo nog naar die text-tabel gaan, en dat was nu net niet de bedoeling

Euh, het timestamp field is totaal overbodig, dus dat moet weg. Je wilt _niet_ sorteren op een relatie die totaal geen waarde heeft (als je alle teksten door elkaar smijt heeft je timestamp totaal geen betekenis meer voor sortering) en dan heeft een index erop ook geen nut.

Dat snap ik niet? Als je gaat sorteren op bijvoorbeeld de topics van de laatste maand, dan sorteer je toch effectief op datum? Of bedoel je dat niet?

Een fulltext index domweg op je tekst smijten is een van de domste dingen die je kan doen, dit heeft een enorme performance hit en alleen voordeel als je de teksten ook wilt doorzoeken.

Ik ben van plan een search functie te maken.

[ACM]

Verwijderd schreef op 23 December 2002 @ 16:24:
Goede vraag ALS ik het ga doen (ik ga het eerst allemaal eens uittesten, zoals je zelf hierna aangeeft), zou ik het volgens de methode 2 doen. Waarom? Weet ik eigenlijk niet Het lijkt mij het makkelijkste om mee te werken, omdat je nu "rechtstreeks" aan je text-velden kan via dat ID dat je gegeven hebt. Hoe zou jij het doen? Ik kan geen situatie inbeelden waar methode 1 voordeel zou hebben op methode 2.

Als er meerdere textvelden bij een enkele message horen werkt de 1e niet en de 2e prima
Als er maar 1 tekstveld per message is kan je (als je dit perse wilt) beter idd op de 2e manier doen.

Achteraf bekeken hoort die timestamp inderdaad niet in mijn text-tabel thuis. Simpel voorbeeldje: als je een forum maakt, en je wil de topics van de laatste maand neerhalen (en je timestamp staat dus in de text-table), moet hij zoiezo nog naar die text-tabel gaan, en dat was nu net niet de bedoeling
[...]
Dat snap ik niet? Als je gaat sorteren op bijvoorbeeld de topics van de laatste maand, dan sorteer je toch effectief op datum? Of bedoel je dat niet?

Ik bedoel het anders.
De timestamp bij de message-tabel moet je zeker houden, maar de timestamp bij de text-tabel (zoals jij het dus wilt) heeft totaal geen betekenis meer. Zeker als de teksten van replies, private messages, nieuws etc allemaal door elkaar zou staan. Want een sortering op dat timestamp is onzin, want element X is niet perse iets dat bij element Y gesorteerd had mogen worden.
Verder is de timestamp bij een tekstje zinloos en verlies je gelijk het voordeel wat je zelf al aanhaalde

Ik ben van plan een search functie te maken.

Ik zou dan alsnog niet gelijk een fulltext index erop gooien, pas als je zeker weet dat dat de manier is die je wilt gebruiken.

[Verwijderd]

ACM schreef op 23 December 2002 @ 16:53:
Als er meerdere textvelden bij een enkele message horen werkt de 1e niet en de 2e prima

Dat kan op zich wel ja, bijvoorbeeld een broadcasting mailtje.

Als er maar 1 tekstveld per message is kan je (als je dit perse wilt) beter idd op de 2e manier doen.

Waarom eigenlijk?

Ik bedoel het anders.
De timestamp bij de message-tabel moet je zeker houden, maar de timestamp bij de text-tabel (zoals jij het dus wilt) heeft totaal geen betekenis meer. Zeker als de teksten van replies, private messages, nieuws etc allemaal door elkaar zou staan. Want een sortering op dat timestamp is onzin, want element X is niet perse iets dat bij element Y gesorteerd had mogen worden.
Verder is de timestamp bij een tekstje zinloos en verlies je gelijk het voordeel wat je zelf al aanhaalde

OK! Nu snap ik je! Als je allemaal verschillende soorten tekst-velden door elkaar hebt, heeft die datum uiteraard totaal geen nut. Had ik eerst niet aan gedacht

Ik zou dan alsnog niet gelijk een fulltext index erop gooien, pas als je zeker weet dat dat de manier is die je wilt gebruiken.

Oké, dat ga ik doen. Ik ga eerst de structuur verder uitwerken, daarna alles goed vullen met dummy teksten, en dan wat testen ivm. die fulltext index (met dus een goedgevulde dbase).

[ACM]

Verwijderd schreef op 23 December 2002 @ 17:01:
Dat kan op zich wel ja, bijvoorbeeld een broadcasting mailtje.

Nee, die heeft juist meerdere messages met een verwijzing naar 1x dezelfde text

Waarom eigenlijk?

(messageid, userid, textid) 1 --- 0..n (textid, textdata)

(messageid, userid) 0..n --- 1 (textdata, messageid)

Kortom, bij de eerste is er verplicht een tekstitem bij elke message, maar dat kan best 10x dezelfde tekst bij 10 verschillende messages zijn. Echter een tekst kan ook geen message bij zich hebben.

Bij de tweede geldt het precies andersom, dan is elke tekst verplicht bij een message, maar heeft een message niet verplicht teksten en kunnen er ook meerdere zijn.

Ook deze zaken zijn redenen waarom je eigenlijk de tabellen niet zomaar uit elkaar moet trekken. Het kan natuurlijk verder allemaal wel, maar je moet wel goed uitkijken met je applicatie eromheen.

Oké, dat ga ik doen. Ik ga eerst de structuur verder uitwerken, daarna alles goed vullen met dummy teksten, en dan wat testen ivm. die fulltext index (met dus een goedgevulde dbase).

Succes

[Verwijderd]

ACM schreef op 23 December 2002 @ 17:08:Nee, die heeft juist meerdere messages met een verwijzing naar 1x dezelfde text

GRR Tekst, message.. ik begin ze zo te verwarren

(messageid, userid, textid) 1 --- 0..n (textid, textdata)

(messageid, userid) 0..n --- 1 (textdata, messageid)

Kortom, bij de eerste is er verplicht een tekstitem bij elke message, maar dat kan best 10x dezelfde tekst bij 10 verschillende messages zijn. Echter een tekst kan ook geen message bij zich hebben.

Bij de tweede geldt het precies andersom, dan is elke tekst verplicht bij een message, maar heeft een message niet verplicht teksten en kunnen er ook meerdere zijn.

Ook deze zaken zijn redenen waarom je eigenlijk de tabellen niet zomaar uit elkaar moet trekken. Het kan natuurlijk verder allemaal wel, maar je moet wel goed uitkijken met je applicatie eromheen.

Heldere uitleg! Dank!

Succes

Thanks Ik zal het nodig hebben

[djluc]

Er is net al een aantal keren gezegd dat de timestamp waardeloos wordt als je de teksten gewoon in 1 tabelletje mikt, als je gewoon een mysql datum en tijd NOW() insert kan je toch gewoon zoiets doen:
[code]
SELECT iets FROM tabel WHERE soort='nieuwsid' ORDER BY datum
[\code]
of denk ik nu te simpel, denk het niet.

[Verwijderd]

djluc schreef op 23 December 2002 @ 18:41:
Er is net al een aantal keren gezegd dat de timestamp waardeloos wordt als je de teksten gewoon in 1 tabelletje mikt, als je gewoon een mysql datum en tijd NOW() insert kan je toch gewoon zoiets doen:
[code]
SELECT iets FROM tabel WHERE soort='nieuwsid' ORDER BY datum
[\code]
of denk ik nu te simpel, denk het niet.

En wat wil je nu zeggen?