Facetted search bouwen 12 miljoen records, welke database?

[JJ93]

Ik heb een dataset met 40 kolommen, en iets meer dan 12 miljoen rijen. Ik wil hiervoor een zo uitgebreid mogelijke facetted search bouwen. Dus voor elke kolom de unieke waardes selecteren en hoe vaak ze voorkomen.

Ik ben eerst simpel begonnen met 100k records en alles ging toen vrij voorspoedig. Maar nu ik alle data heb geïmporteerd is het tellen van de rijen ongelofelijk traag geworden.

Setup:
Ubuntu 14.04, dualcore cpu, 512MB RAM.
MySQL
InnoDB
Primary key ID auto-increment
Index op elke kolom

Een query zoals

SQL:

SELECT field, COUNT(*) FROM db GROUP BY field;

om de unieke waardes en hoe vaak ze voorkomen te selecteren duurt zo'n 25 seconden. Als je dit een tweede keer selecteert dan is het wel gecached blijkbaar want dan duurt het maar 30ms. En dit moet dus 40x gedaan worden om alle facetted search blokjes op te bouwen.

Dit is echter wel problematisch wanneer je complexere queries krijgt die ik moet genereren op basis van de geselecteerde filter opties. Dan krijg je dus miljoenen combinaties en als iemand weer een unieke, niet gecachede combinatie opvraagt duurt het genereren van de nieuwe pagina dus veeeeeels te lang.


DUS: Zou ik hier bijvoorbeeld ElasticSearch voor gebruiken of NoSQL o.i.d.? Moet ik mijn data gaan verspreiden over meerdere tabellen, een andere engine gebruiken of MySQL op een andere manier optimaliseren? Of is mijn server gewoon te licht voor 12 miljoen records?

JJ

[dev10]

Zoals altijd met dit soort dingen: meten is weten. Kijk met EXPLAIN waar de queries precies mee bezig zijn en ga kijken waar de bottlenecks zitten.

Sowieso denk ik dat het een idee is om wat meer geheugen in je server te stoppen, want 512 MB is bar weinig.

[Breezers]

JJ93 schreef op maandag 09 februari 2015 @ 10:57:
I
Setup:
Ubuntu 14.04, dualcore cpu, 512MB RAM.
MySQL
InnoDB

Even offtopic: Ik dacht dat MySQL onder Linux minimaal 2 GB RAM als requirement had ?

[Xudonax]

512MB RAM is erg weinig, zeker voor een dergelijke setup. Zelf zou ik voor zoiets Solr of ElasticSearch aanraden, maar deze gebruiken erg veel RAM. 2GB in totaal en 1GB voor Solr/ElasticSearch is niet overbodig. Heb zelf een Solr draaien voor ~300.000 entries en deze lust al snel 6GB RAM om snel te zijn. Minder RAM is niet erg, maar kost je snelheid.

(My)SQL kan zoiets wel, maar het is er niet echt voor gemaakt om dit efficient te kunnen IMO.

[JJ93]

dev10 schreef op maandag 09 februari 2015 @ 11:12:
Zoals altijd met dit soort dingen: meten is weten. Kijk met EXPLAIN waar de queries precies mee bezig zijn en ga kijken waar de bottlenecks zitten.

Sowieso denk ik dat het een idee is om wat meer geheugen in je server te stoppen, want 512 MB is bar weinig.

Ik wil het best gaan meten maar dan wil ik eerst de structuur goed hebben. Als het overduidelijk is dat ik een ander engine, dbms of de data moet structureren in verschillende tabellen dan is het zonde van mijn tijd om nu allemaal testjes uit te voeren.

Breezers schreef op maandag 09 februari 2015 @ 11:15:
[...]


Even offtopic: Ik dacht dat MySQL onder Linux minimaal 2 GB RAM als requirement had ?

Server is eigenlijk niet het juiste woord, VPS van http://buyvm.net/

Xudonax schreef op maandag 09 februari 2015 @ 11:21:
512MB RAM is erg weinig, zeker voor een dergelijke setup. Zelf zou ik voor zoiets Solr of ElasticSearch aanraden, maar deze gebruiken erg veel RAM. 2GB in totaal en 1GB voor Solr/ElasticSearch is niet overbodig. Heb zelf een Solr draaien voor ~300.000 entries en deze lust al snel 6GB RAM om snel te zijn. Minder RAM is niet erg, maar kost je snelheid.

(My)SQL kan zoiets wel, maar het is er niet echt voor gemaakt om dit efficient te kunnen IMO.

Hm met een factor 40 extra aan entries gaat 512MB dus zeker niet goed werken. ElasticSearch lijkt mij in ieder geval een goede kandidaat.

[Voutloos]

JJ93 schreef op maandag 09 februari 2015 @ 11:25:
[...]

Ik wil het best gaan meten maar dan wil ik eerst de structuur goed hebben. Als het overduidelijk is dat ik een ander engine, dbms of de data moet structureren in verschillende tabellen dan is het zonde van mijn tijd om nu allemaal testjes uit te voeren.

Neehee. Je moet _nu_ je bottleneck meten. Als (bijvoorbeeld!) mysql bij laaaange na niet de indices in 't geheugen kan houden kan een andere db dat wellicht ook niet.

En afhankelijk van het probleem kan ook de oplossing varieren. Stel, het tellen 'moet maar eens in de zoveel tijd' dan is geaggregeerd opslaan of cachen misschien wel een mooie eerste stap. Genoeg apps waarbij het niet uit maakt dat er een minuutje te lang '3.132.626 resultaten' staat terwijl het er stiekem inmiddels 3.132.627 zijn.

[ Voor 23% gewijzigd door Voutloos op 09-02-2015 11:42 ]

[kwaakvaak_v2]

Dit schreeuwt eigenlijk om een dedicated search server, wat je zelf eigenlijk ook al concludeerde

ElasticSearch of Solr (beide gebaseerd op de Lucene engine) Maar je gaat dat denk ik niet redden op een VPS met 512Mb geheugen. Tenminste als performance een issue is, is geheugen best fijn.

Elastic is het hipste jongetje van de klas, en is een stuk makkelijk te schalen/clusteren dan Solr, plus het heeft een zeer goede REST API met JSON.

Persoonlijk zou ik dus een test doen om te kijken of je de dataset in indexbaar kunt krijgen met ES, kost je hooguit een dagje spelen en weet je waar je aan toe bent. Heb je imho meer aan dan kijken of je het in mySQL voor elkaar kunt krijgen met indexes etc. Er is immers een reden dat er voor zoeken specifieke search-server software bestaat.
En dat is toch ook het mooie van de huidige webstack, je gebruikt de tools die er geschikt voor zijn ipv alles maar met 1 tool proberen op te lossen omdat, dat het enige is wat je hebt. (iets met hamers en dat alles dan een spijker is )

[JJ93]

Voutloos schreef op maandag 09 februari 2015 @ 11:39:
[...]
Neehee. Je moet _nu_ je bottleneck meten. Als (bijvoorbeeld!) mysql bij laaaange na niet de indices in 't geheugen kan houden kan een andere db dat wellicht ook niet.

En afhankelijk van het probleem kan ook de oplossing varieren. Stel, het tellen 'moet maar eens in de zoveel tijd' dan is geaggregeerd opslaan of cachen misschien wel een mooie eerste stap. Genoeg apps waarbij het niet uit maakt dat er een minuutje te lang '3.132.626 resultaten' staat terwijl het er stiekem inmiddels 3.132.627 zijn.

Ok, klopt! In dit geval hoeft de data ook niet realtime te zijn, zelfs één update per dag zou voldoen. Maar met 40 kolommen kan je een heleboel combinaties maken om te cachen. Maar ik verwacht dat gebruikers eigenlijk vooral 5 kolommen gebruiken, de andere 35 zijn vrij specifiek.

Dus de counts voor alle combinaties met die 5 kolommen opslaan zou wel kunnen. Het uniek selecteren van waardes gaat wel lekker snel. Alleen dus de counts niet. Misschien ga ik eerst maar gebruik maken van alleen de waardes, alfabetisch gesorteerd.

Als blijkt dat er wat gebruikers op m'n site komen dan kan ik ook de overstap maken naar een flinke server of wat dan ook nodig is.

kwaakvaak_v2 schreef op maandag 09 februari 2015 @ 11:49:
Dit schreeuwt eigenlijk om een dedicated search server, wat je zelf eigenlijk ook al concludeerde

ElasticSearch of Solr (beide gebaseerd op de Lucene engine) Maar je gaat dat denk ik niet redden op een VPS met 512Mb geheugen. Tenminste als performance een issue is, is geheugen best fijn.

Elastic is het hipste jongetje van de klas, en is een stuk makkelijk te schalen/clusteren dan Solr, plus het heeft een zeer goede REST API met JSON.

Persoonlijk zou ik dus een test doen om te kijken of je de dataset in indexbaar kunt krijgen met ES, kost je hooguit een dagje spelen en weet je waar je aan toe bent. Heb je imho meer aan dan kijken of je het in mySQL voor elkaar kunt krijgen met indexes etc. Er is immers een reden dat er voor zoeken specifieke search-server software bestaat.
En dat is toch ook het mooie van de huidige webstack, je gebruikt de tools die er geschikt voor zijn ipv alles maar met 1 tool proberen op te lossen omdat, dat het enige is wat je hebt. (iets met hamers en dat alles dan een spijker is )

Om het echt helemaal goed te doen denk ik ook dat ik een dedicated server nodig heb met een flink aantal gigabytes RAM. Maar als ik de counts achterwegen laat kan ik volgens mij al een heel eind komen met mijn huidige setup.

Ik zal eens kijken hoe ver ik kan komen met verschillende MySQL engines, indices en ordening van gegevens. Plus ook kijken naar ElasticSearch, kan ik wel lokaal draaien op mijn laptop met 8GB RAM om er eens mee te spelen. Thx!

[Cartman!]

Dit moet je niet met MySQL willen doen. Persoonlijk hele goede ervaringen met CloudSearch van AWS maar afhankelijk van de hoeveelheid data kan dat duurder zijn dan zelf een server beheren. Ik vind het erg lekker dat de software en hardware wordt beheerd door iemand anders en ik me niet druk hoef te maken over updates enzo.

[creator1988]

Hoeveel unieke facetten krijg je? Als het er een paar honderd zijn kan je je tabel gewoon eens per dag platslaan. Alle facetten als bit opslaan (elk facet een kolom) en op elke kolom een index. En dan alleen op die kolom zoeken.

500 facetten sla je zo op in 63 bytes, dus dat kost nauwelijks memory.

[ACM]

creator1988 schreef op maandag 09 februari 2015 @ 17:14:
Hoeveel unieke facetten krijg je? Als het er een paar honderd zijn kan je je tabel gewoon eens per dag platslaan. Alle facetten als bit opslaan (elk facet een kolom) en op elke kolom een index. En dan alleen op die kolom zoeken.

500 facetten sla je zo op in 63 bytes, dus dat kost nauwelijks memory.

Maar MySQL biedt nou ook weer geen hele efficiente methodes om bitmap-indexes te gebruiken. PostgreSQL heeft wel bitmap-indices (en kan ze ook combineren), maar het blijft een sql-toepassing die mogelijk niet helemaal past bij de use case.

Lucene (en Solr en Elasticsearch) gebruikt ook bitmaps voor de relatie tussen welk document welke term bevat (en dus welke facet-optie er voorkomt).

JJ93 schreef op maandag 09 februari 2015 @ 10:57:
DUS: Zou ik hier bijvoorbeeld ElasticSearch voor gebruiken of NoSQL o.i.d.? Moet ik mijn data gaan verspreiden over meerdere tabellen, een andere engine gebruiken of MySQL op een andere manier optimaliseren? Of is mijn server gewoon te licht voor 12 miljoen records?

De server is sowieso nogal licht voor die hoeveelheid data, als die indexen niet in RAM passen, dan wordt het al snel traag.
Maar de wijze waarop de data opgeslagen wordt (en in indexen staat) kan ook flink wat invloed hierop hebben; hoe compacter dat is, hoe minder RAM je relatief gezien nodig hebt. Elasticsearch (en andere lucene-gebaseerde producten) zijn erg goed in opslaan en opzoeken van 'document/item X heeft elementen/facet A, C en D' want dat is effectief hetzelfde als een term-document relatie.

En ze zijn "tegenwoordig" (lees: al een paar jaar) ook steeds uitgebreider geworden in situaties voor facetted search. Sterker nog, dat was een van de nadrukkelijk aanwezige key features van elasticsearch sinds ze begonnen.
Mede daardoor is ook het aantal 'round trips' naar je database een stuk lager (je doet in principe maar 1 query die ook gelijk de facetten bij het resultaat meekrijgt).

[ Voor 4% gewijzigd door ACM op 09-02-2015 19:49 ]

[MSalters]

Ik mis vermoedelijk iets, maar als ik "25 seconde seconden" hoor dan denk ik "full table scan", wat voor de simpele query in de TS eigenlijk alleen kan gebeuren als er uberhaupt geen index is.

Nu zou je met 40 indices inderdaad wel een klein RAM probleem kunnen hebben: 40 * 12 miljoen is 480 miljoen entries, dat is 2 GB aan indices. Alle indexen in RAM lukt niet, maar is dat uberhaupt nodig? 35 van die indexen heb je blijkbaar zelden nodig, en 1/8 zou in 256 MB RAM moeten passen. Heb je een extra index nodig, dan heb je een disk I/O.

Kijk, voor grotere collecties moet je je gaan afvragen of SQL wel de beste oplossing is, maar 12 miljoen rijen is niet echt een groot aantal..

[JJ93]

Cartman! schreef op maandag 09 februari 2015 @ 15:50:
Dit moet je niet met MySQL willen doen. Persoonlijk hele goede ervaringen met CloudSearch van AWS maar afhankelijk van de hoeveelheid data kan dat duurder zijn dan zelf een server beheren. Ik vind het erg lekker dat de software en hardware wordt beheerd door iemand anders en ik me niet druk hoef te maken over updates enzo.

Ik vind het op zich ook wel weer leuk om alles zelf te bouwen en te beheren voor de ervaring. Maar het laten beheren is ook interessant natuurlijk.

creator1988 schreef op maandag 09 februari 2015 @ 17:14:
Hoeveel unieke facetten krijg je? Als het er een paar honderd zijn kan je je tabel gewoon eens per dag platslaan. Alle facetten als bit opslaan (elk facet een kolom) en op elke kolom een index. En dan alleen op die kolom zoeken.

500 facetten sla je zo op in 63 bytes, dus dat kost nauwelijks memory.

40 kolommen totaal, dus ook 40 facet blokken
De facet blokken zelf hebben of checkboxes voor de unieke strings, of sliders voor min/max int&float values. Plus nog vier van - tot datum pickers om te filteren op een bepaalde datum.

Maar het gaat vooral om de combinatie van de facetten. Als er een facet A een optie wordt geselecteerd moeten alle andere facetten geupdate worden met nieuwe waardes die gelden voor de geselecteerde waarde A.

Je krijgt dus een enorme hoeveelheid combinaties. De counts verschillen voor elke combinatie. Als ik de counts achterwegen laat is het niet een echte facetted search maar kan het wel werken met mijn huidige opstelling. Als dan blijkt dat er nog interesse is kan ik overgaan op een andere opstelling voor meer geld.

[JJ93]

ACM schreef op maandag 09 februari 2015 @ 19:47:
[...]

Maar MySQL biedt nou ook weer geen hele efficiente methodes om bitmap-indexes te gebruiken. PostgreSQL heeft wel bitmap-indices (en kan ze ook combineren), maar het blijft een sql-toepassing die mogelijk niet helemaal past bij de use case.

Lucene (en Solr en Elasticsearch) gebruikt ook bitmaps voor de relatie tussen welk document welke term bevat (en dus welke facet-optie er voorkomt).


[...]

De server is sowieso nogal licht voor die hoeveelheid data, als die indexen niet in RAM passen, dan wordt het al snel traag.
Maar de wijze waarop de data opgeslagen wordt (en in indexen staat) kan ook flink wat invloed hierop hebben; hoe compacter dat is, hoe minder RAM je relatief gezien nodig hebt. Elasticsearch (en andere lucene-gebaseerde producten) zijn erg goed in opslaan en opzoeken van 'document/item X heeft elementen/facet A, C en D' want dat is effectief hetzelfde als een term-document relatie.

En ze zijn "tegenwoordig" (lees: al een paar jaar) ook steeds uitgebreider geworden in situaties voor facetted search. Sterker nog, dat was een van de nadrukkelijk aanwezige key features van elasticsearch sinds ze begonnen.
Mede daardoor is ook het aantal 'round trips' naar je database een stuk lager (je doet in principe maar 1 query die ook gelijk de facetten bij het resultaat meekrijgt).

Door wat hieronder (en hierboven) wordt opgemerkt kan ik in ieder geval concluderen dat meer RAM de snelheid zeker ten goede komt. ElasticSearch lijkt mij ook een goede keuze in dit geval. En ook een efficientere keuze qua hardware gebruik maar ook qua 'round trips' dus.

MSalters schreef op dinsdag 10 februari 2015 @ 11:19:
Ik mis vermoedelijk iets, maar als ik "25 seconde seconden" hoor dan denk ik "full table scan", wat voor de simpele query in de TS eigenlijk alleen kan gebeuren als er uberhaupt geen index is.

Nu zou je met 40 indices inderdaad wel een klein RAM probleem kunnen hebben: 40 * 12 miljoen is 480 miljoen entries, dat is 2 GB aan indices. Alle indexen in RAM lukt niet, maar is dat uberhaupt nodig? 35 van die indexen heb je blijkbaar zelden nodig, en 1/8 zou in 256 MB RAM moeten passen. Heb je een extra index nodig, dan heb je een disk I/O.

Kijk, voor grotere collecties moet je je gaan afvragen of SQL wel de beste oplossing is, maar 12 miljoen rijen is niet echt een groot aantal..

Ik had zelf ook niet het idee dat 12 miljoen rijen echt zoveel is. Meten = weten moet ik gaan toepassen. En het optimaliseren van mijn db structuur en indices. Dus ik ga vandaag een aan de slag met het testen van verbeterde indices en ook de invloed van RAM op de performance.

Zal alles eens importeren in een MySQL db op mijn laptop met 8GB RAM en zien wat er dan gebeurd met de snelheid.

[Solopher]

Ik heb laatst ongeveer hetzelfde gedaan, gebruik gemaakt van elasticsearch aggregations.
http://www.elasticsearch..../search-aggregations.html

Eerst haal ik al mijn aggregations op, hier staan ook de buckets in. Dit cache is wel in mijn intern geheugen met een duur van 5 minuten.

Vervolgens als er gezocht wordt houd ik twee soorten filters bij:
Orig en een TermsFilter.

Orig houd ik bij om nog te kunnen laten zien wat er eventueel in de andere categorieen zit als je maar 1 categorie selecteert. Zodra je een subselect in een categorie maakt dan worden de waarden in orig allemaal 0 behalve die van de geselecteerde hoofdcategorie.

Dit draait inmiddels een maand of 3 naar volle tevredenheid.

[cariolive23]

12 miljoen records stelt niet zoveel voor, al ben je natuurlijk afhankelijk van de snelheid van de machine. IO en RAM zijn daarbij key.

Maar, wanneer ik iets lees over 40 kolommen in een MySQL-database, bekruipt mij al snel het gevoel dat er niet is genormaliseerd en daarbij dus ook niet is geoptimaliseerd voor een RDBMS. Ik ken jouw data niet, ik kan het dus niet zeker weten, maar ik ben wel benieuwd. Je kunt een schroef prima met een hamer in het hout slaan, maar het is niet optimaal. Over het algemeen bereik je met een schroevendraaier een veel beter resultaat.... Zo ook met een database, een RDBMS werkt het beste wanneer de data is genormaliseerd. Ook wanneer de hardware resources beperkt zijn.

[BasieP]

Hoi hoi,

Ik wil 2 dingen roepen:

1. 12 miljoen records is peanuts. Ga vooral niet voor Hadoop omdat hadoop. Dat is meelopen met de hype.
(lees dit bijv: http://aadrake.com/comman...-your-hadoop-cluster.html)

2. ik geloof niet in een dataset van 40 kolommen. Normaliseer dat uit. Zoek uit op welke je wilt kunnen zoeken, zorg dat je met weinig I/O door die kolommen heen kan.

Als (maar dat geloof ik niet) je echt op alle 40 (of veel) kolommen wilt gaan zoeken kies dan vooral voor een column based database (Sybase IQ of MS SQL Server bijvoorbeeld. Beide hebben erg goede resultaten)
Kies vooral niet voor een 'normale' database (MySQL, Oracle, etc.)

[ Voor 7% gewijzigd door BasieP op 10-02-2015 18:57 ]

[kwaakvaak_v2]

uuh.. BasieP, waar komt die Hadoop ineens vandaan?

[emnich]

Er valt met de informatie die je geeft niet zo heel veel zinnigs te zeggen. We hebben geen idee over het type data en de tabel structuur. 12 Miljoen records is ook voor MySQL echt helemaal niets.

512MB lijkt wel aan de erg krappe kant maar zelfs dat hoeft niet te betekenen dat de queries zó lang duren. Meer resources zijn nooit een goede oplossing voor slechte code/data.

[ACM]

cariolive23 schreef op dinsdag 10 februari 2015 @ 14:48:
12 miljoen records stelt niet zoveel voor

BasieP schreef op dinsdag 10 februari 2015 @ 18:55:
1. 12 miljoen records is peanuts.

emnich schreef op woensdag 11 februari 2015 @ 10:57:
12 Miljoen records is ook voor MySQL echt helemaal niets.

Hoewel ik met jullie eens ben dat 12 miljoen niet iets is waar MySQL per se enorm veel moeite heeft, is het nou niet bepaald een triviale hoeveelheid records.

Als je daarop queries doet die veel van de records aanraakt, moet je niet verbaasd zijn als dat queries van veel meer dan enkele millisecondes zijn. Zeker niet als je te maken hebt met wat complexere statements.
Indexen kunnen zoals gebruikelijk veel helpen, maar ook daarbij geldt dat 12 miljoen records geen triviale hoeveelheid is. Indexen zijn vooral zinvol als je daarmee tot een kleine subset van de records kunt komen, bijvoorbeeld alle records voor een bepaald id of zelfs specifiek het records behorende bij een primaire-sleutelwaarde.
Pas dan heb je pas echt baat bij de O(log-n) werking van b-trees enzo. In de hier genoemde scenario's heb je mogelijk vooral baat bij het feit dat de waardes er gesorteerd in staan en er minder I/O nodig is om alle data door te kijken.

Bij het bepalen van facet-statistieken wil je - neem ik aan - effectief voor elke kolom weten hoeveel records er gaan overblijven als je een te kiezen optie combineert met de reeds opgebouwde query. Die resultaten van je query krijgen kan relatief snel zijn door indexen te combineren, maar vervolgens daar weer alle mogelijke waarden van afleiden... daar zal MySQL toch echt over het algemeen alle resulterende records voor door moeten scannen, wat het automatisch geen heel triviale klus meer maakt.

Bovendien is dit typisch een query die in MySQL juist trager kan worden zodra je velden begint te selecteren - zeker als ze nog altijd een groot deel van alle records opleveren - omdat dan minder efficient puur de indexen uitgelezen kunnen worden ('t voorbeeld uit de topicstart kan puur uit een index op 'field' worden afgeleid, een met een where-clause niet meer).

Met 40 kolommen betekent dat je die query uiteindelijk 40x moet herhalen, steeds met een andere grouping. Het zou mij eerlijk gezegd niet verbazen als onze databaseserver (2x Intel E5-2643 met 3,3Ghz cores, 256GB ram en SSD's) ook ruim boven de 1 seconde blijft uitkomen, zeker met de varianten die niet zo makkelijk puur op basis van indexen kunnen werken.

[The Eagle]

kwaakvaak_v2 schreef op dinsdag 10 februari 2015 @ 20:00:
uuh.. BasieP, waar komt die Hadoop ineens vandaan?

Geen idee, maar het was gezien de casus wel het eerste waar ik aan dacht. Hadoop (single instance of cluster en dan Hive er op.
Is alleen niet 123 even opgezet. Maar je kunt bij bijv Cloudera even een VM downloaden met Hadoop, hive en whatever more om mee te testen natuurlijk

[Gomez12]

ACM schreef op woensdag 11 februari 2015 @ 20:23:
[...]
Met 40 kolommen betekent dat je die query uiteindelijk 40x moet herhalen, steeds met een andere grouping. Het zou mij eerlijk gezegd niet verbazen als onze databaseserver (2x Intel E5-2643 met 3,3Ghz cores, 256GB ram en SSD's) ook ruim boven de 1 seconde blijft uitkomen, zeker met de varianten die niet zo makkelijk puur op basis van indexen kunnen werken.

Maar dat is enkel relevant als je 40 kolommen wilt blijven hanteren.

Een alternatief zou bijv kunnen zijn om die 40 kolommen af te splitsen naar een 2e tabel met een 1:n(/40) verhouding waardoor je 40 query's vervangt door 1 query.
Je praat dan wel over maximaal 480 miljoen records (alhoewel ik betwijfel of alle 40 kolommen altijd gevuld zijn) maar je hebt maar 1 query en grote kans dat je mysql een veel beter query-execution en caching mechanisme kan hanteren.
Heb je verschillende data-types in die 40 kolommen dan ga je wat extra dingen krijgen, maar het principe blijft hetzelfde.

Oftewel je kan ook nog wel wat winnen door je data-model aan te passen zodat die beter past bij je uiteindelijke doel.
Maar op zichzelf zou ik de easy way out pakken en gewoon investeren in RAM en elasticsearch / solr ervoor inzetten, die hebben allerlei interne optimalisaties die je in mysql zelf zou moeten nabouwen.

[JJ93]

Solopher schreef op dinsdag 10 februari 2015 @ 13:12:
Ik heb laatst ongeveer hetzelfde gedaan, gebruik gemaakt van elasticsearch aggregations.
http://www.elasticsearch..../search-aggregations.html

Eerst haal ik al mijn aggregations op, hier staan ook de buckets in. Dit cache is wel in mijn intern geheugen met een duur van 5 minuten.

Vervolgens als er gezocht wordt houd ik twee soorten filters bij:
Orig en een TermsFilter.

Orig houd ik bij om nog te kunnen laten zien wat er eventueel in de andere categorieen zit als je maar 1 categorie selecteert. Zodra je een subselect in een categorie maakt dan worden de waarden in orig allemaal 0 behalve die van de geselecteerde hoofdcategorie.

Dit draait inmiddels een maand of 3 naar volle tevredenheid.

Dit lijkt mij ook een goede opzet voor mijn doel. Hoeveel records heb jij ongeveer en welke specs heeft de machine waarop alles draait?

cariolive23 schreef op dinsdag 10 februari 2015 @ 14:48:
12 miljoen records stelt niet zoveel voor, al ben je natuurlijk afhankelijk van de snelheid van de machine. IO en RAM zijn daarbij key.

Maar, wanneer ik iets lees over 40 kolommen in een MySQL-database, bekruipt mij al snel het gevoel dat er niet is genormaliseerd en daarbij dus ook niet is geoptimaliseerd voor een RDBMS. Ik ken jouw data niet, ik kan het dus niet zeker weten, maar ik ben wel benieuwd. Je kunt een schroef prima met een hamer in het hout slaan, maar het is niet optimaal. Over het algemeen bereik je met een schroevendraaier een veel beter resultaat.... Zo ook met een database, een RDBMS werkt het beste wanneer de data is genormaliseerd. Ook wanneer de hardware resources beperkt zijn.

Het is op dit moment inderdaad niet genormaliseerd. Ik ben aan het uitzoeken wat precies het handigste is om te doen en wat ik nou precies wil. Ik wil in ieder geval een zo uitgebreid mogelijke facetted search maken. Sommige facets worden misschien niet vaak gebruikt, maar het moet wel kunnen.

ACM schreef op woensdag 11 februari 2015 @ 20:23:
Hoewel ik met jullie eens ben dat 12 miljoen niet iets is waar MySQL per se enorm veel moeite heeft, is het nou niet bepaald een triviale hoeveelheid records.

Als je daarop queries doet die veel van de records aanraakt, moet je niet verbaasd zijn als dat queries van veel meer dan enkele millisecondes zijn. Zeker niet als je te maken hebt met wat complexere statements.
Indexen kunnen zoals gebruikelijk veel helpen, maar ook daarbij geldt dat 12 miljoen records geen triviale hoeveelheid is. Indexen zijn vooral zinvol als je daarmee tot een kleine subset van de records kunt komen, bijvoorbeeld alle records voor een bepaald id of zelfs specifiek het records behorende bij een primaire-sleutelwaarde.
Pas dan heb je pas echt baat bij de O(log-n) werking van b-trees enzo. In de hier genoemde scenario's heb je mogelijk vooral baat bij het feit dat de waardes er gesorteerd in staan en er minder I/O nodig is om alle data door te kijken.

Bij het bepalen van facet-statistieken wil je - neem ik aan - effectief voor elke kolom weten hoeveel records er gaan overblijven als je een te kiezen optie combineert met de reeds opgebouwde query. Die resultaten van je query krijgen kan relatief snel zijn door indexen te combineren, maar vervolgens daar weer alle mogelijke waarden van afleiden... daar zal MySQL toch echt over het algemeen alle resulterende records voor door moeten scannen, wat het automatisch geen heel triviale klus meer maakt.

Bovendien is dit typisch een query die in MySQL juist trager kan worden zodra je velden begint te selecteren - zeker als ze nog altijd een groot deel van alle records opleveren - omdat dan minder efficient puur de indexen uitgelezen kunnen worden ('t voorbeeld uit de topicstart kan puur uit een index op 'field' worden afgeleid, een met een where-clause niet meer).

Met 40 kolommen betekent dat je die query uiteindelijk 40x moet herhalen, steeds met een andere grouping. Het zou mij eerlijk gezegd niet verbazen als onze databaseserver (2x Intel E5-2643 met 3,3Ghz cores, 256GB ram en SSD's) ook ruim boven de 1 seconde blijft uitkomen, zeker met de varianten die niet zo makkelijk puur op basis van indexen kunnen werken.

Ik merk in ieder geval een erg groot (enorm) verschil tussen WHERE clauses waar 1m rijen onder vallen en WHERE clauses waar je maar zo'n 1k resultaten terug krijgt. Dus alle rijen uit de query moeten doorlopen worden om vervolgens de unieke waardes te vinden.

Bij de facet-statistieken wil ik inderdaad na elke selectie alles updaten met de nieuwe beschikbare nummers.

Gomez12 schreef op woensdag 11 februari 2015 @ 21:16:
[...]

Maar dat is enkel relevant als je 40 kolommen wilt blijven hanteren.

Een alternatief zou bijv kunnen zijn om die 40 kolommen af te splitsen naar een 2e tabel met een 1:n(/40) verhouding waardoor je 40 query's vervangt door 1 query.
Je praat dan wel over maximaal 480 miljoen records (alhoewel ik betwijfel of alle 40 kolommen altijd gevuld zijn) maar je hebt maar 1 query en grote kans dat je mysql een veel beter query-execution en caching mechanisme kan hanteren.
Heb je verschillende data-types in die 40 kolommen dan ga je wat extra dingen krijgen, maar het principe blijft hetzelfde.

Oftewel je kan ook nog wel wat winnen door je data-model aan te passen zodat die beter past bij je uiteindelijke doel.
Maar op zichzelf zou ik de easy way out pakken en gewoon investeren in RAM en elasticsearch / solr ervoor inzetten, die hebben allerlei interne optimalisaties die je in mysql zelf zou moeten nabouwen.

Hier zat ik inderdaad ook naar te kijken, wordt gebruikt in deze tutorial ook.

Alle 40 kolommen zijn niet altijd gevuld. Om iedereen nog een beter beeld te geven van de data: het gaat om RDW voertuig data. Ik heb dus een CSV bestandje met alle data, gebruikt dezelfde kolom namen als in de code hieronder. Behalve dan ID.

Dus ik ben vooral nog aan het expirementeren hoe ik alles het beste kan importeren, met welke tabellen structuur en welk DBMS om facetted search met zo veel mogelijk facetten te bouwen.

SQL:

CREATE TABLE voertuig (
    ID                  INT AUTO_INCREMENT,
    Aantalcilinders             INT DEFAULT NULL,
    Aantalstaanplaatsen         INT DEFAULT NULL,
    Aantalzitplaatsen           INT DEFAULT NULL,
    BPM                 INT DEFAULT NULL,
    Brandstofverbruikbuitenweg      FLOAT DEFAULT NULL,
    Brandstofverbruikgecombineerd       FLOAT DEFAULT NULL,
    Brandstofverbruikstad           FLOAT DEFAULT NULL,
    Catalogusprijs              FLOAT DEFAULT NULL,
    Cilinderinhoud              INT DEFAULT NULL,
    CO2uitstootgecombineerd         INT DEFAULT NULL,
    Datumaanvangtenaamstelling      DATETIME DEFAULT NULL,
    DatumeersteafgifteNederland     DATETIME DEFAULT NULL,
    Datumeerstetoelating            DATETIME DEFAULT NULL,
    Eerstekleur             VARCHAR(255) DEFAULT NULL,
    G3installatie               VARCHAR(255) DEFAULT NULL,
    Handelsbenaming             VARCHAR(255) DEFAULT NULL,
    Hoofdbrandstof              VARCHAR(255) DEFAULT NULL,
    Inrichting              VARCHAR(255) DEFAULT NULL,
    Kenteken                VARCHAR(255) DEFAULT NULL,
    Laadvermogen                INT DEFAULT NULL,
    Massaleegvoertuig           INT DEFAULT NULL,
    Massarijklaar               INT DEFAULT NULL,
    Maximaleconstructiesnelheid     INT DEFAULT NULL,
    Maximumtetrekkenmassaautonoomgeremd INT DEFAULT NULL,
    Maximumtetrekkenmassageremd     INT DEFAULT NULL,
    Maximumtetrekkenmassamiddenasgeremd INT DEFAULT NULL,
    Maximumtetrekkenmassaongeremd       INT DEFAULT NULL,
    Maximumtetrekkenmassaopleggergeremd INT DEFAULT NULL,
    Merk                    VARCHAR(255) DEFAULT NULL,
    Milieuclassificatie         VARCHAR(255) DEFAULT NULL,
    Nevenbrandstof              VARCHAR(255) DEFAULT NULL,
    Retrofitroetfilter          VARCHAR(255) DEFAULT NULL,
    Toegestanemaximummassavoertuig      INT DEFAULT NULL,
    Tweedekleur             VARCHAR(255) DEFAULT NULL,
    Vermogen                INT DEFAULT NULL,   
    Vermogenbromsnorfiets           INT DEFAULT NULL,
    VervaldatumAPK              DATETIME DEFAULT NULL,
    Voertuigsoort               VARCHAR(255) DEFAULT NULL,
    Wachtopkeuren               VARCHAR(255) DEFAULT NULL,
    WAMverzekerdgeregistreerd       VARCHAR(255) DEFAULT NULL,
    Zuinigheidslabel            VARCHAR(255) DEFAULT NULL,

    PRIMARY KEY (id),
    INDEX Aantalcilinders (Aantalcilinders),
    INDEX Aantalstaanplaatsen (Aantalstaanplaatsen),
    INDEX Aantalzitplaatsen (Aantalzitplaatsen),
    INDEX BPM (BPM),
    INDEX Brandstofverbruikgecombineerd (Brandstofverbruikgecombineerd),
    INDEX Brandstofverbruikstad (Brandstofverbruikstad),
    INDEX Catalogusprijs (Catalogusprijs),
    INDEX Cilinderinhoud (Cilinderinhoud),
    INDEX CO2uitstootgecombineerd (CO2uitstootgecombineerd),
    INDEX Datumaanvangtenaamstelling (Datumaanvangtenaamstelling),
    INDEX DatumeersteafgifteNederland (DatumeersteafgifteNederland),
    INDEX Datumeerstetoelating (Datumeerstetoelating),
    INDEX Eerstekleur (Eerstekleur),
    INDEX G3installatie (G3installatie),
    INDEX Handelsbenaming (Handelsbenaming),
    INDEX Hoofdbrandstof (Hoofdbrandstof),
    INDEX Inrichting (Inrichting),
    INDEX Kenteken (Kenteken),
    INDEX Laadvermogen (Laadvermogen),
    INDEX Massaleegvoertuig (Massaleegvoertuig),
    INDEX Massarijklaar (Massarijklaar),
    INDEX Maximaleconstructiesnelheid (Maximaleconstructiesnelheid),
    INDEX Maximumtetrekkenmassaautonoomgeremd (Maximumtetrekkenmassaautonoomgeremd),
    INDEX Maximumtetrekkenmassageremd (Maximumtetrekkenmassageremd),
    INDEX Maximumtetrekkenmassamiddenasgeremd (Maximumtetrekkenmassamiddenasgeremd),
    INDEX Maximumtetrekkenmassaongeremd (Maximumtetrekkenmassaongeremd),
    INDEX Maximumtetrekkenmassaopleggergeremd (Maximumtetrekkenmassaopleggergeremd),
    INDEX Merk (Merk),
    INDEX Milieuclassificatie (Milieuclassificatie),
    INDEX Nevenbrandstof (Nevenbrandstof),
    INDEX Retrofitroetfilter (Retrofitroetfilter),
    INDEX Toegestanemaximummassavoertuig (Toegestanemaximummassavoertuig),
    INDEX Tweedekleur (Tweedekleur),
    INDEX Vermogen (Vermogen),
    INDEX Vermogenbromsnorfiets (Vermogenbromsnorfiets),
    INDEX VervaldatumAPK (VervaldatumAPK),
    INDEX Voertuigsoort (Voertuigsoort),
    INDEX Wachtopkeuren (Wachtopkeuren),
    INDEX WAMverzekerdgeregistreerd (WAMverzekerdgeregistreerd),
    INDEX Zuinigheidslabel (Zuinigheidslabel)

)

Wat ik uiteindelijk dus wil is:
1. Voertuig informatie weergeven op kenteken
2. API call om voertuig gegevens op te halen
3. Facetted search op de homepage om voertuigen te vinden die voldoen aan de gekozen facet opties
4. Facetted search API call zodat ik deze ook kan opbouwen in een Android en iOS app

Nummer 1 en 2 zijn af. De Android app staat ook in de Playstore. Heb hem van de grond af aangebouwd en gebruikt nu de RDW API. Maar ik wil toch minder afhankelijk zijn van derde partijen en ben nu dit alles dus in elkaar in het knutselen. Het geavanceerd zoeken kan ook niet met de RDW API. Alleen op één kolom met de exacte waarde. Mercedes op kolom MERK vind je niks, Mercedes-Benz wel.

In ieder geval de hardware eisen onderschat. Ik dacht dat 12 mln records niet heel veel voorstelde. Het selecteren van kentekens gaat ook zonder problemen. Maar het tellen van unieke combinaties met nog een aantal WHERE clauses is gewoon niet efficient met de huidige opzet. Moet alle rijen in de result set doorlopen en dan de uniques er uit filteren eigenlijk. Dus duurt met een query zoals

SQL:

SELECT Voertuigsoort FROM voertuig WHERE Inrichting='Hatchback' GROUP BY Voertuigsoort;

4 rows returned, 41.226 sec

SQL:

SELECT Handelsbenaming FROM voertuig WHERE Merk='Mazda' GROUP BY Handelsbenaming;

1000 rows returned, 16.538 sec
Als je de GROUP BY achterwege laat voor de unieke waardes duurt het nog maar 0.185s.

Zoiets als

SQL:

SELECT * FROM voertuig WHERE Kenteken='22TZZB';

Duurt slechts 0.050s zoals je ook zou verwachten.

SQL:

SELECT DISTINCT Nevenbrandstof FROM voertuig;

Unieke waardes zonder WHERE clause duurt ook slechts 0.035s

Het probleem is ten eerste dus het vinden van de unieke waardes met WHERE clause en ten tweede ook nog de counts er bij. Dus het oplossen hiervan: meer RAM, betere structuur/normaliseren, mogelijk ander DBMS... in ieder geval interessant en best lastig.

[Gomez12]

Een zinnige 1e stap zou imho zijn : Herschrijf je kolomtypes.

Ik ben niet zo bekend met wat er exact in de RDW-gegevens kan staan. Maar aantalcilinders kan volgens mij nooit negatief zijn en vermoedelijk zal die ook niet in de buurt van de 32765 komen, oftewel zoek een kleiner datatype.
Kentekens ken ik ook niet 100% uit mijn hoofd, maar ik heb ze nog nooit gezien met 255 tekens.
Een prijs is per definitie nooit een float.
Een wachtopkeuren klinkt mij in de oren als een boolean en niet als een varchar.
Een merk is zeer waarschijnlijk geen varchar maar beter te implementeren als een FK naar een losse merkentabel.

In wezen wil je je datatypes zo klein mogelijk hebben zodat er zoveel mogelijk in je geheugen passen (want je barst nu niet echt van het geheugen zeg maar en normaliter is geheugen 1000x sneller dan disk) idealiter krijg je je complete dbase in memory.

En dan heb je in je voorbeeld query's nog als additionele toevoegingen dat je veel lijkt te group by'en op varchars, dat is relatief traag, weet je die varchars weg te normaliseren dan kan je group by'en op FK's en dat is sneller / goedkoper.

En dan kan je nog overwegen om je group by's te versnellen door gecombineerde indexen te maken van je facetten en je group by-velden (maar dit levert snel een explosie van indexen op en dat kan weer tegen je werken met insert / update query's en dat is een beetje trial and error wat je hierbij moet toepassen niet alles is zinnig)

Daarnaast zou ik een aantal indexen laten vallen, wat is reeeel de kans dat iemand zoekt op een Datumaanvangtenaamstelling oftewel heb je daar wel een index op nodig (hij is niet echt duur, maar onzinnige indexen zijn en blijven onzinnig)

Oftewel ik zou eerst eens je datamodel gaan optimaliseren, ik denk dat daar al echt een heleboel performance uit te winnen is. Als je het huidige datamodel blind in elasticsearch / nosql stopt dan krijg je waarschijnlijk wel betere performance, maar alsnog geen top performance (en het kost je erg veel geheugen) omdat je datamodel gewoon ruk is.

[pedorus]

Misschien is dit een interessant artikel, over facetted search op funda.nl:
http://weblogs.asp.net/dr...arch-with-solr-on-windows
Of in NL, van creator1988 (zie boven): Coding Glamour: Solr, deel 1: Introductie tot faceted search
Daar zijn ze van sql naar solr overgestapt.

Nu is 12M records iets meer dan 250K, dus dat wordt testen. Solr works for me, maar heb het nooit uitgetest met zoveel facetten. En RAM kost weinig deze dagen, dus 512MB is echt niets.. Voor minder dan 50 euro per maand huur je een server met 32GB..

[Gomez12]

SolR en ElasticSearch zijn wmb niet zo verschillend.

Persoonlijk gaat mijn voorkeur tegenwoordig meer uit naar elasticsearch, maar aan de andere kant is voor mij het voornaamste verschil dat elasticsearch sneller indexeert en retourneert. Maar dat is meer omdat ik met "flexibele" data werk die op elk moment ingeschoten moet kunnen worden.
Bij een RDW bestand verwacht ik dat het meer statische data is (zeker qua schema wat je imho makkelijker kan optimaliseren in Solr) met periodieke updates.

Maar alletwee gebruiken ze lucene als onderliggende software en als je of voor solr of voor elasticsearch zou willen gaan dan zou ik me eerst even inlezen over de onderlinge verschillen want ze zijn op veel punten als redelijk "gelijk"

[pedorus]

Hier een site die beweert dat juist solr sneller is:
https://www.loggly.com/bl...-scalable-log-management/
(Maar ze kiezen voor elasticsearch vanwege datadistributie/dynamiek)

[ Voor 3% gewijzigd door pedorus op 12-02-2015 01:18 ]

[Gomez12]

Ik check het ook niet dagelijks meer (keuze is wmb gemaakt en op ES heb ik nu al dingen gebouwd) maar even uit mijn hoofd meende ik dat solr sneller was met bruut indexeren van zeg maar 100.000 items, maar dat je de resultaten pas kon zien na de complete indexatie terwijl ES die gewoon ook tussendoor toont.

Wat voor ons toendertijd de doorslag gaf omdat wij een redelijk continue hoge doorvoerstroom hebben en niet periodieke batches die goed ingepland geindexeerd kunnen worden. Voor ons is de totale batch niet zo relevant, voornamelijk de eerste zichtbaarheid van data.

Maar dit kan ondertussen ook weer omgedraaid zijn en sowieso weet ik niet hoe relevant het hier is (met RDW verwacht ik max iets van dagelijkse dumps die gewoon periodiek in te lezen zijn).
Het enige wat ik wilde aangeven is is dat ze imho niet zo verschillend zijn enkel dat je wel een keuze moet maken, dus eerst even inlezen. Want in het beginstadium vreten ze ongeveer dezelfde data,maar hoe verder je erin gaat hoe meer je de verschillen gaat zien en hoe minder je kan overswitchen.

[JJ93]

Gomez12 schreef op donderdag 12 februari 2015 @ 00:42:
Een zinnige 1e stap zou imho zijn : Herschrijf je kolomtypes.

Ik ben niet zo bekend met wat er exact in de RDW-gegevens kan staan. Maar aantalcilinders kan volgens mij nooit negatief zijn en vermoedelijk zal die ook niet in de buurt van de 32765 komen, oftewel zoek een kleiner datatype.
Kentekens ken ik ook niet 100% uit mijn hoofd, maar ik heb ze nog nooit gezien met 255 tekens.
Een prijs is per definitie nooit een float.
Een wachtopkeuren klinkt mij in de oren als een boolean en niet als een varchar.
Een merk is zeer waarschijnlijk geen varchar maar beter te implementeren als een FK naar een losse merkentabel.

In wezen wil je je datatypes zo klein mogelijk hebben zodat er zoveel mogelijk in je geheugen passen (want je barst nu niet echt van het geheugen zeg maar en normaliter is geheugen 1000x sneller dan disk) idealiter krijg je je complete dbase in memory.

En dan heb je in je voorbeeld query's nog als additionele toevoegingen dat je veel lijkt te group by'en op varchars, dat is relatief traag, weet je die varchars weg te normaliseren dan kan je group by'en op FK's en dat is sneller / goedkoper.

En dan kan je nog overwegen om je group by's te versnellen door gecombineerde indexen te maken van je facetten en je group by-velden (maar dit levert snel een explosie van indexen op en dat kan weer tegen je werken met insert / update query's en dat is een beetje trial and error wat je hierbij moet toepassen niet alles is zinnig)

Daarnaast zou ik een aantal indexen laten vallen, wat is reeeel de kans dat iemand zoekt op een Datumaanvangtenaamstelling oftewel heb je daar wel een index op nodig (hij is niet echt duur, maar onzinnige indexen zijn en blijven onzinnig)

Oftewel ik zou eerst eens je datamodel gaan optimaliseren, ik denk dat daar al echt een heleboel performance uit te winnen is. Als je het huidige datamodel blind in elasticsearch / nosql stopt dan krijg je waarschijnlijk wel betere performance, maar alsnog geen top performance (en het kost je erg veel geheugen) omdat je datamodel gewoon ruk is.

Helemaal gelijk, tot nu toe heb ik iets te makkelijk gedaan over mijn kolomtypes. Aantalcilinders mag inderdaad niet negatief zijn en de maximale waarde in de database is 99. Ze zullen zelf dus ook wel een limit gesteld hebben op 100. TINYINT UNSIGNED zal dus ook voldoen, 0-255

De drie waardes van Wachtopkeuren zijn Ja, N.V.T. en Nee. Dus in principe volstaat een BOOL ook wel.

De merken, kleuren en handelsbenamingen en meer kan ik allemaal wel normaliseren inderdaad. Valt zeker nog een heleboel te halen, en dit is het eerste waar ik mee bezig ga. Voor elke kolom kijken wat de beste oplossing is. Thnx!

pedorus schreef op donderdag 12 februari 2015 @ 00:49:
Misschien is dit een interessant artikel, over facetted search op funda.nl:
http://weblogs.asp.net/dr...arch-with-solr-on-windows
Of in NL, van creator1988 (zie boven): Coding Glamour: Solr, deel 1: Introductie tot faceted search
Daar zijn ze van sql naar solr overgestapt.

Nu is 12M records iets meer dan 250K, dus dat wordt testen. Solr works for me, maar heb het nooit uitgetest met zoveel facetten. En RAM kost weinig deze dagen, dus 512MB is echt niets.. Voor minder dan 50 euro per maand huur je een server met 32GB..

Interest, de structuur die ze binnen hun SQL tabellen gebruikten lijkt wel op mijn huidige structuur. Ten eerste ga ik mijn kolommen verbeteren en data normaliseren. Maar met 12 miljoen records is het nog maar de vraag of de performance hoog genoeg is hierna. Maar in ieder geval een goede stap voordat ik kijk naar alternatieven zoals SOLR of ElasticSearch.

Vereiste RAM inderdaad flink onderschat. Meeste (school) projecten werken we met een 512MB VPS en dat volstaat prima met iets simpels. Maar dit is toch een hoop complexer en niet een paar honderd to duizend rijen met data maar miljoenen rijen. En geen goed schema. Ik zou best een server willen huren met 32GB RAM voor 50 euro als ik dit geld ook terug zie aan inkomsten.

Voor nu dus nog niet, en ik denk dat een paar GB ook wel moet voldoen met een betere MySQL structuur of ElasticSearch. Is ook nog wat uitzoek werk.

Gomez12 schreef op donderdag 12 februari 2015 @ 01:04:
SolR en ElasticSearch zijn wmb niet zo verschillend.

Persoonlijk gaat mijn voorkeur tegenwoordig meer uit naar elasticsearch, maar aan de andere kant is voor mij het voornaamste verschil dat elasticsearch sneller indexeert en retourneert. Maar dat is meer omdat ik met "flexibele" data werk die op elk moment ingeschoten moet kunnen worden.
Bij een RDW bestand verwacht ik dat het meer statische data is (zeker qua schema wat je imho makkelijker kan optimaliseren in Solr) met periodieke updates.

Maar alletwee gebruiken ze lucene als onderliggende software en als je of voor solr of voor elasticsearch zou willen gaan dan zou ik me eerst even inlezen over de onderlinge verschillen want ze zijn op veel punten als redelijk "gelijk"

De data die je krijgt is in precies hetzelfde format als mijn tabel. Dus zo’n 40 kollommen en die gaan ook niet veranderen de komende tijd neem ik aan. Dus dat schema ligt wel vast voor meerdere maanden.

pedorus schreef op donderdag 12 februari 2015 @ 01:17:
Hier een site die beweert dat juist solr sneller is:
https://www.loggly.com/bl...-scalable-log-management/
(Maar ze kiezen voor elasticsearch vanwege datadistributie/dynamiek)

Hangt dus echt af de use-case en het gebruikte schema/dynamiek etc. lijkt mij. Ik heb het idee dat ik met ES gewoon mijn huidige 40 kolommen er in kan pompen en dat alles dan wel wordt geïndexeerd en geoptimaliseerd door ES zelf. Ik ga echter toch eerst aan de slag met het verbeteren van mijn MySQL DB.

Gomez12 schreef op donderdag 12 februari 2015 @ 01:30:
Ik check het ook niet dagelijks meer (keuze is wmb gemaakt en op ES heb ik nu al dingen gebouwd) maar even uit mijn hoofd meende ik dat solr sneller was met bruut indexeren van zeg maar 100.000 items, maar dat je de resultaten pas kon zien na de complete indexatie terwijl ES die gewoon ook tussendoor toont.

Wat voor ons toendertijd de doorslag gaf omdat wij een redelijk continue hoge doorvoerstroom hebben en niet periodieke batches die goed ingepland geindexeerd kunnen worden. Voor ons is de totale batch niet zo relevant, voornamelijk de eerste zichtbaarheid van data.

Maar dit kan ondertussen ook weer omgedraaid zijn en sowieso weet ik niet hoe relevant het hier is (met RDW verwacht ik max iets van dagelijkse dumps die gewoon periodiek in te lezen zijn).
Het enige wat ik wilde aangeven is is dat ze imho niet zo verschillend zijn enkel dat je wel een keuze moet maken, dus eerst even inlezen. Want in het beginstadium vreten ze ongeveer dezelfde data,maar hoe verder je erin gaat hoe meer je de verschillen gaat zien en hoe minder je kan overswitchen.

De doorstroom zal hier niet zo hoog liggen, niet meer dan een paar duizend records per dag. Ik ga gewoon 1x per dag de nieuwe data requested en inserten. Dus dat stelt niet zoveel voor. Het inlezen en vergelijken gaat zeker gebeuren. Eerst aan de slag met de MySQL structuur en zien wat dat doet met de performance.

Ik heb wel het idee dat ik waarschijnlijk toch iets als ES of SOLR moet gaan gebruiken, zijn toch ook meer gemaakt voor dit soort use cases.

In ieder geval bedankt allemaal. Ik zou er nu graag weer verder mee gaan maar eerst nog wat (werk)colleges volgen.

[cariolive23]

Dit soort data is uitstekend te normaliseren en dit zal de performance zeker ten goede komen. Zorg ook voor de best passende datatypes, een INT voor het aantal cylinders is ietwat overdreven.... Of verwacht jij echt een auto met 83000 cilinders aan te treffen? 16 cilinders komt bij hoge uitzondering nog wel eens voor, maar daar houdt het wel zo'n beetje mee op. In MySQL heb je een TINYINT, die kost maar 1 byte ipv 4 bytes voor een INT. En wanneer je een datum nodig hebt, gebruik dan een DATE ipv een DATETIME. Ook dat scheelt per record weer 4 bytes.

Items zoals vermogen zul je ook duidelijker moeten krijgen, er is een verschil tussen pk en kW.

Wanneer je deze data hebt genormaliseerd, de beste datatypes toepast en vervolgens de gewenste indexen aanmaakt, kan het zomaar een factor 1000 sneller zijn in vergelijking met wat je nu hebt.

Ps. Waarom gebruik je GROUP BY wanneer je nergens een aggregate functie gebruikt? Gebruik DISTINCT wanneer je DISTINCT bedoelt.

Pps. Is de door jou gebruikte data openbaar beschikbaar? Zou er graag eens mee spelen

[ Voor 4% gewijzigd door cariolive23 op 12-02-2015 10:43 ]

[kwaakvaak_v2]

De drie waardes van Wachtopkeuren zijn Ja, N.V.T. en Nee. Dus in principe volstaat een BOOL ook wel.

huh? een boolean met 3 waardes? enum, ja misschien maar voor zover ik weet zijn die niet native in mysql

[cariolive23]

kwaakvaak_v2 schreef op donderdag 12 februari 2015 @ 13:06:
[...]


huh? een boolean met 3 waardes? enum, ja misschien maar voor zover ik weet zijn die niet native in mysql

NULL kun je ook zien als een NVT, dus: NULL, FALSE en TRUE

[Verwijderd]

In dit geval is Sphinx Search ook een serieuze optie. Zeker wanneer je bijvoorbeeld niet alleen facets, maar bijvoorbeeld ook tegelijk Full Text searches zou willen doen, want daarin is mysql behoorlijk beperkt en relatief traag.
Wanneer je Sphinx hebt geïnstalleerd kun je met je MySQL client direct connecten met Sphinx. Die benader je vervolgens op port 9306, dus: mysql -h0 -P 9306
Het imho grote voordeel is dat je het ontzettend snel en eenvoudig kan opzetten (realtime indexes nagelaten). Je hoeft ook geen XML of JSON schemes of oid en je je kan beperken tot alleen SQL. Kan mogelijk veel tijd schelen. Je hoeft alleen een conf bestand aan te maken, de ruwe data in SELECT plaatsen en je columns definiëren. Vervolgens met het indexer commando (bijv indexer --all --rotate) vull je je index. That's all.

Met de nieuwste versie van SphinxQL kun je facets eigenlijk doodeenvoudig als FACET clause wegschrijven.
Verder zijn er genoeg API's php/ruby/php om met SphinxQL te connecten. Al met al kun je met Sphinx prima uit te voeten voor het basic werk. ElasticSearch biedt weer veel meer opties.

[JJ93]

cariolive23 schreef op donderdag 12 februari 2015 @ 10:41:
Dit soort data is uitstekend te normaliseren en dit zal de performance zeker ten goede komen. Zorg ook voor de best passende datatypes, een INT voor het aantal cylinders is ietwat overdreven.... Of verwacht jij echt een auto met 83000 cilinders aan te treffen? 16 cilinders komt bij hoge uitzondering nog wel eens voor, maar daar houdt het wel zo'n beetje mee op. In MySQL heb je een TINYINT, die kost maar 1 byte ipv 4 bytes voor een INT. En wanneer je een datum nodig hebt, gebruik dan een DATE ipv een DATETIME. Ook dat scheelt per record weer 4 bytes.

Items zoals vermogen zul je ook duidelijker moeten krijgen, er is een verschil tussen pk en kW.

Wanneer je deze data hebt genormaliseerd, de beste datatypes toepast en vervolgens de gewenste indexen aanmaakt, kan het zomaar een factor 1000 sneller zijn in vergelijking met wat je nu hebt.

Ps. Waarom gebruik je GROUP BY wanneer je nergens een aggregate functie gebruikt? Gebruik DISTINCT wanneer je DISTINCT bedoelt.

Pps. Is de door jou gebruikte data openbaar beschikbaar? Zou er graag eens mee spelen

Klopt, dat heb ik hierboven ook besproken en ga ik meteen mee aan de slag. Moet daar meteen al veel performance mee kunnen winnen. Tussen DISTINCT en GROUP BY zag ik soms performance verschil, vandaar. De anonieme voertuig gegevens is open data.

kwaakvaak_v2 schreef op donderdag 12 februari 2015 @ 13:06:
[...]


huh? een boolean met 3 waardes? enum, ja misschien maar voor zover ik weet zijn die niet native in mysql

Zoals door cariolive23 ook wordt opgemerkt zal ik dan ook NULL gebruiken.

Verwijderd schreef op donderdag 12 februari 2015 @ 14:08:
In dit geval is Sphinx Search ook een serieuze optie. Zeker wanneer je bijvoorbeeld niet alleen facets, maar bijvoorbeeld ook tegelijk Full Text searches zou willen doen, want daarin is mysql behoorlijk beperkt en relatief traag.
Wanneer je Sphinx hebt geïnstalleerd kun je met je MySQL client direct connecten met Sphinx. Die benader je vervolgens op port 9306, dus: mysql -h0 -P 9306
Het imho grote voordeel is dat je het ontzettend snel en eenvoudig kan opzetten (realtime indexes nagelaten). Je hoeft ook geen XML of JSON schemes of oid en je je kan beperken tot alleen SQL. Kan mogelijk veel tijd schelen. Je hoeft alleen een conf bestand aan te maken, de ruwe data in SELECT plaatsen en je columns definiëren. Vervolgens met het indexer commando (bijv indexer --all --rotate) vull je je index. That's all.

Met de nieuwste versie van SphinxQL kun je facets eigenlijk doodeenvoudig als FACET clause wegschrijven.
Verder zijn er genoeg API's php/ruby/php om met SphinxQL te connecten. Al met al kun je met Sphinx prima uit te voeten voor het basic werk. ElasticSearch biedt weer veel meer opties.

Thx, dat ziet er ook interessant uit. Ten eerste mijn types verbeteren en data normaliseren. Vervolgens kan ik misschien ook wat testjes doen met Sphinx.
http://www.quora.com/Whic...asticSearch-or-Sphinx-Why
http://db-engines.com/en/...ticsearch%3BSolr%3BSphinx

[cariolive23]

JJ93 schreef op donderdag 12 februari 2015 @ 15:11:
De anonieme voertuig gegevens is open data.

Thanks! Ik moest even mijn regio aanpassen, anders mocht ik er niet bij....

Downloaden gaat nu met een magische 300Kb/s, gaat dus nog wel even duren

Ik ga er ook eens mee spelen, maar dan met PostgreSQL 9.4 op een Mac. Dit met jouw oude model en een nieuw/beter model. Performance is niet te vergelijken met jouw hard- en software, maar toch.

[JJ93]

cariolive23 schreef op donderdag 12 februari 2015 @ 17:09:
[...]

Thanks! Ik moest even mijn regio aanpassen, anders mocht ik er niet bij....

Downloaden gaat nu met een magische 300Kb/s, gaat dus nog wel even duren

Ik ga er ook eens mee spelen, maar dan met PostgreSQL 9.4 op een Mac. Dit met jouw oude model en een nieuw/beter model. Performance is niet te vergelijken met jouw hard- en software, maar toch.

Thx Ik ben nu ook bezig op een Macbook met 8GB RAM en een i5 4258u dus een stuk meer performance dan een VPS bakje met 512MB RAM

Het downloaden gaat inderdaad traag. De gehele dataset kost een paar uur inderdaad. Maar je kan ook bv filteren op Catalogusprijs > 100000 dan heb je nog een redelijke dataset en binnen een uur binnen.

[ Voor 14% gewijzigd door JJ93 op 12-02-2015 17:49 ]

[JJ93]

Verbeterde data types, nog niet genormaliseerd.

SQL:

ID                          INT AUTO_INCREMENT,
    Aantalcilinders                 TINYINT UNSIGNED DEFAULT NULL,
    Aantalstaanplaatsen             TINYINT UNSIGNED DEFAULT NULL,
    Aantalzitplaatsen               TINYINT UNSIGNED DEFAULT NULL,
    BPM                         MEDIUMINT UNSIGNED DEFAULT NULL,
    Brandstofverbruikbuitenweg      DECIMAL(4, 4) DEFAULT NULL,
    Brandstofverbruikgecombineerd   DECIMAL(4, 4) DEFAULT NULL,
    Brandstofverbruikstad           DECIMAL(4, 4) DEFAULT NULL,
    Catalogusprijs                  MEDIUMINT UNSIGNED DEFAULT NULL,
    Cilinderinhoud                  SMALLINT UNSIGNED DEFAULT NULL,
    CO2uitstootgecombineerd         TINYINT UNSIGNED DEFAULT NULL,
    Datumaanvangtenaamstelling      DATE DEFAULT NULL,
    DatumeersteafgifteNederland     DATE DEFAULT NULL,
    Datumeerstetoelating            DATE DEFAULT NULL,
    Eerstekleur                 VARCHAR(25) DEFAULT NULL,
    G3installatie                   BOOL DEFAULT NULL,
    Handelsbenaming             VARCHAR(100) DEFAULT NULL,
    Hoofdbrandstof                  VARCHAR(50) DEFAULT NULL,
    Inrichting                      VARCHAR(100) DEFAULT NULL,
    Kenteken                        VARCHAR(10) DEFAULT NULL,
    Laadvermogen                    SMALLINT UNSIGNED DEFAULT NULL, 300 boven smallint
    Massaleegvoertuig               SMALLINT UNSIGNED DEFAULT NULL, 200 boven smallint
    Massarijklaar                   SMALLINT UNSIGNED DEFAULT NULL, 191 boven smallint
    Maximaleconstructiesnelheid     TINYINT UNSIGNED DEFAULT NULL,
    Maximumtetrekkenmassaautonoomgeremd SMALLINT UNSIGNED DEFAULT NULL, 32 boven smallint
    Maximumtetrekkenmassageremd         SMALLINT UNSIGNED DEFAULT NULL, 2 boven smallint
    Maximumtetrekkenmassamiddenasgeremd SMALLINT UNSIGNED DEFAULT NULL,
    Maximumtetrekkenmassaongeremd           SMALLINT UNSIGNED DEFAULT NULL, 4 boven smallint
    Maximumtetrekkenmassaopleggergeremd     SMALLINT UNSIGNED DEFAULT NULL,
    Merk                            VARCHAR(100) DEFAULT NULL,
    Milieuclassificatie             VARCHAR(50) DEFAULT NULL,
    Nevenbrandstof                  VARCHAR(50) DEFAULT NULL,
    Retrofitroetfilter                  BOOL DEFAULT NULL,
    Toegestanemaximummassavoertuig  SMALLINT UNSIGNED DEFAULT NULL, 1015 boven MEDIUMINT
    Tweedekleur                 VARCHAR(25) DEFAULT NULL,
    Vermogen                        TINYINT UNSIGNED DEFAULT NULL,
    Vermogenbromsnorfiets           TINYINT UNSIGNED DEFAULT NULL,
    VervaldatumAPK              DATE DEFAULT NULL,
    Voertuigsoort                   VARCHAR(50) DEFAULT NULL,
    Wachtopkeuren                   BOOL DEFAULT NULL,
    WAMverzekerdgeregistreerd       BOOL DEFAULT NULL,
    Zuinigheidslabel                VARCHAR(1) DEFAULT NULL,

Zie ook de extra aantekeningen, van de 12 miljoen records zijn er bij een aantal velden maximaal 1000 boven de maximale grootte van SMALLINT (65535). Dus zo'n 0.0083%. Zal ik dan gewoon SMALLINT gebruiken om 12 miljoen keer een byte te besparen?

Dus voor 7 kolommen SMALLINT ipv TINYINT, raakt totaal zo'n 1.750 records op 12.000.000

[Gomez12]

Wat je wilt besparen en wat je ermee wilt kunnen doen is aan jezelf.

Denk jij nooit een situatie tegen te komen dat iemand zoekt op de te verliezen waardes dan kan je het overwegen, maar je zegt dan wel dat je ook bij mogelijke nieuwe dingen dingen gaat missen (zeg je uberhaupt al als je verder gaat optimaliseren dan het schema van de RDW)

Of een laadvermogen een smallint of een tinyint moet worden dat is aan jezelf. Simplistisch gezegd ga je er met een smallint nooit Amerikaanse trucks inkrijgen of Australische Road Warriors. Maar wat is de kans dat die wel in het RDW komen in de komende 5 jaar en is jouw doelgroep er dan wel in geinteresseerd? Dit zijn vragen die alleen jij kan beantwoorden.

[cariolive23]

Vermogen TINYINT? Ik wil niet opscheppen, maar mijn auto heeft meer pk's dan dat er in een tinyint passen. En dat geldt ook nog voor de kW's. Een tinyint gaat maar tot 255 en dat is het dan.

[JJ93]

cariolive23 schreef op donderdag 12 februari 2015 @ 19:26:
Vermogen TINYINT? Ik wil niet opscheppen, maar mijn auto heeft meer pk's dan dat er in een tinyint passen. En dat geldt ook nog voor de kW's. Een tinyint gaat maar tot 255 en dat is het dan.

Klopt, moet MEDIUMINT zijn, weet niet wat ik daar dacht. Net alles nog even doorlopen en de nieuwe data types kloppen nu wel.

Gomez12 schreef op donderdag 12 februari 2015 @ 19:22:
Wat je wilt besparen en wat je ermee wilt kunnen doen is aan jezelf.

Denk jij nooit een situatie tegen te komen dat iemand zoekt op de te verliezen waardes dan kan je het overwegen, maar je zegt dan wel dat je ook bij mogelijke nieuwe dingen dingen gaat missen (zeg je uberhaupt al als je verder gaat optimaliseren dan het schema van de RDW)

Of een laadvermogen een smallint of een tinyint moet worden dat is aan jezelf. Simplistisch gezegd ga je er met een smallint nooit Amerikaanse trucks inkrijgen of Australische Road Warriors. Maar wat is de kans dat die wel in het RDW komen in de komende 5 jaar en is jouw doelgroep er dan wel in geinteresseerd? Dit zijn vragen die alleen jij kan beantwoorden.

Ik denk niet dat het vaak voor zal komen. Het liefst heb ik wel een zo correct mogelijke dataset, maar met zoveel rijen aan data valt er meteen veel te besparen.

[Gomez12]

JJ93 schreef op donderdag 12 februari 2015 @ 20:36:
[...]
Ik denk niet dat het vaak voor zal komen. Het liefst heb ik wel een zo correct mogelijke dataset, maar met zoveel rijen aan data valt er meteen veel te besparen.

Definieer "veel" eens...

Kijk in 1e instantie was je datamodel gewoon onzinnig, persoonlijk zou ik alle getalsvelden gewoon gezet hebben op unsigned mediumint (met een paar uitzonderingen) omdat de rest me gewoon veels te veel vraagtekens / mogelijke toekomstige veranderingen etc zouden kunnen opleveren.
Al het numerieke gezeur moet je gewoon met redelijke defaults vullen en dan ben je er imho wel, je praat tenslotte maar over 40 velden.

Of laat ik het anders vragen : Hoeveel ruimte ga je nog per record opschieten met numeriek geneuzel en hoeveel ruimte ben je opgeschoten met het verkleinen van een kenteken van varchar(255) naar varchar(10) (hint:niet echt veel)

En wat is een varchar(1), wat is dat exact volgens jou?

Als je gaat normaliseren dan ga je ruimte winnen doordat je dan niet meer 12 miljoen keer een varchar(100) voor merk gebruikt maar zeg 100x die varchar(100) en voor de rest numerieke FK's.

Je bent nu aan het doorslaan qua optimaliseren van datamodel zelfs zover dat je zit te overwegen om zelfs data te verliezen. Terwijl je nu juist grote slagen kan maken door te normaliseren.

[JJ93]

Gomez12 schreef op donderdag 12 februari 2015 @ 22:01:
[...]

Definieer "veel" eens...

Kijk in 1e instantie was je datamodel gewoon onzinnig, persoonlijk zou ik alle getalsvelden gewoon gezet hebben op unsigned mediumint (met een paar uitzonderingen) omdat de rest me gewoon veels te veel vraagtekens / mogelijke toekomstige veranderingen etc zouden kunnen opleveren.
Al het numerieke gezeur moet je gewoon met redelijke defaults vullen en dan ben je er imho wel, je praat tenslotte maar over 40 velden.

Of laat ik het anders vragen : Hoeveel ruimte ga je nog per record opschieten met numeriek geneuzel en hoeveel ruimte ben je opgeschoten met het verkleinen van een kenteken van varchar(255) naar varchar(10) (hint:niet echt veel)

En wat is een varchar(1), wat is dat exact volgens jou?

Als je gaat normaliseren dan ga je ruimte winnen doordat je dan niet meer 12 miljoen keer een varchar(100) voor merk gebruikt maar zeg 100x die varchar(100) en voor de rest numerieke FK's.

Je bent nu aan het doorslaan qua optimaliseren van datamodel zelfs zover dat je zit te overwegen om zelfs data te verliezen. Terwijl je nu juist grote slagen kan maken door te normaliseren.

Klopt daar heb je gelijk in, het verliezen/afstoten van data gaat te ver. Zuinigheidslabel is slechts één karakter maar kan dus ook CHAR(1) zijn omdat het altijd hetzelfde is. Hetzelfde geldt ook voor kenteken, altijd 8 karakters tot nu toe. Maar wie weet krijgen we in de toekomst meer karakters.

Voor de rest met de VARCHAR lengtes schiet ik inderdaad niet veel op. Misschien maar gewoon op 255 laten staan. Of in ieder geval wat ruimere voor bv Merk en Model 255 zou misschien ooit voor kunnen komen, Kenteken 10 lijkt mij future-proof. Maar omdat ik niet meer 12 miljoen rijen ga hebben maar alleen een extra tabel met de unieke waardes (paar 100) na normalisering win je ook niet echt veel ruimte. Ik ben nu bezig met het normaliseren. Nog even kijken hoe ik dat het beste kan doen vanaf een CSV import.

[Douweegbertje]

Je mist nog wat dingen. Ik heb nu niet zo heel veel tijd om er diep op in te gaan maar;
Hoofdbrandstof kun je gewoon een koppel tabel voor maken met een aantal opties namelijk: benzine, diesel, gas, combi benz/gas, combi diesel/gas, elektrisch en combi elektrisch
Verder mis je al wat punten zoals de dagwaarde, ledig gewicht (en dat is anders dan leeg gewicht). Je kunt (of moet beter gezegd) een kenteken als 6 karakters opslaan, niet 10? Gewoon de - eruit halen natuurlijk..

Daarbij zou ik niet zo heel moeilijk gaan lopen doen. Verzin anders eens wat categorieën en maak daar koppeltabellen van, of wil je perse alle auto's met het exacte gewicht van 1337KG hebben? Lijkt me logischer om stappen van ~ 25kg te maken dan, en elk voertuig zo in te voeren dat 1250kg ID 25 in je gewichten tabel is..

Er is ook een reden dat zo'n partij al dit niet aanlevert.

[Gomez12]

JJ93 schreef op donderdag 12 februari 2015 @ 22:55:
[...]
Zuinigheidslabel is slechts één karakter maar kan dus ook CHAR(1) zijn omdat het altijd hetzelfde is. Hetzelfde geldt ook voor kenteken, altijd 8 karakters tot nu toe. Maar wie weet krijgen we in de toekomst meer karakters.

Waarom ik die varchar(1) noemde is omdat die wederom onzinnig is. Varchars hebben extra "opstartkosten" omdat ze ook bij moeten houden hoe lang de waarde is, maar hebben weer "gebruiksvoordelen" omdat ze niet de volledige lengte hoeven te pakken (itt CHAR)
daarnaast zit er nog wat (imho voornamelijk theoretisch vanwege dat dit enkel uitmaakt als je echt 100% char's gebruikt) geneuzelverschil qua directe aanspreekbaarheid / fixed length rows etc als verschil tussen varchar en char.

Alleen bij een varchar(1) heb je dus wel de "opstartkosten" maar je hebt geen "gebruiksvoordelen" want het veld is 1 lang of null, er is geen ruimte voor de gebruikersvoordelen.

Voor de rest met de VARCHAR lengtes schiet ik inderdaad niet veel op. Misschien maar gewoon op 255 laten staan.

Waarom een arbitraire 255 boven een arbitraire 100? Waarom niet een arbitraire 1024?
Het is enkel het error point (tenminste als MySQL in strict modus draait, anders wordt het gewoon afgekapt en krijg je enkel een warning, is ook altijd leuk als je die niet effectief opvangt)
Wat is gewoon een zinnige default voor die velden? Blind 255 neerdumpen is wmb geen zinnige default, dat is gewoon blind neerdumpen. Denk erover na en bouw het goed nagedacht op.

Kenteken 10 lijkt mij future-proof.

Waarom? Heb jij een indicatie dat de volgende uitbreiding op kentekens maar 2 tekens gaat zijn en geen 3 (alhoewel ik nu al niet weet wat 8 tekens zou moeten hebben maargoed ik ga er blind vanuit dat die voorkomt in jouw dataset) of misschien zelfs 4.
Het autogebruik is explosief gestegen de laatste 100 jaar, dus ik heb geen idee of ze straks maar 2 extra cijfers gaan invoeren of nog meer.

Alleen zo lang als ik geen idee heb zie ik geen meerwaarde om het meer dan een char( of varchar( te maken, want je zit het nu mogelijk future proof te maken, maar mogelijk ook weer niet. Zolang mijn glazen bol bij de reparatie afdeling ligt weet ik niet wat future proof is en zou ik het lekker houden op wat er nu wel bekend is.

Of je definieert voor jezelf een paar sane defaults in de trant van :
- Extreem kort char veld (char(10)/varchar(10))
- Kort char veld(varchar(50))
- Lang char veld (varchar(255))
- Insane char veld (varchar(max) / text)

Maar nu blijf je maar gewoon wat doen.

Ik ben nu bezig met het normaliseren. Nog even kijken hoe ik dat het beste kan doen vanaf een CSV import.

Heb je er een scripting taal / programma tussenzitten of zit je het blind te importeren in de database?
Want in geval van 2 kan je ook overwegen om een soort van staging te maken die het gewoon letterlijk importeert en daarnaast een zinnige tabelstructuur waarbij je dan na een import met sql-scripts alles vanuit staging naar je zinnige tabelstructuur overzet (waarbij je dan uiteindelijk werkt vanuit je zinnige tabelstructuur)

[Gomez12]

Douweegbertje schreef op donderdag 12 februari 2015 @ 23:09:
Daarbij zou ik niet zo heel moeilijk gaan lopen doen. Verzin anders eens wat categorieën en maak daar koppeltabellen van, of wil je perse alle auto's met het exacte gewicht van 1337KG hebben? Lijkt me logischer om stappen van ~ 25kg te maken dan, en elk voertuig zo in te voeren dat 1250kg ID 25 in je gewichten tabel is..

Waarom zou je niet werken met dat een auto 1337 KG weegt? Persoonlijk zou ik gewoon een koppeltabel maken met gewichten (er zullen er meerdere gelijk zijn) en wil je dan met stappen van 25kg in je interface werken en wil je dit niet berekenen dan sla je deze stapid naast je gewicht op.

Persoonlijk lijkt het mij wel een grappige query : Wie heeft "exact" dezelfde auto als ik maar niet met hetzelfde gewicht en waar zit dat verschil dan in?
Wellicht dat Ford wel blauwe verf gebruikt die 1 kg zwaarder is dan al hun andere kleuren (als alle blauwe fords 1 kg zwaarder zijn dan alle niet blauwe) dat is toch weer een funfact.

Ik zie vooralsnog geen reden om data te gaan verliezen, hooguit niet meer gebruiken (of een 2e waarde ernaast die je primair gebruikt).

Waarom zou jij data willen verliezen? Performance technisch zal het geen zoden aan de dijk zetten en gebruikerstechnisch vind ik het enkel slechter werken.

[JJ93]

Douweegbertje schreef op donderdag 12 februari 2015 @ 23:09:
Je mist nog wat dingen. Ik heb nu niet zo heel veel tijd om er diep op in te gaan maar;
Hoofdbrandstof kun je gewoon een koppel tabel voor maken met een aantal opties namelijk: benzine, diesel, gas, combi benz/gas, combi diesel/gas, elektrisch en combi elektrisch
Verder mis je al wat punten zoals de dagwaarde, ledig gewicht (en dat is anders dan leeg gewicht). Je kunt (of moet beter gezegd) een kenteken als 6 karakters opslaan, niet 10? Gewoon de - eruit halen natuurlijk..

Daarbij zou ik niet zo heel moeilijk gaan lopen doen. Verzin anders eens wat categorieën en maak daar koppeltabellen van, of wil je perse alle auto's met het exacte gewicht van 1337KG hebben? Lijkt me logischer om stappen van ~ 25kg te maken dan, en elk voertuig zo in te voeren dat 1250kg ID 25 in je gewichten tabel is..

Er is ook een reden dat zo'n partij al dit niet aanlevert.

Ja die koppel tabellen ga ik aanmaken. Dagwaarde en ledig gewicht worden niet aangeleverd in deze dataset dus moet ik ergens anders vandaan haal ik wil mijn set verder uitbreiden. Dan maak ik wel een koppeltabel of ik kan later nog een kolom invoegen.

Alle kentekens zijn sowieso nu al opgeslagen zonder streepjes, alles 6 karakters. Maar in de toekomst kan gekozen worden voor een 7+ cijferig kenteken. Voor auto's kan ik net zo goed het exacte gewicht opslaan. De gewichtklasses voor de motorrijtuig belasting lopen op per 50kg en dat is vooral belangrijk voor mensen.

Gomez12 schreef op donderdag 12 februari 2015 @ 23:20:
[...]

Waarom ik die varchar(1) noemde is omdat die wederom onzinnig is. Varchars hebben extra "opstartkosten" omdat ze ook bij moeten houden hoe lang de waarde is, maar hebben weer "gebruiksvoordelen" omdat ze niet de volledige lengte hoeven te pakken (itt CHAR)
daarnaast zit er nog wat (imho voornamelijk theoretisch vanwege dat dit enkel uitmaakt als je echt 100% char's gebruikt) geneuzelverschil qua directe aanspreekbaarheid / fixed length rows etc als verschil tussen varchar en char.

Alleen bij een varchar(1) heb je dus wel de "opstartkosten" maar je hebt geen "gebruiksvoordelen" want het veld is 1 lang of null, er is geen ruimte voor de gebruikersvoordelen.

Klopt, in de huidige dataset is alles 1 karakter. Label gaat neem ik aan ook niet meer veranderen naar een langere tekst.

Gomez12 schreef op donderdag 12 februari 2015 @ 23:20:
[...]

Waarom een arbitraire 255 boven een arbitraire 100? Waarom niet een arbitraire 1024?
Het is enkel het error point (tenminste als MySQL in strict modus draait, anders wordt het gewoon afgekapt en krijg je enkel een warning, is ook altijd leuk als je die niet effectief opvangt)
Wat is gewoon een zinnige default voor die velden? Blind 255 neerdumpen is wmb geen zinnige default, dat is gewoon blind neerdumpen. Denk erover na en bouw het goed nagedacht op.

255 omdat het (max) 8-bits inneemt. In mijn laatste sql code hierboven heb ik al nuttige waardes bedacht. Er komt ook geen 255 meer in voor.

Gomez12 schreef op donderdag 12 februari 2015 @ 23:20:
[...]

Waarom? Heb jij een indicatie dat de volgende uitbreiding op kentekens maar 2 tekens gaat zijn en geen 3 (alhoewel ik nu al niet weet wat 8 tekens zou moeten hebben maargoed ik ga er blind vanuit dat die voorkomt in jouw dataset) of misschien zelfs 4.
Het autogebruik is explosief gestegen de laatste 100 jaar, dus ik heb geen idee of ze straks maar 2 extra cijfers gaan invoeren of nog meer.

Alleen zo lang als ik geen idee heb zie ik geen meerwaarde om het meer dan een char( of varchar( te maken, want je zit het nu mogelijk future proof te maken, maar mogelijk ook weer niet. Zolang mijn glazen bol bij de reparatie afdeling ligt weet ik niet wat future proof is en zou ik het lekker houden op wat er nu wel bekend is.

Of je definieert voor jezelf een paar sane defaults in de trant van :
- Extreem kort char veld (char(10)/varchar(10))
- Kort char veld(varchar(50))
- Lang char veld (varchar(255))
- Insane char veld (varchar(max) / text)

Maar nu blijf je maar gewoon wat doen.

In de huidige dataset 6 tekens, de nieuwe reeks blijft geloof ik ook weer bij 6 tekens. Een paar defaults hebben lijkt mij ook een goed idee ja zodat er nog wat meer logica in de gekozen nummers zit. Ze zijn nu vooral gebaseerd op de huidige langste tekst in de dataset en de maximale verwachte (relevante) lengte

Gomez12 schreef op donderdag 12 februari 2015 @ 23:20:
[...]

Heb je er een scripting taal / programma tussenzitten of zit je het blind te importeren in de database?
Want in geval van 2 kan je ook overwegen om een soort van staging te maken die het gewoon letterlijk importeert en daarnaast een zinnige tabelstructuur waarbij je dan na een import met sql-scripts alles vanuit staging naar je zinnige tabelstructuur overzet (waarbij je dan uiteindelijk werkt vanuit je zinnige tabelstructuur)

Op dit moment gewoon een LOAD DATA LOCAL INFILE meteen SET met allemaal IF statements om 'Ja' als TRUE neer te zetten, 'Nee' als FALSE en andere waardes as NULL. En lege strings als NULL voor varchars, ongeldige ints als NULL, etc.

Het makkelijkste leek mij ook om gewoon eerst alles te importeren in een dump tabel en vervolgens over te zetten naar de zinnige structuur. En dan de dump tabel weer legen. Of via PHP of een andere taal alles uitlezen en importeren. Het plan is ook om het updaten te automatiseren, of via de API calls of een CSV bestand downloaden en dan importeren. Voorkeur ligt bij het downloaden van het CSV bestand denk ik. Dan kan ik de code ook meteen weer herbruiken.

Gomez12 schreef op donderdag 12 februari 2015 @ 23:29:
[...]

Waarom zou je niet werken met dat een auto 1337 KG weegt? Persoonlijk zou ik gewoon een koppeltabel maken met gewichten (er zullen er meerdere gelijk zijn) en wil je dan met stappen van 25kg in je interface werken en wil je dit niet berekenen dan sla je deze stapid naast je gewicht op.

Persoonlijk lijkt het mij wel een grappige query : Wie heeft "exact" dezelfde auto als ik maar niet met hetzelfde gewicht en waar zit dat verschil dan in?
Wellicht dat Ford wel blauwe verf gebruikt die 1 kg zwaarder is dan al hun andere kleuren (als alle blauwe fords 1 kg zwaarder zijn dan alle niet blauwe) dat is toch weer een funfact.

Ik zie vooralsnog geen reden om data te gaan verliezen, hooguit niet meer gebruiken (of een 2e waarde ernaast die je primair gebruikt).

Waarom zou jij data willen verliezen? Performance technisch zal het geen zoden aan de dijk zetten en gebruikerstechnisch vind ik het enkel slechter werken.

Eens, ik ben er nu ook over uit dat ik alle data wil behouden net zoals ik de facetten zo uitgebreid mogelijk wil hebben. Ook al is er maar één gebruiker die graag wil filteren op een bepaald veld is het als nog een win voor mezelf.

Voor de wegenbelasting zit je met 50kg stappen. Ik wil voor getallen sliders maken dus dan kan je de schuifjes gewoon goed zetten. Het leuke van alle facetten lijkt mij ook juist om de aparte feitjes op te zoeken. Ik kan ook grafieken kan genereren op basis van de gekozen recordset, bv pie-diagram met alle kleuren, etc.

[Douweegbertje]

Gomez12 schreef op donderdag 12 februari 2015 @ 23:29:
[...]

Waarom zou je niet werken met dat een auto 1337 KG weegt? Persoonlijk zou ik gewoon een koppeltabel maken met gewichten (er zullen er meerdere gelijk zijn) en wil je dan met stappen van 25kg in je interface werken en wil je dit niet berekenen dan sla je deze stapid naast je gewicht op.

Persoonlijk lijkt het mij wel een grappige query : Wie heeft "exact" dezelfde auto als ik maar niet met hetzelfde gewicht en waar zit dat verschil dan in?
Wellicht dat Ford wel blauwe verf gebruikt die 1 kg zwaarder is dan al hun andere kleuren (als alle blauwe fords 1 kg zwaarder zijn dan alle niet blauwe) dat is toch weer een funfact.

Ik zie vooralsnog geen reden om data te gaan verliezen, hooguit niet meer gebruiken (of een 2e waarde ernaast die je primair gebruikt).

Waarom zou jij data willen verliezen? Performance technisch zal het geen zoden aan de dijk zetten en gebruikerstechnisch vind ik het enkel slechter werken.

Waarom zou je data verliezen? Je kunt nog steeds de echte waarde hebben, maar wel een koppel tabel op een gewicht range zodat je niet gewicht < xxxx && gewicht > xxxx hoeft te doen. Simpelweg where gewichtrange = 25. Dat je in een eventuele vervolg stap / query dan wel van ~ xxxx aantal voertuigen de exacte waarde gaat ophalen is dan natuurlijk wel mogelijk

Zo zou ik het dan doen met beperkte resources e.d.

[Gomez12]

JJ93 schreef op donderdag 12 februari 2015 @ 23:42:
[...]
Alle kentekens zijn sowieso nu al opgeslagen zonder streepjes, alles 6 karakters. Maar in de toekomst kan gekozen worden voor een 7+ cijferig kenteken.

Waar heb je vandaan dat er sprake is van een 7+ cijferig kenteken? Gewoon even nieuwsgierigheid mijnerzijde...

Want ik zie geen reden waarom het geen 8+ of bijv 13-char code zou worden en bijv de 13-char vereist ook weer een aanpassing aan jouw kant, oftewel niet vooruit lopen op de zaken die je niet weet.
Als je ergens vandaan hebt dat er concrete plannen zijn om 7-char kentekens in te voeren dan kan je daarop je dbase aanpassen, maar als je het enkel zit te gokken dan kan je imho beter gewoon voor 6-char gaan.

[...]
255 omdat het (max) 8-bits inneemt. In mijn laatste sql code hierboven heb ik al nuttige waardes bedacht. Er komt ook geen 255 meer in voor.

Daarop reageerde ik juist, je had helemaal geen enkele structuur meer. Alles was gokwerk geworden.

[...]
Op dit moment gewoon een LOAD DATA LOCAL INFILE meteen SET met allemaal IF statements om 'Ja' als TRUE neer te zetten, 'Nee' als FALSE en andere waardes as NULL. En lege strings als NULL voor varchars, ongeldige ints als NULL, etc.

Aargh, ondertussen krijg ik echt de neiging om te zeggen : Koop een boek / volg een cursus...

IF's om ja als true / nee als false neer te zetten heeft al niet mijn voorkeur maar dat is meer persoonlijke voorkeur. Alleen definieer dan ook 'n.v.t.' als null als dat van toepassing is of '' als null als dat van toepassing is en laat de rest gewoon keihard foutlopen.
Straks kiest het rdw ervoor om naar het engels over te gaan, dan krijg je yes/no terug (of ze kiezen ervoor om true/false terug te geven) dan krijg jij geen enkele foutmelding want alles verloopt nog steeds zoals standaard in je IF-constructies, alleen alles valt nu in de laatste categorie en is dan dus NULL, dan kan je gaan rennen want opeens heb je gebruikers die klagen terwijl jij geen foutmelding ergens ziet.

Een ongeldige int is ook geen null, dat is gewoon een error en daar moet je van gaan bekijken waarom die anders is en wat je ermee aan kan, die moet je niet verdoezelen door hem naar null te zetten.

Null is normaliter : Unknown / onbekend en iets totaal anders als leeg. Nou leent csv zich ruk voor het uitdrukken van null, waardoor het waarschijnlijk aangeduid wordt als leeg, maar conceptueel zit er een verschil in en zeker als je wel toegekende waardes (ongeldige ints etc) naar null gaat brengen dan krijg je dat je data weggooit maar je weet niet wat voor data.
Simpel gezegd heb je bijv geslacht, dat is over het algemeen : M/V (praktijksituatie even daargelaten want die is ietwat complexer), die kan niet leeg zijn, want geslachtloos bestaat niet, dus leeg is null want het is onbekend wat het geslacht is.

Hetzelfde heb je met iets wat jij eerder aanhaalde en wat je omzette naar een BOOL, jij zei dat er maar 3 waardes voor waren, Ja/nee/n.v.t.
Ik denk praktisch gezien dat er 4 waardes in je dataset voorkomen : ja/nee/n.v.t./leeg waarbij jij nu leeg interpreteert alszijnde n.v.t. terwijl leeg waarschijnlijk betekent : Nog niet bekend, is wel vereist maar momenteel nog niet bekend (en dat is iets heel anders als n.v.t.)

Douweegbertje schreef op donderdag 12 februari 2015 @ 23:58:
[...]
Waarom zou je data verliezen?

Ik las het blijkbaar iets anders als jij het bedoelde. Ik las het als koppeltabel ter vervanging van huidige waarde.

[Voutloos]

Dergelijke rounded/redundant kolommetjes toevoegen is weer een heel ander onderwerp. Maar dit topic is nu al te veel doorgeslagen naar de db-trivia en micro-optimalisatie kant.

Gomez12 schreef op vrijdag 13 februari 2015 @ 00:05:
Want ik zie geen reden waarom het geen 8+ of bijv 13-char code zou worden en bijv de 13-char vereist ook weer een aanpassing aan jouw kant, oftewel niet vooruit lopen op de zaken die je niet weet.
Als je ergens vandaan hebt dat er concrete plannen zijn om 7-char kentekens in te voeren dan kan je daarop je dbase aanpassen, maar als je het enkel zit te gokken dan kan je imho beter gewoon voor 6-char gaan.

Check. Als je met alle niet aangekondigde wijzigingen rekening wil houden moet je alles maar megahugeblob maken en niet zeuren over performance. En vervolgens gaat de rest vd logica ook alsnog fout idd (i.e. je syntax regex op kenteken user input etc).

[ Voor 147% gewijzigd door Voutloos op 13-02-2015 10:50 ]

[JJ93]

@Voutloos en @Douweegbertje ook gelezen.

Gomez12 schreef op vrijdag 13 februari 2015 @ 00:05:
[...]

Waar heb je vandaan dat er sprake is van een 7+ cijferig kenteken? Gewoon even nieuwsgierigheid mijnerzijde...

Want ik zie geen reden waarom het geen 8+ of bijv 13-char code zou worden en bijv de 13-char vereist ook weer een aanpassing aan jouw kant, oftewel niet vooruit lopen op de zaken die je niet weet.
Als je ergens vandaan hebt dat er concrete plannen zijn om 7-char kentekens in te voeren dan kan je daarop je dbase aanpassen, maar als je het enkel zit te gokken dan kan je imho beter gewoon voor 6-char gaan.

Nergens, dus ik ga inderdaad voor 6 chars.

Gomez12 schreef op vrijdag 13 februari 2015 @ 00:05:
IF's om ja als true / nee als false neer te zetten heeft al niet mijn voorkeur maar dat is meer persoonlijke voorkeur. Alleen definieer dan ook 'n.v.t.' als null als dat van toepassing is of '' als null als dat van toepassing is en laat de rest gewoon keihard foutlopen.
Straks kiest het rdw ervoor om naar het engels over te gaan, dan krijg je yes/no terug (of ze kiezen ervoor om true/false terug te geven) dan krijg jij geen enkele foutmelding want alles verloopt nog steeds zoals standaard in je IF-constructies, alleen alles valt nu in de laatste categorie en is dan dus NULL, dan kan je gaan rennen want opeens heb je gebruikers die klagen terwijl jij geen foutmelding ergens ziet.

Een ongeldige int is ook geen null, dat is gewoon een error en daar moet je van gaan bekijken waarom die anders is en wat je ermee aan kan, die moet je niet verdoezelen door hem naar null te zetten.

Null is normaliter : Unknown / onbekend en iets totaal anders als leeg. Nou leent csv zich ruk voor het uitdrukken van null, waardoor het waarschijnlijk aangeduid wordt als leeg, maar conceptueel zit er een verschil in en zeker als je wel toegekende waardes (ongeldige ints etc) naar null gaat brengen dan krijg je dat je data weggooit maar je weet niet wat voor data.
Simpel gezegd heb je bijv geslacht, dat is over het algemeen : M/V (praktijksituatie even daargelaten want die is ietwat complexer), die kan niet leeg zijn, want geslachtloos bestaat niet, dus leeg is null want het is onbekend wat het geslacht is.

Hetzelfde heb je met iets wat jij eerder aanhaalde en wat je omzette naar een BOOL, jij zei dat er maar 3 waardes voor waren, Ja/nee/n.v.t.
Ik denk praktisch gezien dat er 4 waardes in je dataset voorkomen : ja/nee/n.v.t./leeg waarbij jij nu leeg interpreteert alszijnde n.v.t. terwijl leeg waarschijnlijk betekent : Nog niet bekend, is wel vereist maar momenteel nog niet bekend (en dat is iets heel anders als n.v.t.)

Er zijn in de huidige dataset 3 waardes, alleen ja/nee/n.v.t. en geen lege waardes.

Om het importeren te verbeteren ben ik nu bezig met een PHP scriptje met een groot aantal checks voor elk field.

Ik heb ook koppel tabellen voor de varchar waardes. De voertuig tabel ziet er nu zo uit

SQL:

CREATE TABLE voertuig (

    id              INT AUTO_INCREMENT,
    kenteken        CHAR(6) NOT NULL,

    merk                    SMALLINT UNSIGNED,
    model                   SMALLINT UNSIGNED,

    soort                   TINYINT UNSIGNED,
    inrichting              TINYINT UNSIGNED,

    prijs_catalogus         MEDIUMINT UNSIGNED DEFAULT NULL,
    prijs_bpm               MEDIUMINT UNSIGNED DEFAULT NULL,

    datum_tenaamstelling    DATE DEFAULT NULL,
    datum_afgifte           DATE DEFAULT NULL,
    datum_toelating         DATE DEFAULT NULL,
    datum_verval_apk        DATE DEFAULT NULL,

    gekeurd                 BOOL DEFAULT NULL,
    wam_verzekerd           BOOL DEFAULT NULL

    kleur_een               TINYINT UNSIGNED,
    kleur_twee              TINYINT UNSIGNED,

    staanplaatsen           TINYINT UNSIGNED DEFAULT NULL,
    zitplaatsen             TINYINT UNSIGNED DEFAULT NULL,

    cilinder_aantal         TINYINT UNSIGNED DEFAULT NULL,
    cilinder_inhoud         SMALLINT UNSIGNED DEFAULT NULL,

    vermogen                MEDIUMINT UNSIGNED DEFAULT NULL, 
    vermogen_bromsnorfiets  TINYINT UNSIGNED DEFAULT NULL,

    brandstof_primair       TINYINT UNSIGNED,
    brandstof_secundair     TINYINT UNSIGNED,
    brandstof_g3            BOOL DEFAULT NULL,

    brandstof_buitenweg     DECIMAL(4, 4) DEFAULT NULL,
    brandstof_gecombineerd  DECIMAL(4, 4) DEFAULT NULL,
    brandstof_stad          DECIMAL(4, 4) DEFAULT NULL,
    
    milieu_classificatie    TINYINT UNSIGNED,
    milieu_uitstoot         TINYINT UNSIGNED DEFAULT NULL,
    milieu_zuinigheidslabel TINYINT UNSIGNED,
    milieu_roetfilter       BOOL DEFAULT NULL,

    laadvermogen            MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_maximum           MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_leeg              MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_rijklaar          MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_autonoom_geremd   MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_massa_geremd      MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_middenas_geremd   MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_ongeremd          MEDIUMINT UNSIGNED DEFAULT NULL,
    massa_oplegger_geremd   MEDIUMINT UNSIGNED DEFAULT NULL,

    PRIMARY KEY (id),
    INDEX kenteken (kenteken)

);

Voor een simpele tabel met maar een paar unieke waardes

SQL:

CREATE TABLE milieuclassificatie (
    id              TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    classificatie   VARCHAR(50)
);

Tot nu toe altijd gewoon INT gebruikt maar er zullen nooit meer dan 255 rijen data in deze tabel zitten. Dus zal ik dan TINYINT gebruiken voor zowel de referentie in de voertuig tabel als in deze tabel?

[Verwijderd]

Ik zie nog geen relaties, dus er is nog geen argument om voor een relationele database te kiezen.

[JJ93]

Verwijderd schreef op vrijdag 13 februari 2015 @ 21:19:
Ik zie nog geen relaties, dus er is nog geen argument om voor een relationele database te kiezen.

Was nog bezig met de structuur. Die is ook niet iets veranderd (o.a. smallint voor uitstoot ipv tinyint).

Mijn PHP script met allemaal checks voor alle kolommen is ook af. Paar 100 regels nette code en het werkt nu bijna allemaal.



Enige dat fout gaat is te zien bij de voertuig_inrichting kolom. De 2e rij heeft daar ineens een waarde van 3 terwijl dat nummer 2 in de koppel tabel is. Dus hij pakt de laatste rij ipv normale auto increment.

Komt door

SQL:

INSERT INTO milieu_label (label) VALUES (?) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

Dus even zien wat de correcte query voor het inserten naar de koppeltabel is en vervolgens het ID herbruiken. Denk dat het dan moet zijn milieu_label.id ipv gewoon id, dan pakt hij denk ik het ID van voertuig tabel.

In ieder geval bedankt voor de hulp allemaal. Ik zal vannacht het script draaien om alle 12 miljoen rijen weer te importeren. En dan zien hoe de performance is

[TweakMDS]

Erg interessant topic. Ik ben voor mijn werk veel met data bezig (met name telecom data in SQL). Met die achtergrond ben ik me de laatste jaren ook veel aan het verdiepen in nosql (in de vorm van voornamelijk mongodb en cassandra). Dit lijkt wel een leuke case om eens kapot te normaliseren tot puur id + key-value pair, of juist in een schemaloos array van kvp's op te slaan. Wat ik in dit topic nog niet heb gezien is dat een heleboel van deze gegevens eigenlijk "sparse" zijn. Dat is dan data die meestal geen waarde heeft (of false bij een bit). Dat indexeer je in de praktijk anders, en ALS je ernaar op zoek bent begin je meestal een zoekopdracht met juist die gegevens. Denk hierbij bijvoorbeeld aan de attributen "wacht op keuring" of "staanplaatsen". Bij een schemaloze oplossing laat je ze dan weg.

Interessante materie. Hopelijk vind ik komende week wat tijd om de dataset te downloaden (uit andere topic begreep ik dat de download rampzalig is) en deze op mongodb te projecteren. Leuk experimenteer werk. Als ik iets nuttigs vind meld ik me weer, maar in elk geval bedankt voor de inspiratie

[ Voor 6% gewijzigd door TweakMDS op 22-02-2015 18:49 ]