[SQL] Abfrage dauert zu lange

[MrSpider]

Servus
Ich habe hier ien kleines Problem mit einer SQL-Abfrage. Die Abfrage verknüpft 4 Tabellen miteinander und braucht dafür, wenn man das limit auf 10 stellt, 25 Sekunden.

SELECT literature.*,
	GROUP_CONCAT(DISTINCT AUTOR ORDER BY POSITION SEPARATOR ' & ') AS AUTORS,
	GROUP_CONCAT(DISTINCT CONCAT_WS(' ', SPECIES_GENUS, SPECIES_SPECIES) SEPARATOR ', ') AS SPECIES 
FROM literature, autorLit, speciesLit, species 
WHERE literature.ID = autorLit.LITERATURE_ID 
	AND literature.ID = speciesLit.LITERATURE_ID 
	AND speciesLit.SPECIES_ID = species.ID 
GROUP BY literature.ID 
ORDER BY AUTORS, YEAR
LIMIT 10

Die Datenbank ist wie folgt aufgebaut:

 

[odigo]

Was ist das für eine DB? Hast du Indizes generieren lassen? Um welche Datenmenge geht es hier? Falls es eine Oracle-DB ist kann man sich z.B. mit Hilfe vom SQL-Developer den Explain-Plan anzeigen lassen, dann weiß man recht schnell wo z.B. die Full Table Scans stecken.

Gruß odigo

 

[MrSpider]

Das ist eine MySQL Datenbank für Literatur über fossile/rezente Haie (und ähnliches, weiß nicht genau ich bin nur der ders programmiert) Indizes wollte ich später für die Volltextsuche benutzen, machen die so normale Sachen auch schneller? Datenmenge so ca. 4000 eintraäge in der literatur tabelle, die anderen ähnlich viel.

explain gibt das aus:

mysql> EXPLAIN SELECT literature.*,
    -> GROUP_CONCAT(DISTINCT AUTOR ORDER BY POSITION SEPARATOR ' & ') AS AUTORS,
            -> GROUP_CONCAT(DISTINCT CONCAT_WS(' ', SPECIES_GENUS, SPECIES_SPECIES) SEPARATOR ', ') AS SPECIES
    -> FROM literature, autorLit, speciesLit, species
    -> WHERE autorLit.LITERATURE_ID  = literature.ID
    -> AND speciesLit.LITERATURE_ID = literature.ID
    -> AND speciesLit.SPECIES_ID = species.ID
    -> GROUP BY literature.ID
    -> ORDER BY AUTORS, YEAR;
+----+-------------+------------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref                           | rows | Extra                           |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | autorLit   | ALL    | NULL          | NULL    | NULL    | NULL                          | 6852 | Using temporary; Using filesort |
|  1 | SIMPLE      | speciesLit | index  | PRIMARY       | PRIMARY | 6       | NULL                          | 5472 | Using where; Using index        |
|  1 | SIMPLE      | literature | eq_ref | PRIMARY       | PRIMARY | 3       | fossil.autorLit.LITERATURE_ID |    1 |                                 |
|  1 | SIMPLE      | species    | eq_ref | PRIMARY       | PRIMARY | 3       | fossil.speciesLit.SPECIES_ID  |    1 |                                 |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)

EDIT:
Indem ich aus autorLit.Literature_Id und den beiden spalten in speciesLit einzelne Indexes gemacht habe, bekomme och folgendes:

+----+-------------+------------+--------+----------------------------------+---------------+---------+---------------------------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys                    | key           | key_len | ref                             | rows | Extra                                        |
+----+-------------+------------+--------+----------------------------------+---------------+---------+---------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | speciesLit | index  | PRIMARY,SPECIES_ID,LITERATURE_ID | PRIMARY       | 6       | NULL                            | 7296 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | autorLit   | ref    | LITERATURE_ID                    | LITERATURE_ID | 3       | fossil.speciesLit.LITERATURE_ID |    2 |                                              |
|  1 | SIMPLE      | literature | eq_ref | PRIMARY                          | PRIMARY       | 3       | fossil.autorLit.LITERATURE_ID   |    1 | Using where                                  |
|  1 | SIMPLE      | species    | eq_ref | PRIMARY                          | PRIMARY       | 3       | fossil.speciesLit.SPECIES_ID    |    1 |                                              |
+----+-------------+------------+--------+----------------------------------+---------------+---------+---------------------------------+------+----------------------------------------------

Demnach dauert die Abfrage nur noch 1.5 sekunden. Irgendwie immmer noch zu langsam -_-

 

[odigo]

Hm, ich bin leider kein MySQL-Spezialist. Hast du auf autorLit.LITERATURE_ID, literature.ID, speciesLit.LITERATURE_ID, species.ID einen Index gelegt? Hast du mal getestet was passiert wenn du z.B. deinen Select ohne die GROUP_CONCATs laufen lässt, also nur mit Select * oder so? Nur um mal rauszufinden wo denn der Hacken ist.

Und natürlich bringen hier die Indizes viel. Bei einer Volltextsuche bringen sie sogar eigentlich gar nichts, wenn du z.B. nach like('irgendwas%') suchst. Dann musst du immer mit einem Full Table Scan rechnen.

Schau dir vielleicht auch mal das hier an. Vielleicht findest du eine Lösung für deine Performance-Probleme.

Gruß
odigo

 

[MrSpider]

Ich werds jetzt so machen, dass ich die Abfrage audteilen werde:

SELECT literature.*,
	GROUP_CONCAT(DISTINCT AUTOR ORDER BY POSITION SEPARATOR ' & ') AS AUTORS
FROM literature LEFT JOIN autorLit
	ON autorLit.LITERATURE_ID  = literature.ID
GROUP BY literature.ID
ORDER BY AUTORS, YEAR;

SELECT speciesLit.LITERATURE_ID AS ID,
	GROUP_CONCAT(DISTINCT CONCAT_WS(' ', SPECIES_GENUS, SPECIES_SPECIES) SEPARATOR ', ') AS SPECIES 
FROM speciesLit, species 
WHERE speciesLit.SPECIES_ID = species.ID
GROUP BY speciesLit.LITERATURE_ID
ORDER BY speciesLit.LITERATURE_ID;

und dann in PHP das nötige noch zusammensetze