Abfrage optimieren

TecEye · 10. Feb. 2016

Code:

SELECT * FROM data_betriebe WHERE (code IN (SELECT connector FROM data_attribute WHERE liste = 'bet' AND type = '90d884efa7114c1a448e4a1b0e7fd037' AND value = 'Elektro Düsseldorf'))

bei 2.000 Betrieben und 10.000 Attributen, dauert das bis zu einer Minute bis ein Ergebnis kommt, jemand n Plan zur Optimierung? Danke schonmal

tsseh · 10. Feb. 2016

TecEye schrieb:
Code:

SELECT * FROM data_betriebe WHERE (code IN (SELECT connector FROM data_attribute WHERE liste = 'bet' AND type = '90d884efa7114c1a448e4a1b0e7fd037' AND value = 'Elektro Düsseldorf'))

bei 2.000 Betrieben und 10.000 Attributen, dauert das bis zu einer Minute bis ein Ergebnis kommt, jemand n Plan zur Optimierung? Danke schonmal

anstelle erst alle einträge passender liste, typs und values zusammenzustellen und dann darin nach dem conbnector zu suchen,
such doch alle codes die den passenden connector haben und die passende liste und den passenden value und den passenden typ
und dann solltest du was einbauen, dass clientseitig eine lange aktion kenntlich macht, datenbanken sind nunmal langsam

dertypdernixkan · 10. Feb. 2016

Du solltest den Sternchen-Platzhalter nicht benutzen, nur die Felder auswählen die du auch wirklich brauchst.

Vielleicht auch keine Subselects, damit habe ich auch eher ungünstige Erfahrunegn gemacht, wenn es nicht anders geht solltest du vielleicht auch über Limits nachdenken und den Rest dynamisch nachladen?

tsseh · 10. Feb. 2016

dertypdernixkan schrieb:
Du solltest den Sternchen-Platzhalter nicht benutzen, nur die Felder auswählen die du auch wirklich brauchst.

Vielleicht auch keine Subselects

nee, anders herum, keine Subselects, dann vielleicht die auswahl einschränken, wenn es geht. das ist hier aber ein tropfen auf den heißen stein.
er stellt erst ne riesige liste zusammen über wahnsinnige stringvergleiche, in der dann wieder einträge wegfallen. deswegen als erstes den code in der connectorspalte überprüfen, stimmt der nicht, lohnt es nicht mehr den typ von 32 byte zu vergleichen. es sei denn der code ist wieder ein string von zig zeichen, dann erst die liste, dann das mit den 2t-wenigsten zeichen usw.

kkapsner · 11. Feb. 2016

Kannst du mal ein Bild von deinem Datenmodell machen. Ich habe den Eindruck, dass dieses schon nicht ideal ist.

PS: Es könnte ev. auch helfen, wenn du ein paar strategisch günstige Indices erstellst.

TecEye · 11. Feb. 2016

also es gibt die DB lib_betriebe da sind wie der name schon sagt alle betriebe drin unique ist hier der code(md5).

dann kann man Arrtibute anlegen, das ist eine Art Formulareditor, man legt dort also irgendein (oder mehrere) individuelles Feld an, zB. Schugröße, dieses hat auch als code einen unique md5

und dann gibt es die data_attribute, wenn ich also einen betrieb öffne und die Attribute anzeigen lasse und ein feld von denen ausfülle, wird dieses value in der data_attribute angelegt, mit dem type = sys_attribute.code und dem connector = lib_betriebe.code

so also wenn ich ein suchanfrage starten will dann suche ich alle betriebe mit Schuhgröße 43,
also muss ja geguckt werden, suche sys_betriebe.code = data_attribute.connector, type=(md5 von Schuhgröße) und value = 43

Hoffe ich konnte es so erklären, sonst zeichne ich es gern auch mal auf

Hab gelesen Subquerys sollen sehr langsam sein, aber welche methode gäb es denn noch? INNER JOIN geht nicht

- - - Aktualisiert - - -

kkapsner schrieb:
PS: Es könnte ev. auch helfen, wenn du ein paar strategisch günstige Indices erstellst.

Ich habe noch keine Indexes gesetzt, startegisch gut wäre aber alles was mit code und connector zusammenhängt oder?

kkapsner · 11. Feb. 2016

TecEye schrieb:
INNER JOIN geht nicht

Warum nicht? Auf code mit connector sollte es eigentlich gehen...

Aber ob das die ganze Sache schneller macht, ist auch fraglich.

TecEye schrieb:
was mit code und connector zusammenhängt

Wahrscheinlich. Probier's aus.

Aber generell sieht mir dein ganzen Unterfangen so aus, als ob das das nicht besonders für (My?)SQL geeignet ist. Mir scheint, als solltest du besser eine nicht-relationale DB verwenden... also einfach irgendwas, wo du kein festes Schema hast und beliebige Attribute an deine Datensätze hängen kannst.

tsseh · 11. Feb. 2016

also mit meinen minitabellen bekomme ich folgende werte:

Code:

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                              |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00047250 | SELECT * FROM xxx WHERE (code IN (SELECT connector FROM yyy WHERE liste = 'bet' AND type = 'AE72B8E596D04a72A79C56235ED7BAA6' AND value = 'Elektro D?sseldorf'))   |
|        2 | 0.00046075 | SELECT * FROM xxx INNER JOIN yyy ON (xxx.code = yyy.connector) WHERE liste = "bet" AND type = "AE72B8E596D04a72A79C56235ED7BAA6" AND value = "Elektro D?sseldorf"  |
|        3 | 0.00035200 | SELECT * FROM xxx, yyy WHERE xxx.code = yyy.connector AND yyy.liste = "bet" AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" AND yyy.value = "Elektro D?sseldorf" |
|        4 | 0.00045925 | SELECT * FROM xxx WHERE (code IN (SELECT connector FROM yyy WHERE liste = 'bet' AND type = 'AE72B8E596D04a72A79C56235ED7BAA6' AND value = 'Elektro D?sseldorf'))   |
|        5 | 0.00045500 | SELECT * FROM xxx WHERE (code IN (SELECT connector FROM yyy WHERE liste = 'bet' AND type = 'AE72B8E596D04a72A79C56235ED7BAA6' AND value = 'Elektro D?sseldorf'))   |
|        6 | 0.00048375 | SELECT * FROM xxx INNER JOIN yyy ON (xxx.code = yyy.connector) WHERE liste = "bet" AND type = "AE72B8E596D04a72A79C56235ED7BAA6" AND value = "Elektro D?sseldorf"  |
|        7 | 0.00037975 | SELECT * FROM xxx INNER JOIN yyy ON (xxx.code = yyy.connector) WHERE liste = "bet" AND type = "AE72B8E596D04a72A79C56235ED7BAA6" AND value = "Elektro D?sseldorf"  |
|        8 | 0.00037450 | SELECT * FROM xxx, yyy WHERE xxx.code = yyy.connector AND yyy.liste = "bet" AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" AND yyy.value = "Elektro D?sseldorf" |
|        9 | 0.00037375 | SELECT * FROM xxx, yyy WHERE xxx.code = yyy.connector AND yyy.liste = "bet" AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" AND yyy.value = "Elektro D?sseldorf" |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

die tabellen

Code:

mysql> SELECT * FROM xxx;
+----------------------------------+------+------+------+------------+
| code                             | id   | id2  | id3  | datum      |
+----------------------------------+------+------+------+------------+
| F4BB14553899491294D895823F50F149 |    1 |  123 |  247 | 2002-01-01 |
| 7E4D581BC4DB4b298616C0E822C041F3 |    2 |  456 |  233 | 2002-01-01 |
| E2B6C5B741794dd39F938500E32B281F |    3 |  456 |  103 | 2002-12-02 |
| B83DA8B8F95C4dbcAC6D1C006082B1A2 |    4 |  456 |  103 | 2002-12-01 |
| CE8345F4F58C46e1B1422428CA861D13 |    5 |  789 |  103 | 2002-12-01 |
| 2F1141F7B3804cd9884904FF58F9F163 |    6 |  987 |  103 | 2002-12-01 |
| A55723E1E0ED4e51BF7AA11C885686B8 |    7 |  654 |  103 | 2002-12-01 |
| FC153880AF0F49d7866E354C8B4BEB6A |    8 |  321 |  103 | 2002-12-01 |
+----------------------------------+------+------+------+------------+

Code:

mysql> SELECT * FROM yyy;
+----------------------------------+-------+----------------------------------+--------------------+
| connector                        | liste | type                             | value              |
+----------------------------------+-------+----------------------------------+--------------------+
| F4BB14553899491294D895823F50F149 | bet   | AE72B8E596D04a72A79C56235ED7BAA6 | Elektro Düsseldorf |
| F4BB14553899491294D895823F50F149 | bet   | D076B880841A45b19CACEF75451AFEBB | Elektro Düsseldorf |
| F4BB14553899491294D895823F50F149 | bet   | AAA37832971F49ddBDA63B104A492EB5 | Elektro Düsseldorf |
| 7E4D581BC4DB4b298616C0E822C041F3 | bet   | AE72B8E596D04a72A79C56235ED7BAA6 | Elektro Düsseldorf |
| 7E4D581BC4DB4b298616C0E822C041F3 | bet   | D076B880841A45b19CACEF75451AFEBB | Elektro Düsseldorf |
| 7E4D581BC4DB4b298616C0E822C041F3 | bet   | AAA37832971F49ddBDA63B104A492EB5 | Elektro Düsseldorf |
| B83DA8B8F95C4dbcAC6D1C006082B1A2 | bet   | AE72B8E596D04a72A79C56235ED7BAA6 | Elektro Düsseldorf |
| B83DA8B8F95C4dbcAC6D1C006082B1A2 | bet   | D076B880841A45b19CACEF75451AFEBB | Elektro Düsseldorf |
| CE8345F4F58C46e1B1422428CA861D13 | bet   | AE72B8E596D04a72A79C56235ED7BAA6 | Elektro Düsseldorf |
| CE8345F4F58C46e1B1422428CA861D13 | bet   | AAA37832971F49ddBDA63B104A492EB5 | Elektro Düsseldorf |
| 2F1141F7B3804cd9884904FF58F9F163 | bat   | AE72B8E596D04a72A79C56235ED7BAA6 | Elektro Düsseldorf |
| 2F1141F7B3804cd9884904FF58F9F163 | bat   | D076B880841A45b19CACEF75451AFEBB | Elektro Düsseldorf |
| 2F1141F7B3804cd9884904FF58F9F163 | bat   | AAA37832971F49ddBDA63B104A492EB5 | Elektro Düsseldorf |
| A55723E1E0ED4e51BF7AA11C885686B8 | bet   | AE72B8E596D04a72A79C56235ED7BAA6 | Erotik Maier       |
| A55723E1E0ED4e51BF7AA11C885686B8 | bet   | D076B880841A45b19CACEF75451AFEBB | Erotik Maier       |
| A55723E1E0ED4e51BF7AA11C885686B8 | bet   | AAA37832971F49ddBDA63B104A492EB5 | Erotik Maier       |
| FC153880AF0F49d7866E354C8B4BEB6A | bet   | F50F8314DD494d718CA7DBF1BD00A1C6 | Elektro Düsseldorf |
| FC153880AF0F49d7866E354C8B4BEB6A | bet   | D076B880841A45b19CACEF75451AFEBB | Elektro Düsseldorf |
| FC153880AF0F49d7866E354C8B4BEB6A | bet   | AAA37832971F49ddBDA63B104A492EB5 | Elektro Düsseldorf |
+----------------------------------+-------+----------------------------------+--------------------+

mach mal den test bei dir mit echtem inhalt

- - - Aktualisiert - - -

und jetzt noch nach länge(wenn liste immer der kürzeste string ist)

Code:

|       10 | 0.00035600 | SELECT * FROM xxx, yyy WHERE yyy.liste = "bet" AND xxx.code = yyy.connector AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" AND yyy.value = "Elektro D?sseldorf" |
|       11 | 0.00035375 | SELECT * FROM xxx, yyy WHERE yyy.liste = "bet" AND xxx.code = yyy.connector AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" AND yyy.value = "Elektro D?sseldorf" |
|       12 | 0.00034275 | SELECT * FROM xxx, yyy WHERE yyy.liste = "bet" AND xxx.code = yyy.connector AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" AND yyy.value = "Elektro D?sseldorf" |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

wenn liste immer der kürzeste string ist und value der 2t kürzeste

Code:

|       13 | 0.00030700 | SELECT * FROM xxx, yyy WHERE yyy.liste = "bet" AND yyy.value = "Elektro D?sseldorf" AND xxx.code = yyy.connector AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" |
|       14 | 0.00037000 | SELECT * FROM xxx, yyy WHERE yyy.liste = "bet" AND yyy.value = "Elektro D?sseldorf" AND xxx.code = yyy.connector AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" |
|       15 | 0.00032700 | SELECT * FROM xxx, yyy WHERE yyy.liste = "bet" AND yyy.value = "Elektro D?sseldorf" AND xxx.code = yyy.connector AND yyy.type = "AE72B8E596D04a72A79C56235ED7BAA6" |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

TecEye · 11. Feb. 2016

tsseh schrieb:
also mit meinen minitabellen bekomme ich folgende werte:

Wahnsinn was du dir für eine Mühe gemacht hast, danke dir

scheint also als ob implizite JOINs wohl das schnellste wäre, dann werd ich das nochmal versuchen, hatte damals bei dieser methode allersings mehr ergebnisse erhalten als es sollten, aber ich checks nochmal ab

Zusammen mit Index ev ein guter weg.

@kkapsner: welche DB würdest du vorschlagen? nosql? Aber ein Projekt dieser Größenordnung jetzt auf ein neues Datenbanksystem umzubauen dauert wahrscheinlich Monate :/

- - - Aktualisiert - - -

Leute ihr seid die geilsten, die Kombination aus all euren Tipps (Längensortierung, SELECT-Eingrenzung, implizites INNER JOIN und index setzen) hat die Abfrage von 4,3min auf unglaubliche 185ms gedonnert, buhjaaaaaaaa - DANKE

kkapsner · 11. Feb. 2016

TecEye schrieb:
buhjaaaaaaaa

So soll's sein!

TecEye schrieb:
welche DB würdest du vorschlagen?

K.A. - das kommt auf andere Faktoren/Randbedingungen an. Aber ich kann da sowieso keinen Vorschlag geben, da ich mich nur periphär damit beschäftigt hab'. Dein Problem klingt nur einfach danach.

TecEye schrieb:
dauert wahrscheinlich Monate

Wahrscheinlich...

TecEye · 17. Feb. 2016

sagt mal der index, wenn ich den in einer spalte gesetzt habe, haben neue einträge dann auch den index oder muss ich dann neu setzen?

kkapsner · 17. Feb. 2016

Neue Einträge sind automatisch im Index.

Abfrage optimieren

TecEye

New member

tsseh

Lounge-Member

dertypdernixkan

New member

tsseh

Lounge-Member

kkapsner

Super Moderator

TecEye

New member

kkapsner

Super Moderator

tsseh

Lounge-Member

TecEye

New member

kkapsner

Super Moderator

TecEye

New member

kkapsner

Super Moderator