Optimizare query-uri MySQL

optimizare
mysql
query

(Igor Mardari) #1

Am acest query si ma intereseaza sa aflu cum l-as putea optimiza.

SELECT * AS dsp FROM data_storage_payroll
WHERE file_id = 1
AND (SELECT COUNT(*) FROM data_storage_payroll 
	  WHERE file_id = 1 
	  AND employee_first_name = dsp.employee_first_name AND employee_last_name = dsp.employee_last_name 
	  AND transaction_amount = dsp.transaction_amount
	  AND ABS(DATEDIFF(dsp.transaction_date, transaction_date)) <= 7
) >= 2

Ce face acest query: selecteaza toate row-urile care mai au cel putin un duplicate dupa coloanele employee_first_name, employee_last_name, transaction_amount,
indeplinind, in acelasi timp, si conditia asta: ABS(DATEDIFF(dsp.transaction_date, transaction_date)) <= 7

Evident, solutia cu GROUP BY, e prima care-ti vine in minte, dar problema e ca eu am nevoie de toate row-urile, nu sa fie grupate.

Am incercat si alte variante, cu JOIN, dar, ori nu-mi returneaza numarul de row-uri corect, ori dureaza tot cam atat.

Mai sunt si alte query-uri pe care vreau sa le optimizez, dar pentru inceput, l-am postat doar pe acesta.

Multumesc.


(Horia Coman) #2

Cred ca mai trebuie niste informatii. Poate sunt niste optimizari OK de facut, dar se poate sa fie si optim query-ul si nu mai ai ce sa-i faci.

Cat de incet merge acum si cat de repede vrei sa mearga? Ce fel de indexi ai pe tabela? Cat de mare e tabela? Care-i structura tabelei in rest (macar un hint de schema, daca nu toate coloanele). Vre-un output de la query planner ceva ca sa vezi unde isi bate capul MySQL cu query-ul asta?

Un query de genul asta se poate structura si ca un self join, desi as zice ca MySQL o sa aduca query-ul tau la o forma echivalenta.


(Georgiana Gligor) #3

primul care sare in ochi e count(*)
incearca sa pui count(id) sau ceva coloana ce o ai tu indexata, si vezi cum iti imbunatateste un pic

ca regula generala e bine sa ne ferim de * in orice imprejurare, si sa fim f specifici

apoi acel calcul de data… nu il poti optimiza cu alte functii mysql mai performante?

later edit: am uitat sa cer structura tabelelor si indecsii pe care ii ai deja pe acestea


(Marius Lucian NEAG) #4

E destul de greu să dăm sfaturi fără să știm structura datelor, ce reprezintă sau ce anume vrei să obții[1]. La fel cum a zis și @tekkie, structura și indecșii ar ajuta mult. Fără să avem informațiile astea putem doar să ne dăm cu presupusul.

Așa că eu presupun că tabela are un PRIMARY KEY de care te poți folosi și să încerci faci un LEFT JOIN cu care să verifici dacă mai există înregistrări care să respecte criteriile tale. LEFT JOIN poate fi mai rapid decât un subquery.

Te mai poți folosi și de faptul că tu verifici să existe minim 2 tranzacții și să eviți să folosești COUNT. În cazul tău e de ajuns să găsești încă o înregistrare diferită (PRIMARY KEY diferit) și să verifici cu IS NOT NULL.

Dacă 2 e un număr care e variabil poți încerca să folosești LEFT JOIN cu GROUP BY și HAVING.


[1] Tu practic ne întrebi cum poți optimiza doar o mică parte din soluția ta. De multe ori poți primi recomandări cu abordări diferite la problema mai mare pe care încerci să o rezolvi și din care să rezulte în soluții mai simple/mai eficiente.


(Igor Mardari) #5


(Opencart Romania) #6

Salutare!

Din experienta, Select in Select count(*) nu va fi niciodata rapid.

As merge pe join insa cu denumire exacta a coloanelor …

Spor


(Georgiana Gligor) #7

Eu as porni de la o varianta alterata putin,

SELECT dsp.id, dsp.employee_last_name, dsp.transaction_date FROM data_storage_payroll dsp
WHERE dsp.file_id = 1
AND (SELECT COUNT(dsp_inner.id) FROM data_storage_payroll dsp_inner
	  WHERE dsp_inner.file_id = 1 
	  AND dsp_inner.employee_first_name = dsp.employee_first_name AND dsp_inner.employee_last_name = dsp.employee_last_name 
	  AND dsp_inner.transaction_amount = dsp.transaction_amount
	  AND ABS(DATEDIFF(dsp.transaction_date, dsp_inner.transaction_date)) <= 7
) >= 2

Am rugamintea sa ne dai un analyze/explain pe subquery, deci pe

SELECT COUNT(dsp_inner.id) FROM data_storage_payroll dsp_inner
	  WHERE dsp_inner.file_id = 1 
	  AND dsp_inner.employee_first_name = dsp.employee_first_name AND dsp_inner.employee_last_name = dsp.employee_last_name 
	  AND dsp_inner.transaction_amount = dsp.transaction_amount
	  AND ABS(DATEDIFF(dsp.transaction_date, dsp_inner.transaction_date)) <= 7

evident punand o valoare in loc de dsp.transaction_date si respun cel e mai folosesti din outer


(Sergiu Șandor) #8

Contrar așteptărilor, * in contextul countului se comportă diferit față de cum se comportă în general, unde într-adevăr sunt de acord cu ce ai spus. Trebuie să ne ferim de * in orice împrejurare, mai puțin în contextul countului. Sunt mai multe discuții pe tema asta, dar ideea este că în unele cazuri (depinde de indexi, de tipul coloanei, de tipul bazei de date) count(*) este mai rapid decât count(coloană).

Așa că merită citit puțin despre asta:

Pe scurt, count(*) si count(coloana) nu este același lucru în unele cazuri, și în altele este fix același lucu.

  • COUNT(*) counts all rows
  • COUNT(column) counts non-NULLs only (asta poate să țină mai mult pentru că face și checkuri)

Edit: ăsta este un răspuns pentru răspunsul lui @tekkie legat de *.
Urmează și un răspuns scurt legat de problema în general.


(Georgiana Gligor) #9

Yay! Intram in adancime, e fain cand avem mai multi implicati la optimizari, welcome @micku7zu!

Manualul mysql e destul de limpede,

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count

This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly.

Acuma ca si trick e bine de folosit count(constanta), dar cum multi nu il inteleg am preferat sa mergem pe lucrurile clasice.


(Sergiu Șandor) #10

Adevărat, sunt multe cazuri (și aici vorbind și de alte DB-uri), de aia am vrut să scot în evidență că acel * de la count se comportă puțin diferit față de “SELECT *” și că trebuie avut grijă și studiat puțin (pentru că merită!).

Urmăresc forumul și m-am gândit că ție nu-ți scapă chestia asta, am răspuns pentru lumea care citește :smiley:


(Sergiu Șandor) #11

După cum ziceau și colegii de pe forum, suntem cam limitați având în vedere că nu știm toate datele problemei, așă că doar o să ghicim cât putem.

@Luxian a punctat foarte bine când a spus:

Și a avut dreptate legat de query-ul în sine.

Query-ul tău este aproape “optimizat la maxim”, lipsind doar ce a spus @Luxian. Tu ai nevoie de count(*) > 2, nu ai nevoie de rezultatul countului, ceea ce înseamnă că la primul row pe care-l găsește trebuie să se oprească, nu să caute în continuare.

Adică în loc de count(*) > 2, să folosești “exists”, ca și aici: https://stackoverflow.com/questions/5264658/is-exists-more-efficient-than-count0 sau soluția cu join a lui @Luxian (deși cred că dacă sunt scrise bine, ambele o să rezulte în același execution plan cu aceleași performanțe).

Dacă asta nu te ajută, înseamnă că trebuie să intervii la structura datelor. Aici mă refer la indexi sau la structura tabelelor.

De ce spun asta? pentru că oricum ai scrie queryul respectiv, DB-ul trebuie să facă asta:

Care se traduce neapărat într-un scan a 3 coloane (2 varchar și 1 dată). Din căte văd, data este indexată, ceea ce este bine, dar căutarea după 2 varchar neindexate este grea, oricum ai scrie query-u, baza de date trebuie să se ducă row cu row să caute dacă există first_name = first_name și last_name = last_name, care este slow. Nu poți să schimbi asta decât prin indexi sau prin altă structura (o coloană în plus? să scoți first_name si last_name într-o tabela separată? sau alte idei care ți se potrivesc).

În funcție de ce alte query-uri mai faci pe tabela respectivă, poate merită să pui un index pe (first_name, last_name), sau poate doi indexi, unul pe first_name, altul pe last_name. Sau, cum ziceam mai sus, poate merită să scoti first_name și last_name într-o tabelă separată și să fie legat de tabela ta prin foreign_key.

Să ai grijă și la indexi, pentru că au și bune, și rele. Citește putin despre ei, încearcă să pui unde crezi că se potrivesc mai bine și fă niște teste.

TL;DR
Ce spunea și lumea, e greu să ne dăm seama de o soluție mai bună pentru că ne lipsesc date, dar problema ta se rezuma la o căutare în baza de date după 2 coloane varchar neindexate care trebuie făcută oricum ai scrie queryul (imposibil să faci altfel dacă nu modifici structura / indexi), singura optimizare este că poți să faci “exists” și nu “count(*) > 2” pentru că nu te interesează numărul și atunci el poate să treacă mai departe când găseste primul row care îți îndeplinește condiția (optimizare destul de bună). Dacă vrei mai mult: indexi sau structura tabelelor diferite.


(Adrian Tufă ) #12

Totusi, cat dureaza acum rularea acestui query? Nu-mi dau seama daca s-a precizat deja acest lucru.


(adrian) #13

n-am vazut niciodata absolut nici o diferenta. stiu ca asa e teoria, dar in practica, pe MyISAM cel putin…


(adrian) #14

ontopic, cand ai subquery-uri complicate cea mai buna solutie e sa faci cache (ma refer la coloana separata cu rezultatul subquery-ului) la rezultate (presupun ca n-ai pretentie de realtime absolut). Altfel merg ordonarile si ce mai vrei tu dupa aia.