Pomoc w optymalizacji Opcje

[Wasper]

Czesc,
mam zapytanie, ktore jeszcze nie zawiera 100% okreslonych pozycji, a juz generuje sie ponad 4s.
Baza jest w miare przyzwoicie zindeksowana. Chcialbym zasiegnac Waszej opinii, czy jest mozliwosc ulepszenia samego zapytania.

Zapytanie:

[SQL] pobierz, plaintext 
SELECT
tfirm.name fname, 
tfirm.city fcity, 
tfirm_navo.navo_code ncode, 
tperson.name pname, 
tperson.family pfamily, 
psr.name psrname, 
psr.family psrfamily, 
um_str.user_id um, 
tperson.detail_process,
tperson.mail_info, 
tperson.own_business, 
telefon.telefon ismobile, 
mail.mail ismail, 
logowania.suma log_sum,
logowania.last_log ostatni_log,
zamowienia.liczba zam_value
FROM
tperson
INNER JOIN tuser_firm ON tuser_firm.user_id = tperson.user_id
INNER JOIN tfirm ON tfirm.id = tuser_firm.firm_id
INNER JOIN tfirm_navo ON tfirm_navo.firm_id = tfirm.id
INNER JOIN (
SELECT count( tuser_log.id ) suma, IF(max(tuser_log.timestamp) IS NULL, '0000-00-00 00:00:00', max(tuser_log.timestamp))  last_log, tperson.user_id
FROM tperson
LEFT JOIN tuser_log ON tperson.user_id = tuser_log.user_id
AND log_type =1 WHERE tperson.role=7
GROUP BY tperson.user_id
)logowania ON logowania.user_id = tperson.user_id
INNER JOIN tfirm_psr ON tfirm_psr.firm_code=tfirm_navo.navo_code
INNER JOIN tteren_user ON tteren_user.teren_id=tfirm_psr.psr_field
INNER JOIN tuser_navo ON tuser_navo.navo_id=tteren_user.navo_user_id
INNER JOIN tperson psr ON psr.user_id=tuser_navo.user_id
INNER JOIN tstructure um_str ON um_str.child=tuser_navo.navo_id
INNER JOIN
(SELECT tperson.user_id uid, IF((tperson_mobile.mobile='' OR tperson_mobile.mobile IS NULL), 0, 1) telefon 
FROM tperson LEFT JOIN tperson_mobile ON tperson.user_id=tperson_mobile.user_id WHERE tperson.role=7) telefon ON telefon.uid=tperson.user_id
INNER JOIN
(SELECT tperson.user_id uid, IF((tperson_mail.mail='' OR tperson_mail.mail IS NULL), 0, 1) mail 
FROM tperson LEFT JOIN tperson_mail ON tperson.user_id=tperson_mail.user_id WHERE tperson.role=7) mail ON mail.uid=tperson.user_id
INNER JOIN 
(SELECT tperson.user_id uid, count(torder.id) liczba FROM tperson LEFT JOIN torder ON tperson.user_id=torder.user_id GROUP BY tperson.user_id) zamowienia ON zamowienia.uid=tperson.user_id
WHERE
tperson.role=7 AND tfirm.STATUS=1
[SQL] pobierz, plaintext 

probowalem pozbyc sie podzapytan i stworzylem cos takiego

[SQL] pobierz, plaintext 
SELECT
tfirm.name fname, 
tfirm.city fcity, 
tfirm_navo.navo_code ncode, 
tperson.name pname, 
tperson.family pfamily, 
psr.name psrname, 
psr.family psrfamily, 
um_str.user_id um, 
tperson.detail_process,
tperson.mail_info, 
tperson.own_business, 
 
count( tuser_log.id ),
IF(max(tuser_log.timestamp) IS NULL, '0000-00-00 00:00:00', max(tuser_log.timestamp)),
IF((tperson_mobile.mobile='' OR tperson_mobile.mobile IS NULL), 0, 1),
IF((tperson_mail.mail='' OR tperson_mail.mail IS NULL), 0, 1),
count(torder.id)
 
FROM
tperson
INNER JOIN tuser_firm ON tuser_firm.user_id = tperson.user_id
INNER JOIN tfirm ON tfirm.id = tuser_firm.firm_id
INNER JOIN tfirm_navo ON tfirm_navo.firm_id = tfirm.id
LEFT JOIN tuser_log ON tuser_log.user_id=tperson.user_id AND tuser_log.log_type=1
LEFT JOIN tperson_mobile ON tperson_mobile.user_id=tperson.user_id AND tperson_mobile.STATUS=1
LEFT JOIN tperson_mail ON tperson_mail.user_id=tperson.user_id AND tperson_mail.STATUS=1
LEFT JOIN torder ON torder.user_id=tperson.user_id
 
INNER JOIN tfirm_psr ON tfirm_psr.firm_code=tfirm_navo.navo_code
INNER JOIN tteren_user ON tteren_user.teren_id=tfirm_psr.psr_field
INNER JOIN tuser_navo ON tuser_navo.navo_id=tteren_user.navo_user_id
INNER JOIN tperson psr ON psr.user_id=tuser_navo.user_id
INNER JOIN tstructure um_str ON um_str.child=tuser_navo.navo_id
 
WHERE
tperson.role=7 AND tfirm.STATUS=1 AND 
HAVING count( tuser_log.id )=0
GROUP BY tperson.user_id, tuser_log.user_id, torder.user_id
[SQL] pobierz, plaintext 

dziala o wiele szybciej
ale pojawia sie kwestia filtrowania wg countow , gdyz wrzucajac do warunku where przykladowo count( tuser_log.id )=1 wyrzuca "invalid use of group function".
Moze to jakas moja niewiedza... dlatego chcialbym zasiegnac Waszej opinii.
A gory dzieki za pomoc.

edit.
zapomnialem dodac, ze ify sa wstawione po to, zeby uniknac wartosci NULL w zapytaniu (kwestia uzycia kwerendy w frameworku.. nie ma filtrow obslugujacych nulle)

Wasp

update. zapomnialem o having moze to z racji nieprzespanej nocy

Ten post edytował Wasper 22.05.2012, 09:19:57

[alegorn]

uff, ale to wielkie.
nie mam czasu analizowac calosci, ale odniose sie do tego kawalka:

[SQL] pobierz, plaintext 
IF(max(tuser_log.timestamp) IS NULL, '0000-00-00 00:00:00', max(tuser_log.timestamp)),
IF((tperson_mobile.mobile='' OR tperson_mobile.mobile IS NULL), 0, 1),
IF((tperson_mail.mail='' OR tperson_mail.mail IS NULL), 0, 1),
[SQL] pobierz, plaintext 

przedefiniuj te kolumny na NOT NULL, i ustaw wartosc domyslna.
ominiesz w ten sposob kilka operacji czyli czysty zysk. [zwlaszcza na OR]

ogolnie stosowanie wartosci NULL w bazie danych oznacza ze najprawdopodobniej projekt jest nieprzemyslany / baza nie jest znormalizowana

przemysl, czy optymalizacja tylko zapytania da ci efekt.
pokaz jakiegos explaina, to tez troche nam podpowie.

co do indeksow, radze uwazac.
ja np wczoraj wywalilem wczoraj primary, na zastepczym indeksie (czyli popularny autoinc).
po jego wywaleniu, i zalozeniu zwyklego indeksu na inna kolumne - zapytanie z czasu ~~4sec spadlo do 0,8
po kolejnej rozkminie i przebudowie tabeli - uzyskalem efekt na poziomie ~~0,02

z ~~4 sec na ~~0,02 wiec optymalizacja zapytania o conajmniej 200%

pisze to dlatego - ze chce zwrocic uwage, iz nie uzyskalbym az takiej redukcji czasu zmieniajac tylko kwerende.
(oczywiscie najwazniejszym kryterium tabeli byl odczyt.)

[Wasper]

Co do if`ow to null wynika z left joina.

ale hmmm... zastanowiles mnie ta wypowiedzia. Nie jest to baza skonstruowana przeze mnie i niestety mam ograniczona mozliwosc zmiany struktury. Ale z tego co widze, to indeksy primary sa wszedzie ustawione na kolumne ID, niezaleznie od relacji. W wielu tabelach sa one poprostu nieistotne.
Mowisz, ze mozna uzyskac az taka roznice?

[alegorn]

zalezy od tego czy te indeksy wykorzystujesz, oraz jakiego rodzaju jest to zapytanie.

pamietaj, ze baza danych, na innodb domyslnie sortuje wiersze wg glownego indeksu.

jesli wybierasz wg innego warunku (niz id autoinc.), a do tego musisz sortowac wynik, sprawa jest wtedy otwarta - ustaw glowny index na kolumne, po ktorej musisz sortowac/wybierac.

przyklad:
klasyczny przyklad tabelki z drzewkiem

[SQL] pobierz, plaintext 
CREATE TABLE `test` (
  `test_id` smallint(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` char(10) NOT NULL,
  `description` char(200) NOT NULL DEFAULT '',
  `lft` int(6) DEFAULT NULL,
  `rgt` int(6) DEFAULT NULL,
  PRIMARY KEY (`test_id`),
  KEY `code` (`code`)
)
[SQL] pobierz, plaintext 

zmiana kluczy

[SQL] pobierz, plaintext 
CREATE TABLE `test2` (
  `test_id` smallint(4) UNSIGNED NOT NULL,
  `code` char(10) NOT NULL,
  `description` char(200) NOT NULL DEFAULT '',
  `lft` int(6) DEFAULT NULL,
  `rgt` int(6) DEFAULT NULL,
  KEY `lft` (`lft`),
  KEY `rgt` (`rgt`),
  KEY `code` (`code`)
) 
[SQL] pobierz, plaintext 

i teraz samo zapytanie:

[SQL] pobierz, plaintext 
 
SELECT SQL_NO_CACHE
 node.*
FROM (`test` `node`
   JOIN `test` `parent`) 
WHERE (`node`.`lft` BETWEEN `parent`.`lft`
       AND `parent`.`rgt`)
GROUP BY `node`.`code`
ORDER BY `node`.`lft` 
[SQL] pobierz, plaintext 

tabela wypelniona okolo 10000 rekordow, jak widac - drzewko kodow.

roznica miedzy jedna tabela a druga to : ~~4 sec vs ~~0.8

po zastanowieniu, i tak przerobilem to jeszcze zupelnie inaczej - ale to juz inna bajka (zjazd do ~~0.02, ale to juz tabela RO)

chodzi mi o to ze w powyzszym przykladzie, (przerabialem juz istniejaca tabele) kolumna test_id jest nie tylko zbędna, ale i szkodliwa.
unikalnosc mam zapewniona na poziomie pola code, i to jest takze prawdziwy klucz glowny. (mozna dodac uniqa na tym polu). w tym konkretnym przypadku klucz primary na kolumnie test_id - szkodzi.

edit.:
w wersji bez indexu primary - zapytanie powinno wskazywac na polaczenie po kolumnie code czyli :

[SQL] pobierz, plaintext 
 
SELECT SQL_NO_CACHE
 node.*
FROM (`test` `node`
   JOIN `test` `parent` USING (code) 
WHERE (`node`.`lft` BETWEEN `parent`.`lft`
       AND `parent`.`rgt`)
GROUP BY `node`.`code`
ORDER BY `node`.`lft` 
[SQL] pobierz, plaintext 

zapytanie wtedy jeszcze znaczniej przyspieszy. [w testach, ale juz na mocniejszej maszynie - do ~~0.1 s]

j.

Ten post edytował alegorn 22.05.2012, 14:15:01