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.)