Hallo,
ich schreibe an einer Select Abfrage, in welcher ich etwa an die 15 Tabellen joine. Dies ist nötig, da ich einige 1:n bzw. m:n Beziehungen abbilde. Bisher hatte ich auch keine Geschwindigkeitsprobleme. Nun habe ich meine Abfrage aber erweitert und zu diesem Zweck drei view-Tabellen hinzugefügt. Meine Abfrage benötigt nun z.T. über 30 Sekunden unter WAMP (timeout) bzw. eine geschätzte viertel Sekunde auf dem Server meines Website Anbieters.
Meine Datenbank ist wie in folgendem Beispiel aufgebaut. In der Haupttabelle werden Personen und deren Eigenschaften abgebildet, daneben gibt es noch eine Tabelle mit Projekten sowie eine Zuordnungstabelle (Personen <-> Projekte).
create database projekte_db;
use projekte_db;
create table personen
(
personen_id int unsigned not null auto_increment primary key,
name varchar(25) not null,
standort_id smallint unsigned not null references standort(standort_id)
) engine = InnoDB;
create table standort
(
standort_id smallint unsigned not null primary key,
standort_name varchar(25) not null
) engine = InnoDB;
create table projekte
(
projekt_id smallint unsigned not null auto_increment primary key,
projekt_name varchar(25) not null
) engine = InnoDB;
create table personen_projekte
(
personen_id int unsigned not null references personen(personen_id),
projekt_id smallint unsigned not null references projekte(projekt_id),
primary key(personen_id, projekt_id)
) engine = InnoDB;
create view projekte_view
as
select personen.personen_id,
group_concat(distinct projekte.projekt_name order by projekte.projekt_id separator ', ') as projekt_name
from personen
join personen_projekte
on personen.personen_id = personen_projekte.personen_id
join projekte
on personen_projekte.projekt_id = projekte.projekt_id
group by personen.personen_id;
insert into personen values
(NULL, 'Otto', '3'),
(NULL, 'Hans', '1'),
(NULL, 'Monika', '2'),
(NULL, 'Anita', '2');
insert into standort values
('1', 'Berlin'),
('2', 'Bern'),
('3', 'London');
insert into projekte Values
(NULL, 'Projektname 1'),
(NULL, 'Projektname 2'),
(NULL, 'Projektname 3');
insert into personen_projekte values
('1', '2'),
('1', '3'),
('2', '3'),
('3', '1'),
('3', '2'),
('4', '1'),
('4', '3');
Mit der ursprünglichen Abfrage habe ich keine Probleme:
select personen.*, standort.standort_name,
group_concat(distinct personen_projekte.projekt_id separator ', ') as projekt_id,
group_concat(distinct projekte.projekt_name order by projekte.projekt_id separator '. ') as projekt_name
from
personen
join standort
on personen.standort_id = standort.standort_id
join personen_projekte
on personen.personen_id = personen_projekte.personen_id
join projekte
on personen_projekte.projekt_id = projekte.projekt_id
where personen.name like '%an%'
group by personen.personen_id;
+-------------+-------+-------------+---------------+------------+------------------------------+
| personen_id | name | standort_id | standort_name | projekt_id | projekt_name |
+-------------+-------+-------------+---------------+------------+------------------------------+
| 2 | Hans | 1 | Berlin | 3 | Projektname 3 |
| 4 | Anita | 2 | Bern | 3, 1 | Projektname 1. Projektname 3 |
+-------------+-------+-------------+---------------+------------+------------------------------+
In meiner erweiterten Abfrage durchsuche ich allerdings auch die Personen-Projekte Zuordnungstabelle und habe zu diesem Zweck meine view-Tabelle in die Abfrage einbezogen.
select personen.*, standort.standort_name,
group_concat(distinct personen_projekte.projekt_id separator ', ') as projekt_id_gesucht,
group_concat(distinct projekte.projekt_name order by projekte.projekt_id separator '. ') as projekt_name_gesucht,
projekte_view.projekt_name as alle_zugeordneten_projekte
from
personen
join standort
on personen.standort_id = standort.standort_id
join personen_projekte
on personen.personen_id = personen_projekte.personen_id
join projekte
on personen_projekte.projekt_id = projekte.projekt_id
join projekte_view
on personen.personen_id = projekte_view.personen_id
where personen.name like '%an%'
and personen_projekte.projekt_id in (3)
group by personen.personen_id;
+-------------+-------+-------------+---------------+--------------------+----------------------+------------------------------+
| personen_id | name | standort_id | standort_name | projekt_id_gesucht | projekt_name_gesucht | alle_zugeordneten_projekte |
+-------------+-------+-------------+---------------+--------------------+----------------------+------------------------------+
| 2 | Hans | 1 | Berlin | 3 | Projektname 3 | Projektname 3 |
| 4 | Anita | 2 | Bern | 3 | Projektname 3 | Projektname 1, Projektname 3 |
+-------------+-------+-------------+---------------+--------------------+----------------------+------------------------------+
Meine Abfrage ist nun aber - wie oben beschrieben - viel zu langsam. Mir ist aufgefallen, dass meine view-Tabelle keinen Primärschlüssel besitzt.
mysql> describe projekte_view;
+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| personen_id | int(10) unsigned | NO | | 0 | |
| projekt_name | text | YES | | NULL | |
+--------------+------------------+------+-----+---------+-------+
Kann dies die Abfrage bremsen? Kann für die view-Tabelle ein Primärschlüssel festgelegt werden? Was könnte noch meine Abfrage bremsen?
Edited 1 time(s). Last edit at 05/28/2011 08:46AM by Alwin Bekker.