Große strings durchsuchen

[chrisi303]

Hi wenn ich noch mal schnell reingrätschen darf. Ich habe die Tabelle anlegt und anfange an die Datensätze von der mysql in die postgresql zu übernehmen. Da bei ist mir aufgefallen das die Tabelle größer wir als die im mysql bei schön 207524 rows ist sie schon 69,2 MiB Groß.

CREATE TABLE "serps_from" (
    "id" INTEGER NOT NULL DEFAULT E'',
    "kw" VARCHAR(10) NULL DEFAULT E'0',
    "keyword" VARCHAR(200) NULL DEFAULT E'0',
    "deeplink" TEXT NULL DEFAULT E'0',
    "host" TEXT NULL DEFAULT E'0',
    "position" INTEGER NULL DEFAULT E'0',
    PRIMARY KEY ("id")
)
;

kann man noch was optimieren oder andere Datentypen nehmen?

 

[akretschmer]

Laß mich raten: du hattest Datensätze schon mal testweise drin und diese wieder gelöscht? PostgreSQL löscht beim DELETE nicht, sondern markiert die alten Rows nur als gelöscht. Aber keine Angst: im Gegensatz zu InnoDB kannst Du den physischen Platz auch wieder gewinnen: VACUUM FULL. Achtung: erfordert exclusive lock. Autovacuum wird aber auch so dafür sorgen, daß diese Bereiche später für neue Rows genutzt werden. Also ganz relaxt bleiben ;-)

KW stellt die Kalenderwoche dar, oder? Würde ich eher ein Datum nehmen, lä0t sich später besser mit rechnen. Die KW kannst daraus noch immer ableiten. Wenn die Daten immer nur hinten angehangen werden und bei der Suche das Datum bzw. ein Datumsbereich als Where-Kriterium gilt könntest Du so auch einfacher z.B. auf einen BRIN-Index setzen.

 

[akretschmer]

Btw.: eine nützliche Extension ist pgstattuple.

 

[akretschmer]

Btw.: ich würde die Default-Werte weglassen. Für einen INT macht '' als Default auch wenig Sinn.

 

[chrisi303]

Hallo ich habe mal alles zusammen geschustert und im Testbetrieb.
in der serps_form sind jetzt 2.883.676 rows macht 932,Mb. eine abfrage dauert
1 query: 0,000 sec. (+ 0,546 sec. network) läuft aber auf einem vm System ohne irgend welche optimierten, das ist schon mal in Ordnung.

 

[chrisi303]

habe noch ein Probelm bei dem ich nicht weiter komme

select serps_from.position, serps_from.keyword,  keyword.cpc, keyword.vol
                                    from serps_from
                                    INNER JOIN keyword
                                    on serps_from.keyword = keyword.keyword
                                    Where host = 'zu suchende url '
                                    and kw = 'aktuelle kw' order by position asc

gibt es eine Möglichkeit die Position der letzte Woche mit auszugeben?
Habe es momentan in php in der Ausgabe noch eine separat abfrage, die dauert alles Dings auch ca 0,640 sec und das dann ca 1500 mal. macht die Sache verdammt langsam

select * from serps_from
Where host = 'zu suchende url'
and kw = 'vor Woche'
and keyword = ' keyword'

order by position asc

habe schon viel nachgeschlagen finde aber kein richtigen Ansatz.

 

[akretschmer]

die Where-Condition anpassen und auch die kw mit ausgeben? Oder wäre dies zu einfach?

 

[chrisi303]

ja schon aber wie unterschied ich dann die Postion zu Kalenderwochen ? es soll quasi die Tabelle herauskommen

| Keyword | Position | Vorwoche | Suchvol. | cpc /* die Vorwoche als Postion oder als Veränderung +- Platz */

wenn ich die Vorkalenderwoche als or mit in die Where nehme bekomme ich dann 2 Wochen als ergebnis zurück.
Ich sehe aber kein weg das als schleife in die obige Tabellenform zu bekommen

 

[akretschmer]

Mit MySQL dann wirklich schwer, mit richtigen Datenbanken via Window-Funktionen trivial.

 

[chrisi303]

was wäre die richtig Datenbank? Es wäre in Postgresql die auf einem Windows 2012 läuft.

 

[akretschmer]

PG läuft auf Windows. Es gibt Installationspakete und auch eine graphische Oberfläche.

 

[chrisi303]

du hast mich doch schon bekehrt es läuft alles schon Postgresql, ich hoffe du meinst PG = Postgresql.

 

[akretschmer]

Ja ;-) Ich hab wohl den Faden verloren, sorry.

 

[chrisi303]

ich versuche es mal mit einer subquery die ich dann join muss.

 

[chrisi303]

okay aber irgend wie noch nicht ganz

select serps_from.position as "diese Woche", serps_from.keyword,  keyword.cpc, keyword.vol , lastweek.position as Vorwoche
from serps_from
INNER JOIN
             (select serps_from.position, serps_from.keyword
                from serps_from
                Where host = 'www.fliesenpark.de'
                and kw = '16_07'
            ) as lastweek
on lastweek.keyword = serps_from.keyword
INNER JOIN keyword
on serps_from.keyword = keyword.keyword
Where host = 'www.fliesenpark.de'
and kw = '16_08'
order by serps_from.position asc

ich habe zu ein paar keywords mehre ränkings pro domain, das sieht dann so aus. z.b. fliesenpark.de. Es ist so verkehrt es kann ja nur 1mal auf platz 1 stehen pro Wochen doese woche müsste es auch 1 bis 11 sein.
diese Woche | keyword | cpc | vol | vorwoche
1| Engers Metro| 0| 1
1 | casa legno naturale| | 20| 2
1| italienische fliesen online| | 0,58 | 70 1
1| fliesenpark.de| | 30| 11
1 | fliesenpark.de| | 30| 10
1| fliesenpark.de| | 30| 9
1| fliesenpark.de| | 30| 8
1| fliesenpark.de| | 30| 7
1| fliesenpark.de| | 30| 6
1| fliesenpark.de| | 30| 5
1| fliesenpark.de| | 30| 4
1| fliesenpark.de| | 30| 3
1| fliesenpark.de| | 30| 2
1| fliesenpark.de| | 30| 1