Propozycje zapytań + INDEXy? Opcje

[Toom]

Witam,
Propozycja katalogu instytucji.
Mój wstępny projekt bazy wygląda tak:

[SQL] pobierz, plaintext 
CREATE TABLE ins_dzial (
  dzi_id smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  dzi_nazwa varchar(150) NOT NULL DEFAULT '',
  dzi_pozycja smallint(6) NOT NULL DEFAULT '0',
  UNIQUE KEY dzi_id (dzi_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
 
CREATE TABLE ins_poddzial (
  pod_id smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  dzi_id smallint(6) NOT NULL DEFAULT '0',
  pod_nazwa varchar(100) NOT NULL DEFAULT '',
  pod_pozycja smallint(6) NOT NULL DEFAULT '0',
  UNIQUE KEY pod_id (pod_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
 
CREATE TABLE ins_przydzial (              
-- przypisuje instytucje do poddziałów (średnio 2-3)
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ins_id int(10) UNSIGNED NOT NULL DEFAULT '0',
  pod_id smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  UNIQUE KEY id (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
 
CREATE TABLE instytucja (
  ins_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  ins_nazwa varchar(255) NOT NULL DEFAULT '',
  ins_nazwa_pl varchar(100) NOT NULL DEFAULT '',      
-- sortująca (np w tym polu usuwane są rozpoczynające nazwę cudzysłowy, 
-- w starych wersjach baz mysql pomaga sortować pl znaki
  ins_opis text NOT NULL,
  ins_obraz varchar(30) NOT NULL DEFAULT '',
  ins_opis_obr varchar(60) NOT NULL DEFAULT '',
  UNIQUE KEY ins_id (ins_id),
  KEY ins_nazwa (ins_nazwa(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
[SQL] pobierz, plaintext 

Od razu mówię, że nie bardzo znam się na indeksach. Baza ma zawierać dużo wpisów... a zależy mi oczywiście na szybkich zapytaniach nie obciążających zbytnio serwera (dużo odwiedzających) wyciągających np:
1 ile jest instytucji w danym dziale;
wyszedł mi taki LEFT JOINowy kwiatek

[SQL] pobierz, plaintext 
SELECT COUNT(*) AS suma,dzi_nazwa 
FROM ins_dzial LEFT JOIN ins_poddzial ON ins_dzial.dzi_id=ins_poddzial.dzi_id 
LEFT JOIN ins_przydzial ON ins_przydzial.pod_id=ins_poddzial.pod_id 
LEFT JOIN instytucja ON instytucja.ins_id=ins_przydzial.ins_id 
GROUP BY  ins_dzial.dzi_id
[SQL] pobierz, plaintext 

- limit 0, 30,dla 26 000 instyt, 30 działów, 900 poddziałów, 80 000 przydziałów czas : 0.8
2 ile jest w poddziale:

[SQL] pobierz, plaintext 
SELECT COUNT( * ) AS suma, pod_nazwa, dzi_nazwa
FROM ins_dzial LEFT JOIN ins_poddzial ON ins_dzial.dzi_id = ins_poddzial.dzi_id
LEFT JOIN ins_przydzial ON ins_przydzial.pod_id = ins_poddzial.pod_id
LEFT JOIN instytucja ON instytucja.ins_id = ins_przydzial.ins_id
GROUP BY ins_poddzial.pod_id
[SQL] pobierz, plaintext 

limit o,30, czas: 0.8
3 wyświetlenie instytucji z danego poddziału (LIMIT 50) sort nazwa_pl:

[SQL] pobierz, plaintext 
SELECT ins_nazwa, instytucja.ins_id
FROM instytucja LEFT JOIN ins_przydzial ON ins_przydzial.ins_id = instytucja.ins_id
LEFT JOIN ins_poddzial ON ins_poddzial.pod_id = ins_przydzial.pod_id
WHERE ins_poddzial.pod_id =45
ORDER BY ins_nazwa_pl
LIMIT 0, 50
[SQL] pobierz, plaintext 

nie wiele daje dodawanie indexu do np ins_nazwa_pl, czas 0.46 (wolno)
4 wyświetlenie instytucji z danego działu (LIMIT 50) sort nazwa_pl;

[SQL] pobierz, plaintext 
SELECT ins_nazwa, instytucja.ins_id
FROM instytucja LEFT JOIN ins_przydzial ON ins_przydzial.ins_id = instytucja.ins_id
LEFT JOIN ins_poddzial ON ins_poddzial.pod_id = ins_przydzial.pod_id
LEFT JOIN ins_dzial ON ins_dzial.dzi_id = ins_poddzial.dzi_id
WHERE ins_dzial.dzi_id =20
ORDER BY ins_nazwa_pl
LIMIT 0, 50
[SQL] pobierz, plaintext 

czas o dziwo szybciej: 0.04??
5 wyciągnięcie instytucji na literkę A (LIMIT 50) sort nazwa_pl

[SQL] pobierz, plaintext 
SELECT ins_nazwa, instytucja.ins_id
FROM instytucja WHERE ins_nazwa LIKE 'a%'
ORDER BY ins_nazwa_pl
LIMIT 0, 50
[SQL] pobierz, plaintext 

czas 0.0013
6 wyciągnięcie instytucji które w /nazwie/ v /opisie/ v /nazwie pliku/ mają słowo "fundacja" lub jakąś frazę. sort nazwa_pl.

[SQL] pobierz, plaintext 
SELECT ins_nazwa, instytucja.ins_id
FROM instytucja WHERE ins_nazwa LIKE '%gm%' OR ins_opis LIKE '%gm%' OR ins_opis_obr LIKE '%gm%'
ORDER BY ins_nazwa_pl
LIMIT 0, 50
[SQL] pobierz, plaintext 

szybko o.003
Jak to się ma do wykorzystania wyszukiwania pełnotekstowego (szybkość)?

Nie bardzo rozumiem co dają mi indexy?? może ktoś mi doradzi.

Czekam na wasze komentarze co do struktury (oraz INDEXÓW) i szybsze zapytania

Toom

Ten post edytował Toom 11.07.2006, 22:47:36