Große strings durchsuchen

chrisi303

chrisi303

Aktiver Benutzer
Beiträge
32
Ich möchte such Ergebnisse von Google auslesen und in eine Datenbank schreiben. In meiner der aktuellen Version lese ich die Ergebnisse aus und schreibe jedes Ergebnis ein eine neu Zeile. Das wir leider sehr schnell ziemlich langsam (ca. 4000 Suchbegriffe * 100 Ergebnisse pro Woche).
Jetzt versuche ich ein begrifflich pro Zeile und die eigentlichen Ergebnisse in einem json zu speichern.
der string sieht dann so aus
+--------------------------+
|data |
+--------------------------+
|{"paltz":1, "url": "www.seite1.de", "page" : "www"},{"paltz":2, "url": "www.seite2.de", "page" : "www"}, ....|
+--------------------------+

das klappt aber auch nicht wirklich. Da ich nicht richtig suche kann z.B. alle Erlebnisse wo seite1.de enthalten ist.
 
Werbung:
hi danke für den Tipp. habe es gleich versucht umzusetzen habe aber immer noch das Problem das ich nicht mit dem SELECT zurecht komme:

Code: 
CREATE TABLE "serps_week" (
    "keyword" VARCHAR(200) NULL DEFAULT NULL,
    "kw" VARCHAR(200) NULL DEFAULT NULL,
    "serps" JSON NULL DEFAULT NULL
)

Code: 
INSERT INTO serps_week VALUES (
  'keyword1', '16_01',    '{ "paltz1": "www.seite1.de",  "paltz2": "www.seite2.de"}', ....);
Code: 
INSERT INTO serps_week VALUES (
  'keyword2', '16_01',    '{ "paltz1": "www.seite5.de",  "paltz2": "www.seite1.de" ,"paltz3": "www.seite4.de"}', ....);

wie kann ich die Abfrage gestalten so das ich nach einer Seite suchen kann. (Alle keywords für "www.seite1.de" und deren Plätze in der kw 16_01)

bekomme es nicht hin, habe wahrscheinlich einen Denk Fehler in der Sache wahrscheinlich muss ich den JSON umbauen.
 
Deine Json-Daten sahen im ersten Post etwas anders aus, darauf aufbauend:

Code: 
test=# select * from serps_week ;
 keyword | kw  |  serps   
---------+-----+-----------------------------------------------------
 1  | kw1 | {"url": "www.seite1.de", "page": "www", "paltz": 1}
(1 row)

test=# \d serps_week
  Table "public.serps_week"
 Column  |  Type  |  Modifiers   
---------+------------------------+---------------------------------
 keyword | character varying(200) | default NULL::character varying
 kw  | character varying(200) | default NULL::character varying
 serps  | jsonb  |
Indexes:
  "blafasel" btree ((serps ->> 'url'::text))

test=# set enable_seqscan to off;
SET
test=# explain select * from serps_week where serps->>'url' = 'www';
  QUERY PLAN   
-----------------------------------------------------------------------------
 Index Scan using blafasel on serps_week  (cost=0.12..8.14 rows=1 width=868)
  Index Cond: ((serps ->> 'url'::text) = 'www'::text)
(2 rows)

test=#

Das set ist nötig, weil die Tabelle zu klein ist, da ist aktuell ein Seq-Scan billiger - daher verbiete ich den. Und er greift zum Index. Beachte, daß ich JSONB nehme als Datentyp. Das ist wichtig - JSON selbst ist nicht indexable. Geht ab PostgreSQL 9.4. - also schon sehr lange.
 
Vielen dank schon mal für deine Geduld. Prinzipiell klappt das jetzt aber leider nur mit
{"url": "www.seite1.de", "page": "www", "paltz": 1}
Ich bräuchte aber einen json mit allen 100 Plätzen.


Die Daten die ich bis jetzt habe sehen so aus damit funktioniert es nicht? Kann bwz. wie kann man ihr nach der url suchen?

Code: 
(7, 'Fliesen Arkim', '[{"platz":1,"url":"www.imolaceramica.com","page":"http:\\/\\/www.imolaceramica.com\\/de\\/"},{"platz":2,"url":"www.fliesenpark.de","page":"http:\\/\\/www.fliesenpark.de\\/arkim-fliesen.html"},{"platz":3,"url":"www.fliesen-schlicht.com","page":"http:\\/\\/www.fliesen-schlicht.com\\/bodenfliesen-arkim-.html"},{"platz":4,"url":"www.fliesen-schlicht.com","page":"http:\\/\\/www.fliesen-schlicht.com\\/bodenfliesen-arkim-seite-1.html"},{"platz":6,"url":"tile.expert","page":"http:\\/\\/tile.expert\\/de\\/catalogue\\/Arkim"},{"platz":7,"url":"www.fliesen-discount24.de","page":"http:\\/\\/www.fliesen-discount24.de\\/catalogsearch\\/result\\/%3Fq%3Dimola%2520ceramica%2520arkim%2520loft"},{"platz":8,"url":"www.top-fliese.de","page":"http:\\/\\/www.top-fliese.de\\/imola-ceramica\\/"},{"platz":9,"url":"fliesen-holzmann.de","page":"http:\\/\\/fliesen-holzmann.de\\/"},{"platz":10,"url":"www.fliesenundplatten.de","page":"http:\\/\\/www.fliesenundplatten.de\\/cooperativa-ceramica-dimola-neue-ausstellung-in-historischer-halle\\/150\\/8893\\/"},{"platz":11,"url":"www.ebay-kleinanzeigen.de","page":"http:\\/\\/www.ebay-kleinanzeigen.de\\/s-anzeige\\/feinsteinzeug-bodenfliese-arkim-micron-format-30x30\\/233280451-84-1264"},{"platz":12,"url":"www.fliesenscholz.de","page":"http:\\/\\/www.fliesenscholz.de\\/lieferanten.html"},{"platz":13,"url":"www.fliesen-huemer.at","page":"http:\\/\\/www.fliesen-huemer.at\\/arkim-serie-koshi\\/"},{"platz":14,"url":"shop.kachelmann-ceramik.de","page":"http:\\/\\/shop.kachelmann-ceramik.de\\/index.php%3Fid%3D609"},{"platz":15,"url":"www.ebay.de","page":"http:\\/\\/www.ebay.de\\/itm\\/Fliesen-IMOLA-ARKIM-Koshi-30x60-dunkelgrau-36DG-weiss-36W-84x-Sockelfliesen-\\/151712714161"},{"platz":16,"url":"fliesen-polomski.de","page":"http:\\/\\/fliesen-polomski.de\\/ausstellung\\/"},{"platz":17,"url":"www.melzer-fliesen.de","page":"http:\\/\\/www.melzer-fliesen.de\\/index.php%3Fmid%3D2%26sid%3D1"},{"platz":18,"url":"www.hxwerker.com","page":"http:\\/\\/www.hxwerker.com\\/fliesen-imola-arkim-koshi-30x60-i507209\\/"},{"platz":19,"url":"www.baustoff-hoffmann.de","page":"http:\\/\\/www.baustoff-hoffmann.de\\/fliesen.php"},{"platz":20,"url":"www.fliesen-shop-arnold.de","page":"http:\\/\\/www.fliesen-shop-arnold.de\\/bodenfliesen\\/natursteinoptik-poliert\\/183\\/beige-60\\/60-cm-poliert-67-95-\\/qm"},{"platz":21,"url":"www.fliesen-center-arnold.com","page":"http:\\/\\/www.fliesen-center-arnold.com\\/sortiment.html"},{"platz":22,"url":"fliese.fliesen-preisliste.de","page":"http:\\/\\/fliese.fliesen-preisliste.de\\/f_marken.php%3Fmitglied%3D-1"},{"platz":23,"url":"www.franke-raumwert.de","page":"http:\\/\\/www.franke-raumwert.de\\/aktuelles\\/neu-im-shop-imola-arkim-micron-2-0\\/2013\\/05"},{"platz":24,"url":"www.fliesen-ks.com","page":"http:\\/\\/www.fliesen-ks.com\\/"},{"platz":25,"url":"www.fliesen-popp.de","page":"http:\\/\\/www.fliesen-popp.de\\/popp-ausstellungsmuster.html"},{"platz":26,"url":"www.fliesen-melzer.de","page":"http:\\/\\/www.fliesen-melzer.de\\/"},{"platz":27,"url":"www.fliesen-tenne.de","page":"http:\\/\\/www.fliesen-tenne.de\\/"},{"platz":28,"url":"www.fliesen-forum.com","page":"http:\\/\\/www.fliesen-forum.com\\/index.php\\/links"},{"platz":29,"url":"www.fliesenpiraten.de","page":"http:\\/\\/www.fliesenpiraten.de\\/3\\/Hersteller%2520und%2520Modelle.html"},{"platz":30,"url":"www.fliesen-steinmetz.de","page":"http:\\/\\/www.fliesen-steinmetz.de\\/galerie\\/fliesen\\/fliesen5b.htm"},{"platz":32,"url":"www.fliesen-kollwitz.de","page":"http:\\/\\/www.fliesen-kollwitz.de\\/index.php\\/ausstellung\\/sortiment"},{"platz":33,"url":"www.stone-unity.de","page":"http:\\/\\/www.stone-unity.de\\/Fliesen_Feinsteinzug.html"},{"platz":35,"url":"www.berlin.de","page":"http:\\/\\/www.berlin.de\\/special\\/kleinanzeigen\\/detail\\/351062826\\/"},{"platz":36,"url":"www.shpock.com","page":"http:\\/\\/www.shpock.com\\/i\\/VR_FO8xzHvRKi0W0\\/"},{"platz":37,"url":"www.konz-fliesenhandel.de","page":"http:\\/\\/www.konz-fliesenhandel.de\\/publish\\/72e916a5_7e90_43c1_7de7f19dab86a811.cfm"},{"platz":38,"url":"al-fliesenprofi.de","page":"http:\\/\\/al-fliesenprofi.de\\/produkte\\/"},
ich musste es kürzen war zu lang für den Post
 
Du kannst auch solche verschachtelten JSON-Dokumente indexieren, geh mal auf Google mit "postgres jsonb index" z.B., da findest eine Menge an Beispielen.
 
leider nicht die habe ich alle schon gelesen, die behandle alle zwar relativ komplexe json aber keinen der mehre gleiche Key´s enthält. Daher hatte ich schon überlegt ob ich das so umbaue

Code: 
{
platz1:
{url:'www.seite1.de',
page:'www.seite1.de/unterseite'
},
platz2:
{url:'www.seite2.de',
page:'www.seite2.de/unterseite'
}
}
da bleibt aber die gleiche Frage offen wir kann ich nach "url" suchen ohne den "platz x" anzugeben, denn will ich ja eigentlich wissen.
 
das ist jetzt eher eine JSON-Frage, oder? Da bin ich jetzt nicht sooo firm, aber vielleicht kommst Du zum Ziel, wenn Du das mal ganz sauber als DB-Struktur aufbaust und nach JSON dann konvertierst. Dann solltest Du ein valides JSON bekommen und hättest quasi eine Vorlage. Mal so alls Idee...
 
hallo, mir ist als DB-Struktur nicht eingefallen was derartige Mengen aufnehmen kann. Dashalb kam ich auf json das habe ich im letzten Projekt benutzt aber nur zum speichern der Daten, ich musste nicht in der Datenbank nach suchen.

Bisher ist alles in einer Tabelle in mysql
id |keyid (keyword aus anderer Tabelle) | paltz | url | page | kw

die einfachste abfrage war dann
select `keyword`, `platz` WHERE `url` = ' www.seite.de' AND kw = '16_01'

das tut genau was es soll, nur nach einiger Zeit wird das halt langsam oder ich lösche Einträge die älter als x Wochen sind und mehr keywords sind auch kaum möglich.
Siehst du noch eine andere Möglichkeit die Ergebnisse zu speichern.
Von der Größe her dachte ich an ca. 10000 bis 20000 Keywords das wäre für meine 10 Projekte ausreichend. Macht aber da schon 1.000.000 bis 2.000.000 Zeilen die Woche.
 
Nun mal Butte an die Fische.

Code: 
test=*# \d chrisi;
  Table "public.chrisi"
 Column  |  Type  |  Modifiers
---------+---------+-----------------------------------------------------
 id  | integer | not null default nextval('chrisi_id_seq'::regclass)
 keyword | text  |
 platz  | integer |
 url  | text  |
 page  | text  |
 kw  | text  |
Indexes:
  "chrisi_pkey" PRIMARY KEY, btree (id)

test=*# insert into chrisi (keyword, platz, url, page, kw) select 'keyword'||s::text, y, 'www.seite'||s::text||'.tld', 'www.seite'||s::text||'.tld/bla/fasel/blub/'||s::text,'kw_2016_'||k::int from generate_series(1,10000) s cross join generate_series(1,100) y cross join generate_series(1,30) k;
INSERT 0 30000000
test=*# commit;
COMMIT

Wir haben also nun mal zum Spaß 30 Millionen Zeilen drin. Ich erstelle einen Index:

Code: 
test=# create index idx1 on chrisi (url, kw);
CREATE INDEX

Wir picken uns mal einen Datensatz etwa aus der Mitte und suchen ihn dann mit Deinem Select:

Code: 
test=*# select * from chrisi where id = 15123456;
  id  |  keyword  | platz |  url  |  page  |  kw
----------+-------------+-------+-------------------+---------------------------------------+------------
 15123456 | keyword5315 |  1 | www.seite5315.tld | www.seite5315.tld/bla/fasel/blub/5315 | kw_2016_15
(1 row)

test=*# explain analyse select * from chrisi where url = 'www.seite5315.tld' and kw = 'kw_2016_15';
  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on chrisi  (cost=5.61..410.46 rows=102 width=83) (actual time=26.512..33.479 rows=100 loops=1)
  Recheck Cond: ((url = 'www.seite5315.tld'::text) AND (kw = 'kw_2016_15'::text))
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on idx1  (cost=0.00..5.58 rows=102 width=0) (actual time=26.499..26.499 rows=100 loops=1)
  Index Cond: ((url = 'www.seite5315.tld'::text) AND (kw = 'kw_2016_15'::text))
 Planning time: 0.131 ms
 Execution time: 33.536 ms
(7 rows)


Die Suche in 30 Millionen Rows dauert also 33 Millisekunden. Finde ich jetzt nicht so dolle lahm.
 
na was wohl? PostgreSQL.

PS.: mit warmen Cache:

Code: 
test=*# explain analyse select * from chrisi where url = 'www.seite5315.tld' and kw = 'kw_2016_15';
  QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on chrisi  (cost=28.25..2946.15 rows=750 width=136) (actual time=0.084..0.119 rows=100 loops=1)
  Recheck Cond: ((url = 'www.seite5315.tld'::text) AND (kw = 'kw_2016_15'::text))
  Heap Blocks: exact=2
  ->  Bitmap Index Scan on idx1  (cost=0.00..28.06 rows=750 width=0) (actual time=0.074..0.074 rows=100 loops=1)
  Index Cond: ((url = 'www.seite5315.tld'::text) AND (kw = 'kw_2016_15'::text))
 Planning time: 0.097 ms
 Execution time: 0.168 ms
(7 rows)

0,17 Millisekunden.
 
super vielen Dank für deine Geduld, dann wechsele ich nur die Datenbank und lass das mit dem json :). Wahrscheinliche noch ne dumme frage am Schluss gibt es da irgend welche Größenbegrenzungen für Tabellen.
 
Werbung:

Ähnliche Themen

B
Suchverfahren im dbms
Antworten
7
Aufrufe
5K
ukulele
ukulele
C
Textsuche in Datenbank
Antworten
7
Aufrufe
3K
chuchi
C
Zurück
Oben