Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Schlechte Performance von SQL Abfragen in einer großen Datenbank
48 views
Skip to first unread message
Aeneas A.
Jul 6, 2012, 6:49:45 PM7/6/12
to
Hallo,
ich arbeite zurzeit an einem Projekt, was sich mit der Referenzierung
zwischen Büchern beschäftigt.
Ich bin leider noch ein totaler Anfänger was die Programmierung mit SQL
betrifft.
In einer DB sind Bücher mit id, Titel, Autor, Zeitschrift, Jahr hinterlegt.
Der Nutzer tippt seinen Sucgh-String ein und es wird in der Titelspalte der
Bücher gesucht. Es erscheinen die gesuchten Bücher UND die Bücher die auf
die gesuchten referenzieren mittels Grafikbibliothek "D3". Umso mehr
Referenzen ein Buch hat umso wichtiger ist es für den User.
Die DB ist 1 GB groß, enthält über 1,5 Mio Einträge und ist lokal auf
meinem Rechner gespeichert.
Für die Abfragen verwendete Tabellen und Spalten:
tbl (Spalten): author_writes_pub ( author, publication)
publication ( id, title, year, citationID, venue )
pub_cites_pub (citing_id, cited_id)
Die Abfragen sind in einem PHP Code eingebettet und mittels While-Schleifen
verbunden. Die Performance leidet darunter deutlich.
An der DB wurden bisher keine Veränderungen vorgenommen. Ein Index ist
nicht vorhanden.
Mittels IN BOOLEAN MODE konnte ich eine bessere Performance erreichen, doch
leider führt dies immernoch zu einem timeout.
Kann man die SQL Anfragen soweit zusammenfassen, das sich die while
Schleifen auf ein Minimum reduzieren?
Anbei der Code-Ausschnitt:
$sql = 'SELECT id, title, year, venue FROM publication WHERE MATCH (title)
AGAINST (\' "' . $dbsuche . '" \' IN BOOLEAN MODE)';
$sql2 = 'SELECT author FROM author_writes_pub WHERE publication =' .
$id_db1); // hier wird der dazugehörige Autor abgefragt
sql3 = 'SELECT citing_id FROM pub_cites_pub WHERE cited_id =' . $id_db1);
// hier werden die IDs der Bücher ausgegeben die auf id_db1 referenzieren
sql4 = 'SELECT author FROM author_writes_pub WHERE publication =' .
$citing_db2); // hier werden die Autoren zu $citing_db2 ausgegeben
sql5 = 'SELECT title, year, venue FROM publication WHERE id =' .
$citing_db2); //hier die Titel zu $citing_db2
Vielen Dank für eure Hilfe
Beste Grüße
Aeneas
ich arbeite zurzeit an einem Projekt, was sich mit der Referenzierung
zwischen Büchern beschäftigt.
Ich bin leider noch ein totaler Anfänger was die Programmierung mit SQL
betrifft.
In einer DB sind Bücher mit id, Titel, Autor, Zeitschrift, Jahr hinterlegt.
Der Nutzer tippt seinen Sucgh-String ein und es wird in der Titelspalte der
Bücher gesucht. Es erscheinen die gesuchten Bücher UND die Bücher die auf
die gesuchten referenzieren mittels Grafikbibliothek "D3". Umso mehr
Referenzen ein Buch hat umso wichtiger ist es für den User.
Die DB ist 1 GB groß, enthält über 1,5 Mio Einträge und ist lokal auf
meinem Rechner gespeichert.
Für die Abfragen verwendete Tabellen und Spalten:
tbl (Spalten): author_writes_pub ( author, publication)
publication ( id, title, year, citationID, venue )
pub_cites_pub (citing_id, cited_id)
Die Abfragen sind in einem PHP Code eingebettet und mittels While-Schleifen
verbunden. Die Performance leidet darunter deutlich.
An der DB wurden bisher keine Veränderungen vorgenommen. Ein Index ist
nicht vorhanden.
Mittels IN BOOLEAN MODE konnte ich eine bessere Performance erreichen, doch
leider führt dies immernoch zu einem timeout.
Kann man die SQL Anfragen soweit zusammenfassen, das sich die while
Schleifen auf ein Minimum reduzieren?
Anbei der Code-Ausschnitt:
$sql = 'SELECT id, title, year, venue FROM publication WHERE MATCH (title)
AGAINST (\' "' . $dbsuche . '" \' IN BOOLEAN MODE)';
$sql2 = 'SELECT author FROM author_writes_pub WHERE publication =' .
$id_db1); // hier wird der dazugehörige Autor abgefragt
sql3 = 'SELECT citing_id FROM pub_cites_pub WHERE cited_id =' . $id_db1);
// hier werden die IDs der Bücher ausgegeben die auf id_db1 referenzieren
sql4 = 'SELECT author FROM author_writes_pub WHERE publication =' .
$citing_db2); // hier werden die Autoren zu $citing_db2 ausgegeben
sql5 = 'SELECT title, year, venue FROM publication WHERE id =' .
$citing_db2); //hier die Titel zu $citing_db2
Vielen Dank für eure Hilfe
Beste Grüße
Aeneas
Patrik Schindler
Jul 7, 2012, 9:42:09 AM7/7/12
to
Hallo,
In article <1p5sqx8p29vuy$.1nnk0123yd7i5$.d...@40tude.net>,
der Tabellen, welche mit WHERE referenziert werden.
Dann empfehle ich Dir die Suche nach tuning_primer. Das ist ein
Shellscript, was die MySQL-Konfiguration mit den Laufzeitvariablen
vergleicht und daraufhin Vorschläge zu Optimierungen macht.
Zur Zusammenfassung und möglichen Optimierung empfehle ich Dir, im Web
nach Quellen zur Erklärung und Benutzung von JOINs zu recherchieren und
diese anzuwenden. Felder von mittels JOIN verknüpften Tabellen
(ON-Statement) sollten ebenfalls einen Index besitzen.
Diese Schritte sollten Dir einen erheblichen Geschwindigkeitsgewinn
bringen.
Viel Erfolg!
:wq! PoC
In article <1p5sqx8p29vuy$.1nnk0123yd7i5$.d...@40tude.net>,
"Aeneas A." <rtd...@gmx.de> wrote:
> An der DB wurden bisher keine Veränderungen vorgenommen. Ein Index ist
> nicht vorhanden.
Das ist der erste und wichtigste Schritt. Erstelle Indexe auf die Felder
> An der DB wurden bisher keine Veränderungen vorgenommen. Ein Index ist
> nicht vorhanden.
der Tabellen, welche mit WHERE referenziert werden.
Dann empfehle ich Dir die Suche nach tuning_primer. Das ist ein
Shellscript, was die MySQL-Konfiguration mit den Laufzeitvariablen
vergleicht und daraufhin Vorschläge zu Optimierungen macht.
Zur Zusammenfassung und möglichen Optimierung empfehle ich Dir, im Web
nach Quellen zur Erklärung und Benutzung von JOINs zu recherchieren und
diese anzuwenden. Felder von mittels JOIN verknüpften Tabellen
(ON-Statement) sollten ebenfalls einen Index besitzen.
Diese Schritte sollten Dir einen erheblichen Geschwindigkeitsgewinn
bringen.
Viel Erfolg!
:wq! PoC
Aeneas A.
Jul 7, 2012, 9:54:26 AM7/7/12
to
vielen Dank für deine Antwort. Der Tipp mit der Indexierung ist Gold wert.
Die Ausgabe baut sich jetzt in wenigen Sekunden auf und das ohne timeout.
Danke für deine Hilfe.
Beste Grüße
Aeneas
Andreas Scherbaum
Jul 8, 2012, 4:20:47 PM7/8/12
to
Patrik Schindler <p...@pocnet.net> wrote:
>
> Das ist der erste und wichtigste Schritt. Erstelle Indexe auf die Felder
> der Tabellen, welche mit WHERE referenziert werden.
Zweiter Schritt: überprüfe, ob der Index auch sinnvoll verwendet
>
> Das ist der erste und wichtigste Schritt. Erstelle Indexe auf die Felder
> der Tabellen, welche mit WHERE referenziert werden.
wird (Kardinalität ist hoch genug) oder ob dadurch nur unnötig
Performance zum Aktualisieren des Index verbraucht wird.
--
Andreas 'ads' Scherbaum
Failure is not an option. It comes bundled with your Microsoft product.
(Ferenc Mantfeld)
Aeneas A.
Jul 9, 2012, 8:44:35 PM7/9/12
to
Am Sat, 07 Jul 2012 15:42:09 +0200 schrieb Patrik Schindler:
query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
$searchterm + "' IN BOOLEAN MODE))
UNION
SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
$searchterm + "' IN BOOLEAN MODE))
) AS tbl1
Diese braucht sehr lange (2 min 30 sek), um ausgeführt zu werden. Meine
verschachtelten while-Schleifen, wurden dagegen nach dem Indexierungs Tipp
mit hoher Geschwindigkeit ausgeführt. An was kann es liegen, dass die oben
genannte Funktion so lange brauch um die Ergebnistabelle wiederzugeben?
Die SQL DB ist statisch. Es liegt ein Index auf cited_id und citing_id. Und
die publication Tabelle ist für die Volltextsuche indexiert.
Beste Grüße
Claus Reibenstein
Jul 10, 2012, 1:57:52 AM7/10/12
to
Aeneas A. schrieb:
> Diese braucht sehr lange (2 min 30 sek), um ausgeführt zu werden. Meine
> verschachtelten while-Schleifen [...]
... interessieren hier nicht. Hier ist MySQL, nicht PHP. Fragen zu PHP
stellst Du besser in de.comp.lang.php.
Gruß
Claus
> Diese braucht sehr lange (2 min 30 sek), um ausgeführt zu werden. Meine
... interessieren hier nicht. Hier ist MySQL, nicht PHP. Fragen zu PHP
stellst Du besser in de.comp.lang.php.
Gruß
Claus
Thomas Rachel
Jul 10, 2012, 12:24:43 PM7/10/12
to
Am 10.07.2012 02:44 schrieb Aeneas A.:
> Ich habe von meinem Betreuer folgende Funktion bekommen:
>
> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> UNION
> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> ) AS tbl1
>
> Diese braucht sehr lange (2 min 30 sek), um ausgeführt zu werden. Meine
> verschachtelten while-Schleifen, wurden dagegen nach dem Indexierungs Tipp
> mit hoher Geschwindigkeit ausgeführt. An was kann es liegen, dass die oben
> genannte Funktion so lange brauch um die Ergebnistabelle wiederzugeben?
Ist mir auf den ersten Blick nicht ersichtlich. Kannst Du mal die
> Ich habe von meinem Betreuer folgende Funktion bekommen:
>
> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> UNION
> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> ) AS tbl1
>
> Diese braucht sehr lange (2 min 30 sek), um ausgeführt zu werden. Meine
> verschachtelten while-Schleifen, wurden dagegen nach dem Indexierungs Tipp
> mit hoher Geschwindigkeit ausgeführt. An was kann es liegen, dass die oben
> genannte Funktion so lange brauch um die Ergebnistabelle wiederzugeben?
Ausgabe von EXPLAIN SELECT ... posten?
Thomas
Aeneas Al-Shakirche
Jul 10, 2012, 2:57:15 PM7/10/12
to
anbei die EXPLAIN Ausgabe:
| id | select_type | table | type | possible_keys | ke
y | key_len | ref | rows | Extra |
+----+--------------------+---------------+-----------------+---------------+---
------+---------+------+---------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NU
LL | NULL | NULL | 503 | Using temporary |
| 2 | DERIVED | pub_cites_pub | index | NULL | PR
IMARY | 16 | NULL | 2083929 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | publication | unique_subquery | PRIMARY,title | PR
IMARY | 8 | func | 1 | Using where |
| 4 | UNION | pub_cites_pub | index | NULL | PR
IMARY | 16 | NULL | 2083929 | Using where; Using index |
| 5 | DEPENDENT SUBQUERY | publication | unique_subquery | PRIMARY,title | PR
IMARY | 8 | func | 1 | Using where |
| NULL | UNION RESULT | <union2,4> | ALL | NULL |
NULL | NULL | NULL | NULL | |
+----+--------------------+---------------+-----------------+---------------+---
------+---------+------+---------+--------------------------+
6 rows in set (4 min 6.86 sec)
Aeneas
Sebastian Suchanek
Jul 11, 2012, 9:05:12 AM7/11/12
to
Am 10.07.2012 20:57, schrieb Aeneas Al-Shakirche:
> [...]
> anbei die EXPLAIN Ausgabe:
> [...]
Kleiner Tip: Nimm "EXPLAIN SELECT ... \G", dann bekommst Du eine
Ausgabeformatierung, die besser zu eher knapp bemessenen
Usenet-Zeilenl�ngen pa�t.
HTH,
Sebastian
> [...]
> anbei die EXPLAIN Ausgabe:
> [...]
Kleiner Tip: Nimm "EXPLAIN SELECT ... \G", dann bekommst Du eine
Ausgabeformatierung, die besser zu eher knapp bemessenen
Usenet-Zeilenl�ngen pa�t.
HTH,
Sebastian
Aeneas Al-Shakirche
Jul 11, 2012, 9:34:40 AM7/11/12
to
Am Tue, 10 Jul 2012 18:24:43 +0200 schrieb Thomas Rachel:
> Am 10.07.2012 02:44 schrieb Aeneas A.:
>
>> Ich habe von meinem Betreuer folgende Funktion bekommen:
>>
>> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
>> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> UNION
>> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> ) AS tbl1
>>
>> Diese braucht sehr lange (2 min 30 sek), um ausgef�hrt zu werden. Meine
>
>> Ich habe von meinem Betreuer folgende Funktion bekommen:
>>
>> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
>> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> UNION
>> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> ) AS tbl1
>>
>> verschachtelten while-Schleifen, wurden dagegen nach dem Indexierungs Tipp
>> mit hoher Geschwindigkeit ausgef�hrt. An was kann es liegen, dass die oben
>> genannte Funktion so lange brauch um die Ergebnistabelle wiederzugeben?
>
> Ist mir auf den ersten Blick nicht ersichtlich. Kannst Du mal die
> Ausgabe von EXPLAIN SELECT ... posten?
>
>
> Thomas
Hier noch mal die besser lesbare EXPLAIN SELECT Ausgabe:
>
> Ist mir auf den ersten Blick nicht ersichtlich. Kannst Du mal die
> Ausgabe von EXPLAIN SELECT ... posten?
>
>
> Thomas
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 503
Extra: Using temporary
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: pub_cites_pub
type: index
possible_keys: NULL
key: PRIMARY
key_len: 16
ref: NULL
rows: 2083929
Extra: Using where; Using index
*************************** 3. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: publication
type: unique_subquery
possible_keys: PRIMARY,title
key: PRIMARY
key_len: 8
ref: func
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 4
select_type: UNION
table: pub_cites_pub
type: index
possible_keys: NULL
key: PRIMARY
key_len: 16
ref: NULL
rows: 2083929
Extra: Using where; Using index
*************************** 5. row ***************************
id: 5
select_type: DEPENDENT SUBQUERY
table: publication
type: unique_subquery
possible_keys: PRIMARY,title
key: PRIMARY
key_len: 8
ref: func
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: NULL
select_type: UNION RESULT
table: <union2,4>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
6 rows in set (6 min 30.50 sec)
Aeneas
Peter J. Holzer
Jul 16, 2012, 6:33:31 AM7/16/12
to
On 2012-07-10 00:44, Aeneas A. <rtd...@gmx.de> wrote:
> Ich habe von meinem Betreuer folgende Funktion bekommen:
>
> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> UNION
> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> ) AS tbl1
>
> Diese braucht sehr lange (2 min 30 sek), um ausgef�hrt zu werden.
> Ich habe von meinem Betreuer folgende Funktion bekommen:
>
> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> UNION
> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
> $searchterm + "' IN BOOLEAN MODE))
> ) AS tbl1
>
MySQL tendiert leider dazu, Subqueries verkehrt herum auszuf�hren, also
zuerst die �u�ere Query und dann f�r jede Row des Resultats die innere
Query (wenn ich den Plan, den Du anschlie�end gepostet hast, richtig
interpretiere, ist das auch hier der Fall).
Wenn Du die Query als Join formulierst, wird es fast sicher schneller.
hp
--
_ | Peter J. Holzer | Deprecating human carelessness and
|_|_) | Sysadmin WSR | ignorance has no successful track record.
| | | h...@hjp.at |
__/ | http://www.hjp.at/ | -- Bill Code on as...@irtf.org
Thomas 'PointedEars' Lahn
Jul 16, 2012, 5:04:59 PM7/16/12
to
Peter J. Holzer wrote:
> On 2012-07-10 00:44, Aeneas A. <rtd...@gmx.de> wrote:
>> Ich habe von meinem Betreuer folgende Funktion bekommen:
>>
>> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
>> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> UNION
>> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> ) AS tbl1
>>
>> Diese braucht sehr lange (2 min 30 sek), um ausgeführt zu werden.
> On 2012-07-10 00:44, Aeneas A. <rtd...@gmx.de> wrote:
>> Ich habe von meinem Betreuer folgende Funktion bekommen:
>>
>> query ="SELECT DISTINCT tbl1.citing_id,tbl1.cited_id FROM (
>> SELECT * FROM `pub_cites_pub` WHERE citing_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> UNION
>> SELECT * FROM `pub_cites_pub` WHERE cited_id IN (
>> SELECT id FROM `publication` WHERE MATCH (title) AGAINST ('"+
>> $searchterm + "' IN BOOLEAN MODE))
>> ) AS tbl1
>>
Da wäre noch die Frage, ob `SELECT *' sein muss/sollte (ich meine: nein) und
obj MATCH(…), d. h. natürlichsprachliche Volltextsuche über eine
Textsammlung, sein muss.
> MySQL tendiert leider dazu, Subqueries verkehrt herum auszuführen, also
> zuerst die äußere Query und dann für jede Row des Resultats die innere
> Query (wenn ich den Plan, den Du anschließend gepostet hast, richtig
> interpretiere, ist das auch hier der Fall).
s/leider//
Dies ermöglicht es, in der Subquery auf Werte aus der äusseren Abfrage
zuzugreifen. Jedoch wäre es gut, wenn hier optimiert würde, sofern die
Subquery genau das nicht tut. Wobei ich nicht sicher bin, ob das nicht
bereits passiert.
> Wenn Du die Query als Join formulierst, wird es fast sicher schneller.
Übung. Ist ja nicht das erste Mal, dass der OP seine Hausaufgaben nicht
selbst macht. Soifz [psf 10.1].
--
PointedEars
Please do not Cc: me. / Bitte keine Kopien per E-Mail.
Peter Kiederich
Jul 23, 2012, 4:57:57 AM7/23/12
to
Am 07.07.2012 15:42, schrieb Patrik Schindler:
> Hallo,
>
> In article <1p5sqx8p29vuy$.1nnk0123yd7i5$.d...@40tude.net>,
> "Aeneas A." <rtd...@gmx.de> wrote:
>
Hallo
> Hallo,
>
> In article <1p5sqx8p29vuy$.1nnk0123yd7i5$.d...@40tude.net>,
> "Aeneas A." <rtd...@gmx.de> wrote:
>
vieleicht hilft Dir das hier etwas weiter
http://pi.informatik.uni-siegen.de/lehre/2007s/LM/lm_spv_20070626_a5.pdf
Gruￜ Peter
0 new messages