MySQL: Először szűrni és az eredményt JOIN-olni, howto?

Van egy főtábla (`employees`):

emp_no	int(11)	
birth_date	date	
first_name	varchar(14)	
last_name	varchar(16)	
gender	enum('M','F')	
hire_date	date

Egy segédtábla (`titles`):

emp_no	int(11)	
title	varchar(50)	
from_date	date	
to_date	date NULL	

És egy másik segédtábla (`salaries`):

emp_no	int(11)	
salary	int(11)	
from_date	date	
to_date	date	

Mind a két segédtáblában foreign key az `emp_no`. A szűrés és lekérdezés megy így is:

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
LEFT JOIN `titles` ON
	(`titles`.`emp_no` = `employees`.`emp_no`) AND
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `salaries` ON
	(`salaries`.`emp_no` = `employees`.`emp_no`) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`))
WHERE (`titles`.`title`='Staff') AND (`salaries`.`salary`>='120000')
LIMIT 0, 20

De ez így nagyon lassú, 4-5 másodperceket kotorászik, pedig alig hárommillió rekord van a `salaries`-ben, négyszázezer a `titles`-ben és háromszázezer az `employees`-ben. (Ha pedig még sorrendezés is kerül bele, akkor aztán több, mint 10 másodperc...)

Próbáltam így is

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
LEFT JOIN `titles` ON
	(`titles`.`emp_no` = `employees`.`emp_no`) AND
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `salaries` ON
	(`salaries`.`emp_no` = `employees`.`emp_no`) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`))
WHERE `employees`.`emp_no` IN
(
	SELECT `ut`.`emp_no` FROM
	(
		(SELECT `titles`.`emp_no` FROM `titles` WHERE `titles`.`title`='Staff')
		UNION
		(SELECT `salaries`.`emp_no` FROM `salaries` WHERE `salaries`.`salary`>='100000')
	) `ut`
)
LIMIT 0, 20

viszont így nem jó a végeredmény, rossz értékek jelennek meg a szűrt oszlopokban. Próbáltam úgy is, hogy először megszűröm az eredményt és aztán JOIN-olok

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary` FROM
(
	SELECT * FROM `employees`
	WHERE `employees`.`emp_no` IN
	(
		SELECT `ut`.`emp_no` FROM
		(
			(SELECT `titles`.`emp_no` FROM `titles` WHERE `titles`.`title`='Staff')
			UNION
			(SELECT `salaries`.`emp_no` FROM `salaries` WHERE `salaries`.`salary`>='100000')
		) `ut`
	)
) AS `employees`
LEFT JOIN `titles` ON
	(`titles`.`emp_no` = `employees`.`emp_no`) AND
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `salaries` ON
	(`salaries`.`emp_no` = `employees`.`emp_no`) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`))
LIMIT 0, 20

de dettó rossz adatok jöttek ki.

Sajnos már vagy 8-10 év óta nem foglalkoztam SQL-lel a sima SELECT/INSERT/UPDATE/DELETE szinten túl, így keresni is hiába kerestem, mert igazából nem tudom, hogy mit keressek.
Hogyan lehet ezt tisztességesen megcsinálni, hogy előbb leválogatja a két segédtáblából azokat a sorokat, amik érintettek és csak azokat kérje le a főtáblából, amik csatolhatóak?

Hozzászólások

Szerkesztve: 2020. 10. 11., v – 18:26

MySQL-t sajnos hál' Istennek sosem kellett élesben használnom, de amik dialektustól függetlenül eszembe jutnak (csak a helyes eredményt mutató query-t megnézve):

1) Én megpróbálnám, hogy nem az egész táblával JOIN-olok, hanem egy subquery-vel, ami eleve csak azokat tölti be a két segédtáblából, amire szükség van

2) Ha van ráhatásod a DB struktúrára, akkor megfontolhatod a DB-t denormalizálva a current_title és current_salary mezőket letárolni az employee táblában. (Ofc erről az adatbázis kliensnek is tudnia kell, vagy DB oldalon csinálod meg, hogy ott legyen a cache)

3) from_date ha nincs indexelve, az is megér egy próbát

Imho itt a bottleneck az egyes pont: a hárommillió soros tábla minden soránál kétszer nyálazod végig mindkét segédtáblát ahelyett, hogy egyszer csinálnád meg a segédtáblás lekérdezést (emp_no, title, MAX(from_date)), és onnan szednéd ki, ami kell.

szerk: aztán persze ki tudja, nálam eddig kb. 70% T-SQL, 29% PL/SQL, 1% egyéb :)

1) Így gondoltad?

SELECT `employees`.*, `titles_t`.`title`, `salaries_t`.`salary`, `departments`.`dept_name`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN
(
	SELECT `titles`.`emp_no`, `titles`.`title`, MAX(`titles`.`from_date`) FROM `titles` GROUP BY `titles`.`emp_no`
) AS `titles_t` ON (`titles_t`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN
(
	SELECT `salaries`.`emp_no`, `salaries`.`salary`, MAX(`salaries`.`from_date`) FROM `salaries` GROUP BY `salaries`.`emp_no`
) AS `salaries_t` ON (`salaries_t`.`emp_no` = `employees`.`emp_no`)
WHERE (`titles_t`.`title`='Senior Engineer') AND (`salaries_t`.`salary`<'100000')
ORDER BY `salaries_t`.`salary` DESC
LIMIT 0, 20

Mert így kb. a 2x-esére gyorsult és a sorrendezéssel sem sokkal lassabb, így kb. 1-2-3 sec, amíg bejön. Köszi a tippet.

2) Nincsen. Ez most localban van, de ahol majd tesztelni fogják, ott az a DB felállás lesz, ahogy most van. Amúgy nekem is az első gondolatom az lett volna, hogy csinálok egy redundáns tárolótáblát, a pillanatnyi értékekkel, dehát ez itt nem játszik.

3) De az indexelve van. A `salary` nincs és a `title` sem, de utóbbira már nem is tudnék FULLTEXT-et nyomni, mert az InnoDB csak kreáláskor engedi, a kreálást pedig nem én végzem: importálni kell. :( A `salary`-ra rakhatnék, de ld. 2)-es pont.

Valószínűleg igazad van, mert így jelentősen gyorsabb lett. Még majd bütykölöm, hátha bírok rajta valamit csinálni, de a lényeg, amit a topiccímben is feltettem, az előszűrés már megvan, úgyhogy, thx again.

join-hoz jó, hogy van a dátumon index, de a fizetés és title szerinti szűrésen úgy érzem, hogy segítene egy salary index és egy title index. Bár nem tudom, a mysql hogy csinálja.

disclaimer: ha valamit beidéztem és alá írtam valamit, akkor a válaszom a beidézett szövegre vonatkozik és nem mindenféle más, random dolgokra.

Szerkesztve: 2020. 10. 11., v – 23:27

Gondolom egy program fogja lekérni ezt rendszeresen, másképpen nem lenne érdekes annyira a válaszidő. Lehet valahogy végrehajtási tervet kérni az adatbázistól? Azt sem tartom elképzelhetetlennek, hogy ez ennyi ideig tart normálisan is. Nagyjából 150MB-t kell végignyálazni, ha nem tud értelmesen indexet használni. Nyilván a végrehajtási terven múlik, de a DB nem tudhatja megsaccolni, hogy éppenséggel melyik terv lesz a jó? Sőt, te sem feltétlenül tudod, mert nem mindegy, hogy a kisfizetésű vezérigazgatókat keressük (ekkor a vezérigazgatókra érdemes először szűrni) , vagy a nagyfizetésű átlagdlgozóra (ilyenkor a nagy fizetésre érdemes először szűrni). Tehát adatfüggő lesz, hogy melyik stratégia a jobb.

Egy megoldás lehet az, hogy megsejted, hogy milyen sorrendbéli végrehajtás a legjobb, és azt "kézzel leprogramozod". Tehát például először lekérdezed a 120 ezer feletti fizetésűeket. Aztán ezekkel az azonosítókkal az ő title-jüket. Végül hozzácsattintod a nevüket. Nem a legszebb megoldás, de működhet.

Ha lekérdezésre kell optimalizálni, akkor én is denormalizálnám azt a részt, ami alapján a lekérdezés megy. A salary és a title változásokat például egyetlen táblában lehetne követni. Akkor nem kellene join ahhoz, hogy egyfajta title- fizetés kombinációt megtaláljunk. Persze így hirtelen nagyra nőne a tábla a varchar(50) miatt, de a titlék száma jó esetben igencsak korlátos lesz, ezeknek lehet adni egy számot. Persze az is kapcsolótábla, de pici, és a lekérdezés lényege már int azonosítókon futna le. Tehát így:

`titlenames`:
titlename	varchar(50)	
titleid int16

`salaryandtitle`:

emp_no	int(11)	
salary	int(11)
titleid int16
from_date	date	
to_date	date	

Erre ha rádobsz egy összetett tree alapú indexet titeid+salary sorrendben, akkor a lekérdezésed eredményei kb azonnal ki fognak folyni a rendszerből.

Egy másik megoldás lehet, hogy úgynevezett materializált nézetet hozol létre, amennyiben a lekérdezésnek van olyan része, ami ésszerűen cache-elhető. (https://en.wikipedia.org/wiki/Materialized_view) De ezt nem tudom van-e MySql-ben, meg hogy alkalmazható-e az esetedben. Például a salary-title joint lehetne materializált nézetben tartani. Vagy akár mindhárom tábla joinját current date-re vonatkozóan.

 

Ja, és a lényeg:

gender	enum('M','F')	

Már itt rossz a sémád! Mi ez a kirekesztés????4!!?!? :-)

Egy website kéri le. Végrehajtási terv alatt mit értesz, az explaint?

Mir értesz "kézzel leprogramozás" alatt? Olyannal próbálkoztam, hogy leszűröm a `salaries` és `titles` táblát és csak azokat az ID-kat kérem le, amik kellenek, de rossz eredményeket adott, az OP-ban ott a query.

Arra én is gondoltam, hogy csinálok egy közös táblát, amiből kérdezget, aztán ott is update-elgeti, amit kell, csak nem tudom, hogy azt el fogják-e fogadni, bár ez csak az egyik gond. A nagyobbik, hogy próbálkoztam olyannal, hogy

DROP TABLE IF EXISTS `e`;
CREATE TABLE `e` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  `title` varchar(50) NOT NULL,
  `salary` int(11) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  `dept_no` char(4) NOT NULL,
  PRIMARY KEY (`emp_no`),
  INDEX `birth_date` (`birth_date`),
  FULLTEXT `first_name` (`first_name`),
  FULLTEXT `last_name` (`last_name`),
  INDEX `gender` (`gender`),
  INDEX `hire_date` (`hire_date`),
  FULLTEXT `title` (`title`),
  INDEX `salary` (`salary`),
  FULLTEXT `dept_name` (`dept_name`),
  FULLTEXT `dept_no` (`dept_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `e`
SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, `departments`.`dept_name`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN `titles` ON
	`titles`.`emp_no` = `employees`.`emp_no` AND
	`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON
	`salaries`.`emp_no` = `employees`.`emp_no` AND
	`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `dept_emp` ON
	`dept_emp`.`emp_no` = `employees`.`emp_no` AND
	`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`;

Csak ez 11076 sor felett "Error in query (1062): Duplicate entry '21076' for key 'PRIMARY'" hibaüzenetet dobott. Fel nem fogom miért, hiszen a beszúrandó `emp_no` nem ismétlődik sehol, más PRIMARY KEY meg nincs ebben a táblában...

Egyébként a teszt DB itt elérhető: https://github.com/datacharmer/test_db

Materializált nézet MySQL-ben nincs.

Ja, és a lényeg:
gender enum('M','F')

Már itt rossz a sémád! Mi ez a kirekesztés????4!!?!? :-)

Majd írok a MySQL-eseknek, hogy tegyenek be kettő db. 256-bites floatot helyette és akkor két csúszkán lehet beállítani, hogy ki mennyire M, meg F. :P Bár inkább nem adnék nekik ötleteket...

A kézzel leprogramozást úgy értem, hogy lefuttatod az alqueryt és annak az eredményét kiveszed resultsetként, majd visszafeedeled a következő query-be úgy, hogy az értékeket sorba beleírod a lekérő SQL-be. Alapvetően hülyeség ilyet csinálni, de mégis néha érdemes, például mert így látjuk mi mennyi ideig tart önmagában. Bár aki ismeri az adatbázisát, az valószínűleg abból is ki tudja nyerni a részeredményeket.

További ötlet lehet, hogy a max date bejegyzést a salary és a title táblában is egy boolean-nal megjelölöd. Így nem kell ezeket mindig kinyálazni, egyből rendelkezésre áll - pláne, ha be van indexelve ez a flag. Nyilván a frissítésekkor ezt a flaget kezelni kell. Az indexelésnél lényeges, hogy ne egy adat alapján legyen, hanem a teljes halmazon, amire a lekérdezés vonatkozik. Ugyanis ha egy-egy oszlopon van index, és kettőre vonatkozik a kérdés, akkor még mindig két eredményt őssze kell fésülni. Azonban ha például van egy (latest_entry, salary) összetett indexed, akkor egy where latest_entry and salary>10000 kérdés teljesen indexből kiszolgálható.

A Duplicate entry fogas kérdés. Akármelyik al-lekérdezésnek ha több értéke van véletlenül, az okozhatja. Például a department, ha a dept_no véletlenül nem zárja ki az ismétlést. Vagy ha a date alapú lekérdezésben van két egyforma dátum (ez valószínűbb) és az duplikált sort okoz. Nem logikus, hogy valakinek egyazon dátumon két különböző címe van, de ez a tipikus, hogy a való életbeli adatbázisok tele vannak ilyenekkel. Pláne amiket emberek töltenek fel! Úgy kell megfogalmazni a query-ket, hogy ezek ne okozhassanak bajt! Tesztelni úgy lehet, hogy a lekérdezést lefuttatod akár kulcs nélküli táblába, majd abban keresel duplikátumot valahogy így: https://chartio.com/learn/databases/how-to-find-duplicate-values-in-a-s…

Amúgy én sem vagyok egy SQL mágus, a tananyagot elég jól megtanultam, az adatszerkezetek a véremben vannak, de ritkán használom, és amikor kell, akkor folyton keresnem kell, hogy mit hogy is kell leírni SQL-ben.

> A kézzel leprogramozást úgy értem, hogy lefuttatod az alqueryt és annak az eredményét kiveszed resultsetként, majd visszafeedeled a következő query-be úgy, hogy az értékeket sorba beleírod a lekérő SQL-be.

És ezt hogyan csináljam? Temporary tables-t már próbáltam, de csak még jobban belassított mindent. Olyat is próbáltam, hogy leszűröm a két segédtáblát és az ID-ket használom a fő query szűrésére (ez az OP-ban is benne volt, ez a két query), de rossz végeredményt kaptam.

> További ötlet lehet, hogy a max date bejegyzést a salary és a title táblában is egy boolean-nal megjelölöd. Így nem kell ezeket mindig kinyálazni, egyből rendelkezésre áll - pláne, ha be van indexelve ez a flag.

De ezzel ugyanott vagyok, ahol most, mert a `from_date` is indexelve van.

> Az indexelésnél lényeges, hogy ne egy adat alapján legyen, hanem a teljes halmazon, amire a lekérdezés vonatkozik. Ugyanis ha egy-egy oszlopon van index, és kettőre vonatkozik a kérdés, akkor még mindig két eredményt őssze kell fésülni. Azonban ha például van egy (latest_entry, salary) összetett indexed, akkor egy where latest_entry and salary>10000 kérdés teljesen indexből kiszolgálható.

Ebben lehet, hogy igazad van, csak épp nem piszkálhatok hozzá a szerkezethez.

> A Duplicate entry fogas kérdés. Akármelyik al-lekérdezésnek ha több értéke van véletlenül, az okozhatja. Például a department, ha a dept_no véletlenül nem zárja ki az ismétlést. Vagy ha a date alapú lekérdezésben van két egyforma dátum (ez valószínűbb) és az duplikált sort okoz. Nem logikus, hogy valakinek egyazon dátumon két különböző címe van, de ez a tipikus, hogy a való életbeli adatbázisok tele vannak ilyenekkel. Pláne amiket emberek töltenek fel! Úgy kell megfogalmazni a query-ket, hogy ezek ne okozhassanak bajt! Tesztelni úgy lehet, hogy a lekérdezést lefuttatod akár kulcs nélküli táblába, majd abban keresel duplikátumot valahogy így: https://chartio.com/learn/databases/how-to-find-duplicate-values-in-a-s…

Köszi az ötletet, ezt kipróbálom. (Mondjuk itt ezen a "cache" táblán, amit csináltam, egyedül a PRIMARY KEY-en nem lehet ismétlődés és azon nincs is.)

> És ezt hogyan csináljam?

Ilyesmi:

SQL:

Select * from salary where salary>120000

Programban a resultsettel ezt csinálod:


select * from titles where employee_id in (${resultset->id vesszővel elválasztott lista})

És így tovább. Tehát az al-lekérés eredményét áthajtod a programon. Ez szinte mindig antipattern, nem is végleges megoldásnak javaslom én sem, hanem azért hogy meg tudd mérni, hogy melyik része meddig tart, és akkor azon tudsz gondolkodni, hogy mit lehetne optimalizálni.

Abban a szélsőséges esetben viszont, ha az adatbázis által megálmodott lekérdezés tervnél egyértelműen okosabbat is ki tudsz találni, akkor akár még jobb is lehet egy ilyen megoldás. Hasonlóképpen, ha az al-lekérések eredményeit cache-eled egy korábbi lekérdezésből, vagy valami ilyesmi, akkor is ésszerű lehet ilyet csinálni.

> Ebben lehet, hogy igazad van, csak épp nem piszkálhatok hozzá a szerkezethez.

Ha nem lehet hozzányúlni a szerkezethez, akkor simán lehet, hogy ezek a lekérdezési idők nagyjából kiadják a rendszer lehetőségeit. Gondold meg, hogy ha nincsen célirányos index, akkor a teljes adatmennyiséget végig kell nyálazni. 150 mega (a legnagyobb tábla becsült mérete) nem annyira sok, de ha arra a szűrésre sok pozitív találat van, akkor nagyon sok kulcsot kell lekérni a másik táblából, ami minden esetben egy fában keresés lesz. Hozzávéve, hogy esetleg a cache is rosszul használódik akár még ki is jöhet, hogy ezen az adatszerkezeten ez ennyi és kész. Én leprogramoznám ugyanezt a lekérést Java-ban a megfelelő indexeket TreeMap-ekbe téve, és ha hasonló idők jönnek ki, akkor ez ennyi és kész. (Mármint csak akkor csinálnék ilyet, ha nagyon fontos lenne az egész, és ráadásul jobb ötletem sem lenne. Benchmarknak, hogy mit lehet kihozni a vasból. Hasonlóan a megfelelő indexeket is felvenném még akkor is, ha a végső produktba valamiért nem lehet beletenni ezeket: szintén benchmarknak.)

> Végrehajtási terv alatt mit értesz, az explaint?

Azt hiszem úgy hívják. Olyanok vannak benne, hogy először ehhez az indexhez fogok nyúlni, és ezeket a feltételeket tudom szűrni vele. Utána az eredményt ezzel a másik táblával összejoinolom. És így tovább.

Adatbázisok tárgyból mi annó úgy tanultuk, hogy az SQL egy leíró nyelv, amivel ilyen elméleti konstrukciókat lehet leírni, amikkel deklaráljuk, hogy mit akarunk látni, de egyáltalán nem tudunk általa a lekérdezés végrehajtásának mikéntjére hatni. Legalábbis ez az elv. Az adatbázis a lekérdezésből egy absztrakt adatszerkezetet csinál, amit aztán transzformálgat, ahogy neki tetszik. Tehát két ekvivalens lekérdezésnek ha teljesen más is a formája, akkor is ugyanarra a lekérdezési tervre fordul le többnyire. Hasonlóan az optimalizáló fordítókhoz, illetve tulajdonképpen ez is az. Az olyanok viszont már számítanak, hogy a feltételeket hogyan fogalmazzuk meg, például egy indokolatlan egyenlőség helyett "like" használat le tudja rontani a teljesítményt és hasonlók.

Na most a végrehajtási terv az már egy imperatív dolog, és ha tudod, hogy mekkorák a részeredmények, akkor az alapján már lehet futásidőt is becsülni. Illetve a részlet lekéréseket külön-külön is tudod futtatni, tudod mérni (sőt, szerintem van ilyen feature-e az explain-nek, hogy a mérési eredményeket is beleírja). És akkor látszani fog, hogy mi lassú, hol megy el az idő. Ha van benne négyzetes skálázódás, illetve olyan, hogy a szűrést a rossz oldalon kezdi, akkor azokon lehet gondolkodni, hogy hogy lehet kikerülni. Ha meg nincs, akkor ennyit tud a vas, és mégiscsak a tárolási szerkezetet meg kell változtatni.

> Ilyesmi:

Hát ez ugyanaz pepitában, amit én csináltam az OP-ban... Rossz eredményeket adott. Persze lehet, hogy én írtam meg a lekérést rosszul.

> Ha nem lehet hozzányúlni a szerkezethez, akkor simán lehet, hogy ezek a lekérdezési idők nagyjából kiadják a rendszer lehetőségeit.

Közben hunludvig kolléga javaslata nyomán kiderült, hogy egyáltalán nem. Jól sejtettem én, hogy ennek sokkal gyorsabban kellene mennie, csak nem tudtam, hol fogjam meg.

> Azt hiszem úgy hívják. Olyanok vannak benne, hogy először ehhez az indexhez fogok nyúlni, és ezeket a feltételeket tudom szűrni vele. Utána az eredményt ezzel a másik táblával összejoinolom. És így tovább.

Oké, lecsekkoltam, az átírt query a `salary`-ra sorrendezve így néz ki:

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, `departments`.`dept_name`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN `titles` ON (`titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON (`salaries`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)) AND
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = `employees`.`emp_no`))
ORDER BY `salaries`.`salary` DESC
LIMIT 0, 20

És itt az explain:

id?	select_type?	table?	partitions?	type?	possible_keys?	key?	key_len?	ref?	rows?	Extra?
1	PRIMARY	employees	NULL	ALL	PRIMARY	NULL	NULL	NULL	299025	Using temporary; Using filesort
1	PRIMARY	dept_emp	NULL	ref	PRIMARY	PRIMARY	4	employees.employees.emp_no	1	Using where
1	PRIMARY	departments	NULL	eq_ref	PRIMARY	PRIMARY	16	employees.dept_emp.dept_no	1	
1	PRIMARY	titles	NULL	ref	PRIMARY	PRIMARY	4	employees.employees.emp_no	1	Using where; Using index
1	PRIMARY	salaries	NULL	eq_ref	PRIMARY	PRIMARY	7	employees.employees.emp_no,func	1	Using where
4	DEPENDENT SUBQUERY	dept_emp	NULL	ref	PRIMARY	PRIMARY	4	employees.employees.emp_no	1	
3	DEPENDENT SUBQUERY	salaries	NULL	ref	PRIMARY	PRIMARY	4	employees.employees.emp_no	4	Using index
2	DEPENDENT SUBQUERY	titles	NULL	ref	PRIMARY	PRIMARY	4	employees.employees.emp_no	1	Using index

Asszem itt inkább azzal lesz a baj, hogy a sorrendezésbe belezavar az a csomó olyan entry, ami a dátumok alapján már érvénytelen. Vagy nem, mert a sorrendezésnél már minden le van szűrve és oda van csatolva?

Ránéztél az adatra is?

SELECT * FROM dept_emp WHERE emp_no = 21076;
21076 d004 1993-07-25 1993-08-04
21076 d005 1993-07-25 1993-07-25

Itt az összes "duplikáció":
SELECT * FROM dept_emp WHERE emp_no IN (21076,37429,44683,49509,64098,69836,82648,91899,109363,206466,219624,228322,246188,282558,285052,285338,290639,433010,433358,435075,435183,440659,452212,468620,469544,475919,491049,496147,499964);

Tehát azért van duplikáció, mert adott kezdődátummal több department is fel van sorolva (gyk, egy napon belül lett új department-je).

Szerkesztve: 2020. 10. 12., h – 04:43

Azt jól látom, hogy a to_date alapból NULL és ha van változás (új bejegyzés), akkor kap értéket?

Ez esetben én a to_date = NULL-ra keresnék nem a max(from_date)-re.

Csak rosszabb lenne, ugyanis a `to_date`-en nincs index, azonfelül a tartalma irreleváns, mert a legutolsó title, salary és department a legutolsó `from_date` alapján választódik ki, az érdektelen, hogy még él-e (9999-01-01), vagy befejeződött (egyéb dátum).

Az, hogy mutatja, hogy él-e még az aktuális cím vagy fizetés...de amúgy ez a MySQL egyik tesztadatbázisa, nem én terveztem.

Hogy érnék el vele 1000x-es gyorsulást? MAX(`from_date`) per kopf (azaz per `emp_no`) egy darab van, `to_date`='9999-01-01' meg vagy egy, vagy egy sem, ha az utolsó aktuális érték befejezett, ennek megfelelően, ha leszűröm, hogy ahol `to_date`='9999-01-01', azt adja ki, akkor egy csomó embernél NULL-t fog visszaadni, mert az utolsó megkezdett érték időszaka már be van fejezve nekik. Azonfelül ellentétben a `from_date`-tel, ez nincs indexelve.

De nincs NULL benne. Csak lehet NULL, de egy darab sincs benne; ami nincs belőve az mind '9999-01-01'. Azonfelül a `from_date` indexelve van, nem kell az egész táblát végigscannelnie, csak az indexeket, a `to_date` pedig nincs, ahhoz viszont pesszimális esetben végig kell, ha a legvégén van, vagy egyáltalán nincs. A listában feltüntetni pedig az utolsó érvényes címet, vagy fizetést kell. Ha az utolsó indulódátum helyett leszűröm, hogy `to_date` != '9999-01-01', akkor tele lesz NULL értékekkel, ahol már le van zárva.

Már átírtam. Viszont így egyáltalán nem kapom meg azt a sort, ha be van fejezve.

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN `titles` ON (`titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON (`salaries`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)) AND
	(`titles`.`to_date` = '9999-01-01') AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)) AND
	(`salaries`.`to_date` = '9999-01-01') AND
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = `employees`.`emp_no`)) AND
	(`dept_emp`.`to_date` = '9999-01-01')
LIMIT 0, 20
SELECT e.*, 
(SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no) AS beosztas,
(SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no) AS fizetes
FROM `employees` AS e
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = e.emp_no)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
    (`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = e.emp_no)) AND
    (`dept_emp`.`to_date` = '9999-01-01') 
LIMIT 0, 20

/* Sorok megjelenítése 0-19 (összesen 20, A lekérdezés 0.0032 másodpercig tartott.) */

Mi a feladat? Mire kell leszűrni, rendezni?  

 

 

Rendezve:

/* Sorok megjelenítése 0-19 (összesen 20, A lekérdezés 0.0001 másodpercig tartott.) */

SELECT e.*, 
(SELECT  title FROM titles WHERE  titles.emp_no = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no)) AS beosztas,
(SELECT salary FROM salaries WHERE `salaries`.`emp_no` = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no)) AS fizetes
FROM `employees` AS e
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = e.emp_no)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = e.emp_no)) AND
	(`dept_emp`.`to_date` = '9999-01-01') AND
    (SELECT salary FROM salaries WHERE `salaries`.`emp_no` = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no)) > 10000 AND
    (SELECT  title FROM titles WHERE  titles.emp_no = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no)) = "Staff"
    
    ORDER BY fizetes DESC
    
LIMIT 0, 20

Általánosan, egy táblában kell megjeleníteni az adatokat, ami szűrhető és rendezhető. Már minden gyorsan megy, csak a fizetésre sorrendezve, ha sok sort talál, lassú.

SELECT e.*, 
(SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no) AS beosztas,
(SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no) AS fizetes
FROM `employees` AS e
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = e.emp_no)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
    (`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = e.emp_no)) AND
    (`dept_emp`.`to_date` = '9999-01-01')
ORDER BY `fizetes`
LIMIT 0, 20

Ez is az.

Update: "/* Sorok megjelenítése 0-19 (összesen 20, A lekérdezés 0.0001 másodpercig tartott.) */"
Persze, mert becache-eli, ha ugyanazokat az adatokat kéred le. Ha megváltoztatod, hogy mire szűrsz, vagy a LIMIT paramétereit, akkor 3-4 sec-ig tart.

Viszont, lehet nektek lesz igazatok; nem a sebesség miatt, (az eddig kb. ugyanaz), hanem azért, mert ha nem szűröm le, akkor, ha aznap több titulusváltás történt, akkor a `from_date`-ból több egyforma MAX van, ami a listában duplikált sorokat jelent: az összes utolsó napi meg fog jelenni, a sor többi részét tekintve identikus adatokkal. Ez rosszabb, mint ha üres mezők lennének a lezárt entry-knél; azt lehet magyarázni épeszű indokkal, hogy nincs aktuális címe, fizetése, whatever, ezt meg nem.

ki tudsz tenni valahova egy dumpot dummy adatokkal, hogy kicsit lehessen jatszani vele? en kivancsi vagyok!

Szerkesztve: 2020. 10. 12., h – 10:53

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
LEFT JOIN `titles` ON
(`titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON
(`salaries`.`emp_no` = `employees`.`emp_no`)
WHERE (`titles`.`title`='Staff') AND (`salaries`.`salary`>='120000')
AND `salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)
AND `titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)
LIMIT 0, 20;

Ez nálam valamivel gyorsabban fut le.

Igen, így a szűrés villámgyorssá (<1s) vált, ezt nagyon köszi. Csak sajnos a sorrendezés így 5-6 másodperc... Visszavonom: kizárólag a `salary`-ra sorrendezve lassú. Asszem mindjárt egy index-szel fogom honorálni a dolgot. Update: Sajnos nem segített rajta sem az "ALTER TABLE `salaries` ADD INDEX `salary` (`salary`);", sem az "ALTER TABLE `salaries` ADD INDEX `emp_no_salary` (`emp_no`, `salary`);"...

nekem segitett:

mysql> SELECT `employees`.*, `titles`.`title`, `salaries`.`salary` FROM `employees` LEFT JOIN `titles` ON (`titles`.`emp_no` = `employees`.`emp_no`) LEFT JOIN `salaries` ON (`salaries`.`emp_no` = `employees`.`emp_no`) WHERE (`titles`.`title`='Staff') AND (`salaries`.`salary`>='120000') AND `salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`) AND `titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`) order by salary LIMIT 9,2;
+--------+------------+------------+-----------+--------+------------+-------+--------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | title | salary |
+--------+------------+------------+-----------+--------+------------+-------+--------+
|  45280 | 1956-11-19 | Nalini     | Bardell   | F      | 1989-09-14 | Staff | 120291 |
| 495247 | 1955-03-03 | Kamakshi   | Verhoeff  | M      | 1991-07-31 | Staff | 120305 |
+--------+------------+------------+-----------+--------+------------+-------+--------+
2 rows in set (0.01 sec)

ez a salaries tabla:

mysql> show create table salaries;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                              |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| salaries | CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `salary` (`salary`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

De próbáld meg egy olyan halmazzal, aminek a szűrőfeltételei több eredményt hagynak, vagy próbáld meg szűrés nélkül:

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
LEFT JOIN `titles` ON (`titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON (`salaries`.`emp_no` = `employees`.`emp_no`)
WHERE
	`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`) AND
	`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)
order by salary LIMIT 9,2;

Rég foglalkoztam SQL-lel, mysql-lel meg soha.

A következők jutnak eszembe:

1) egyfelől el kell fogadni, hogy csak azért, mert az SQL tud valamit, egyáltalán nem biztos, hogy használható lesz sebességben.

2) meg kell nézni, hogy mit próbál csinálni. Akár úgy, hogy végiggondolod, akár úgy, hogy ha van rá lehetőség, hogy a használt eszközt megkérdezed, és az megmondja, hogy ezt megnézi ennyiszer, azt megnézi annyiszor, stb.

2.1) ha bárhol full table scan van, az nem jó. Főleg nem, ha sokszor végrehajtja az elemzés szerint. Szóval ha valami mező szerint szűrsz, nézd meg, hogy van-e rajta index.

3) Ha mindig a max(from_date) kell, és az összes többi sora annak a táblának felesleges, akkor én egyfelől simán készítenék két view-t (current_title, current_salary). Ha a dátum mezőn van index, akkor a view maga várhatóan nem fog gyorsítani a dolgon, viszont jóval egyszerűbb lesz az SQL query. Én, személy szerint kedvelem a könnyebben átlátható query-ket, bár tudom, hogy nem mindenki van így ezzel.

3.1) Nem tudom, hogy a mysql tud-e materialised view-t, ha igen, akkor ez gyorsabb lehet, mert míg a view-ból select esetén a view deklarálásakor megadott query-t lefuttatja újra, a materialised view az gyakorlatilag táblaként viselkedik, szóval ott már nincs újabb query futtatás. Persze ha a view valami faék egyszerű dolog, akkor a view query futtatása nem lesz lassabb, mint a materialised query-t egyszerűen végigolvasnia.
Ha materialised view-t nem tud a mysql, akkor készíthetsz magadnak egy vagy több segédtáblát (amit triggerekkel mindig naprakészen tartasz), ami azt az adatot tartalmazza, ami neked kell, és a neked nem kellő adatokat nem kell végignéznie (de egyszerű query esetén lehet, hogy a view nem lassú).

Esetleg készíthetsz olyan materialised view-t vagy segédtáblát, amiben a neked kellő adatok ki vannak emelve: emp_no, current_title, current_salary. Így a lekérdezésed egyszerű is lesz és várhatóan elég gyors is.

disclaimer: ha valamit beidéztem és alá írtam valamit, akkor a válaszom a beidézett szövegre vonatkozik és nem mindenféle más, random dolgokra.

Az indexelésen már túl vagyunk, a dátum mezőkön van index; azt meg még csak beledrótoztam volna a PHP-ba, hogy ha az az egyetlen index hiányzik a `salary`-ról, akkor belerakom, de sajnos nem segített.
Materialised view-et már kérdezték, sajnos ez MySQL - ahogy gelei kolléga fogalmazott - itt nincs ilyen.

Az egyes pontban lehet, hogy igazad van, de hunludvig javaslatával minden baromi gyors lett, csak a `salary`-re rendezés nem. (Ill. a `dept_name`-ra sem, de azt tényleg el kell fogadni, mert egy kettős csatolás mögött van és amíg szűrni lehet rá azzal az egyszerű trükkel, hogy előtte egy pillanatnyi lekéréssel leszedem a `dept_no`-t és azzal szűrök helyette, ez a sorrendezésnél nem működik sajnos...)

MySQLhez nem értek, de left join felesleges a where miatt, szerintem

én valami ilyesmivel is próbálkoznék (persze nagyban függ milyen execution plant ad hozzá)

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
INNER JOIN (select emp_no
                  , title 
                  , row_number() over(partition by `emp_no` order by `from_date` desc) as pos  
            from `titles` 
            WHERE `titles`.`title`='Staff' 
            ) as t 
ON
	(t.`emp_no` = `employees`.`emp_no`) AND
	(t.pos =1)

INNER JOIN (select emp_no
                  , `salary`
                 , row_number() over(partition by `emp_no` order by `from_date` desc) as pos 
          from `salaries` 
         WHERE `salary`>='120000' 
          ) as s 
ON
	(s.`emp_no` = `employees`.`emp_no`) AND
        (s.pos =1)
LIMIT 0, 20

Ez a sorokat rendezi a táblában minden emp_no-n belül from_Date alapján csökkenő sorrendben. A row_number() ad egy sorszámot 1től az emp_no belül a a rendezésnek megfelelően.  Tehát emp_no legnagyobb from_Date hez tartozó sorát adja vissza ha pos=1 re szűrök: (ha több azonos from_date van akkor is 1et!!)

select emp_no
        , title 
from    (
        select  emp_no
                , title 
                , row_number() over(partition by `emp_no` order by `from_date` desc) as pos  
        from    `titles` 
        WHERE   `titles`.`title`='Staff' 
        ) x
where   pos=1

durván(!) ezt csinálja:


select  emp_no
        ,max(title) title 
from    `titles` t
join    (select emp_nmo 
                , max(from_Date) lst_dat
        from    `titles` 
        group by emp_nmo       
        ) lt 
    on  lt.emp_no=t.emp_no 
    and lt.lst_dat = t.from_date
WHERE   t.`title`='Staff'
group by emp_no 

Nem biztos, hogy jól értettem-e, de így gondoltad?

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
INNER JOIN (select  emp_no
        ,max(title) title 
from    `titles` t
join    (select emp_no 
                , max(from_Date) lst_dat
        from    `titles` 
        group by emp_no       
        ) lt 
    on  lt.emp_no=t.emp_no 
    and lt.lst_dat = t.from_date
WHERE   t.`title`='Staff'
group by emp_no ) as t 
ON
	(t.`emp_no` = `employees`.`emp_no`) AND
	(t.pos =1)

INNER JOIN (select  emp_no
        ,max(salary) salary 
from    `salaries` s
join    (select emp_no 
                , max(from_Date) lss_dat
        from    `salaries` 
        group by emp_no       
        ) ls 
    on  ls.emp_no=s.emp_no 
    and ls.lss_dat = s.from_date
WHERE   s.`title`='Staff'
group by emp_no ) as s 
ON
	(s.`emp_no` = `employees`.`emp_no`) AND
        (s.pos =1)
LIMIT 0, 20

Mert ez hibát dob:

Error in query (1052): Column 'emp_no' in field list is ambiguous

Az a baj, hogy ez már nagyon nem az én szintem...

fejből írtam :) nyilván kell az aliasokat használni :) pos=1 már nem kell így.

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
INNER JOIN (select  t . emp_no
        ,max(title) title 
from    `titles` t
join    (select emp_no 
                , max(from_Date) lst_dat
        from    `titles` 
        group by emp_no       
        ) lt 
    on  lt.emp_no=t.emp_no 
    and lt.lst_dat = t.from_date
WHERE   t.`title`='Staff'
group by t.  emp_no ) as t 
ON
	(t.`emp_no` = `employees`.`emp_no`) AND
---	(t.pos =1)

INNER JOIN (select  s. emp_no
        ,max(salary) salary 
from    `salaries` s
join    (select emp_no 
                , max(from_Date) lss_dat
        from    `salaries` 
        group by emp_no       
        ) ls 
    on  ls.emp_no=s.emp_no 
    and ls.lss_dat = s.from_date
WHERE   s.`title`='Staff'
group by s. emp_no ) as s 
ON
	(s.`emp_no` = `employees`.`emp_no`) AND
---        (s.pos =1)
LIMIT 0, 20

Így már működött, de sajnos nagyon lassú volt, kb. 8 sec volt, mire lefutott.

SELECT `employees`.*, `t`.`title`, `s`.`salary`
FROM `employees`
INNER JOIN (select  t . emp_no
        ,max(title) title 
from    `titles` t
join    (select emp_no 
                , max(from_Date) lst_dat
        from    `titles` 
        group by emp_no       
        ) lt 
    on  lt.emp_no=t.emp_no 
    and lt.lst_dat = t.from_date
WHERE   t.`title`='Staff'
group by t.  emp_no ) as t 
ON
	(t.`emp_no` = `employees`.`emp_no`)


INNER JOIN (select  s. emp_no
        ,max(salary) salary 
from    `salaries` s
join    (select emp_no 
                , max(from_Date) lss_dat
        from    `salaries` 
        group by emp_no       
        ) ls 
    on  ls.emp_no=s.emp_no 
    and ls.lss_dat = s.from_date
WHERE   s.`salary`>100000
group by s.emp_no ) as s 
ON
	(s.`emp_no` = `employees`.`emp_no`) 

LIMIT 0, 20

Magamnál kibővítettem amivel kellett. A szűrések dinamikusan kerülnek a lekérésbe, ahogy a sorrendezés is, de minden baromi gyorsan megy vele. (Kivéve a már említett fizetésre rendezést, de az sem mindig lassú, csak ha sok júzerrel kell dolgoznia.)

Nem add vissza mindent. 

lásd 10005 emp_no

 

Cserébe gyors! :P

Rossz a where záradék.

 

Ővé: 300024 rekord

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, salaries.from_date
FROM salaries 
LEFT JOIN `titles` ON (`titles`.`emp_no` = `salaries`.`emp_no`)
LEFT JOIN `employees` ON
(`salaries`.`emp_no` = `employees`.`emp_no`)
WHERE 
`salaries`.`from_date` = (SELECT max(from_date) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)
AND `titles`.`from_date` = (SELECT max(from_date) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)

 

Enyém: 331603 rekord

SELECT e.*, 
(SELECT  title FROM titles WHERE  titles.emp_no = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `titles` WHERE titles.emp_no = e.emp_no)) AS beosztas,
(SELECT salary FROM salaries WHERE `salaries`.`emp_no` = e.emp_no AND from_date = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = e.emp_no)) AS fizetes
FROM `employees` AS e
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = e.emp_no)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`

 

 

Eközben az employees tábla: 299512 :)

 

Na megyek aludni! :P

Akkor én ezt most nem értem. Az `employees` táblában 299025 sor van a DB szerint. Ha lekérem a számosságot, akkor nekem is 300024. Ha megpróbálom összefésülni a főtáblát a három segédtáblával (ld. itt), akkor beszól, hogy Error in query (1062): Duplicate entry '21076' for key 'PRIMARY', márpedig a frissen kreált - azaz üres - táblában a PRIMARY KEY az az `emp_no`, tehát a bejövő `emp_no`-k között ismétlés van. Vagy nem?

Próbáld ki ezt:

SELECT employees.*
FROM employees
LEFT JOIN titles ON titles.emp_no = employees.emp_no
AND titles.from_date = (SELECT MAX(from_date) FROM titles WHERE titles.emp_no = employees.emp_no)
AND titles.to_date = (SELECT MAX(to_date) FROM titles WHERE titles.emp_no = employees.emp_no)
LEFT JOIN salaries ON salaries.emp_no = employees.emp_no
AND salaries.from_date = (SELECT MAX(from_date) FROM salaries WHERE salaries.emp_no = employees.emp_no)
AND salaries.to_date = (SELECT MAX(to_date) FROM salaries WHERE salaries.emp_no = employees.emp_no)
LEFT JOIN dept_emp ON
dept_emp.emp_no = employees.emp_no
AND dept_emp.from_date = (SELECT MAX(from_date) FROM dept_emp WHERE dept_emp.emp_no = employees.emp_no)
AND dept_emp.to_date = (SELECT MAX(to_date) FROM dept_emp WHERE dept_emp.emp_no = employees.emp_no)
LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no;

Ugyanaz a helyzet, mint előtte. Ha egy azonos `emp_no`-hoz több csatolt sor is tartozik

 Modify	emp_no	title	from_date	to_date
 edit	10001	Senior Engineer	1986-06-26	2020-10-13
 edit	10001	Senior Engineer	2020-10-13	2020-10-13
 edit	10001	Senior Engineer1	2020-10-13	2020-10-13

akkor duplikálva jelennek meg a sorok.

emp_no	birth_date	first_name	last_name	gender	hire_date	title	salary	dept_no
10001	1953-09-02	Georgi	Facello	M	1986-06-26	Senior Engineer	88958	d005
10001	1953-09-02	Georgi	Facello	M	1986-06-26	Senior Engineer1	88958	d005

Nálam ez van lekérésnek:

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN `titles` ON (`titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON (`salaries`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)) AND
	(`titles`.`to_date` = '9999-01-01') AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)) AND
	(`salaries`.`to_date` = '9999-01-01') AND
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = `employees`.`emp_no`)) AND
	(`dept_emp`.`to_date` = '9999-01-01')

És ez 240124 sort ad vissza. A '9999-01-01'-re szűrések miatt amire a csatolt táblában nincs infó, az kiesik. Ha kiszedem őket

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN `titles` ON (`titles`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `salaries` ON (`salaries`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `dept_emp` ON (`dept_emp`.`emp_no` = `employees`.`emp_no`)
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`
WHERE
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`)) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`)) AND
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = `employees`.`emp_no`))

akkor 300053 sort ad. Valahol duplikációk vannak...
Az eredeti lekérés, amit írtam

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`, `departments`.`dept_no`
FROM `employees`
LEFT JOIN `titles` ON
	(`titles`.`emp_no` = `employees`.`emp_no`) AND
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `salaries` ON
	(`salaries`.`emp_no` = `employees`.`emp_no`) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `dept_emp` ON
	(`dept_emp`.`emp_no` = `employees`.`emp_no`) AND
	(`dept_emp`.`from_date` = (SELECT MAX(`from_date`) FROM `dept_emp` WHERE `dept_emp`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `departments` AS `departments` ON `dept_emp`.`dept_no` = `departments`.`dept_no`

az is 300053 sort ad vissza. Márványra printelt windowsmanuallal ütném, aki kitalálta ezt az elcseszett DB szerkezetet, semmi épeszűség nincs benne; teljesen alap lenne, hogy még ha jegyezem is a korábbi csatolt állapotokat, a jelenlegit akkor is tárolom a főtáblában. Igazán nem lehet mondani, hogy azzal sok helyet pazarolnánk, amikor a fizetések táblája nagyobb, mint a főtábla. Mégis csak az lesz, hogy csinálni kell egy összevont táblát ebből, de ahhoz még ki kell derítenem, hogy mi a francra reklamál, hogy duplikáció van.

Majdnem pont ezen vesztünk össze egy kollégával nemrég. Ő ragaszkodott hozzá, hogy márpedig csak adatokból kulcs az egyetlen helyes irány, míg én érveltem a fenti létjogosultsága mellett, pláne, hogy a már így összerakott 500 tábla mellé ne kezdjen el saját utat bejárni. Már nem kolléga.

Ez amúgy is igaz. Ha olyan oszlopot updatelsz, amire sűrűn kérdezel le, akkor amúgy is van rajta index, hogy a lekérdezés gyors legyen. És az ilyen oszlop módosításakor módosul az index is.

Általában a kompozit kulcsot adó adatot amúgy nem módosítod sűrűn - pont ezért lehet ő kompozit kulcs. Updatelni olyan adatot szokás, ami módosulhat úgy, hogy anélkül a sor identitását adó kulcs megváltozna.

Hiszen csak úgy nem birizgálunk ID-t a létrehozás után - akár kompozit kulcs, akár surrogate kulcs. Pont azért tud a kompozit kulcsban adatoszlop szerepelni mesterséges kulcs helyett, mert ez adja az adott sor identitását és nem igazán változik.

A surrogate key inkább azért szokás, mert akkor könnyű a kulcsot szerializálni, és máshol hivatkozhatóvá tenni.
Amúgy autoinrement mező helyett UUID sokkal jobb megoldás, több szempontból is.

Elosztott adatbázisoknál, pl. multi-master replication, egy bevett szokás. Jár valamennyi teljesítmény vesztéssel, meg a rendezhetőség veszik el, cserébe elég sok fejfájást meg tud spórolni, ha véletlen rossz helyre sync-kel az adatod (hogy ezzel de sokat szoptam már életemben...).

Az első pont a biztonság. Ha neked autoincrement azonosítóid vannak, akkor egy azonosító megszerzése után nagyon könnyen tudok következtetni más, valid azonosítókra és azokra lekérdezéseket futtatni stb. Ha kiadok több ilyen azonosítót valahova, akkor abból lehet következtetni sok mindenre. Akár még ipari kémkedésre is adhat információt az, hogy egy adott időszakban X volt az autoinrement mező értéke, másik időszakban meg Y.

Gondolj bele, mi lenne, ha a session azonosítók autoincrement mezők lennének valamilyen opaque azonosító helyett. Ha valaki megszerez egy élő session-azonosítót, akkor utána próbálkozás alapon a közeli azonosítókat végigpróbálva egy csomó érvényes session-be bele tudna nézni.

Ha UUID-et használsz, akkor hiába tud meg valaki egy érvényes azonosítót, nem tud következtetni arra, hogy mik a más érvényes azonosítók.

 

A második pont a megoszthatóság: egy UUID által azonosított adattáblát bármikor particionálhatsz több fizikai szerverre, az adattáblák a többi partícióval való kommunikáció nélkül is értelmesen frissíthetők.

Az UUID-ek ugyanis globális azonostók. Ha több, egymástól fizikailag elkülönülő szervezet (pl. egy multicég több üzeme sok országban - mondjuk a Tesco üzemek) azonosítani akarnak globálisan pl. a megrendeléseket, akkor a magyar, angol, lengyel, francia stb. adatbázisok sorai között sem lesz ID-ütközés, annak ellenére, hogy ezek az adatbázisok egymástól függetlenül frissülnek. Azaz egy azonosító globálisan csak egyszer fog szerepelni egy adattáblában, ahelyett, hogy ki kéne találni, hogy a 21324 ID az az angol, francia vagy magyar eladási adatbázisban van benne.

A harmadik pont a skálázhatóság: UUID által azonosított táblák esetén általában a beszúráskor a kliens határozza meg az új azonosítót, és nem a szerver. A kliensre van bízva, hogy generáljon egy érvényes UUID-et, ezáltal nem a szerver erőforrásait foglaljuk, amikor a sok-sok konkurrens tranzakciónál megpróbálja kitalálni, hogy melyik tranzakcióban beküldött adatsornak mi lesz az ID-ja. A kliensek meg tudják ezt anélkül tenni párhuzamosan, hogy ütköznének az UUID-ek.

Nyilván, előfordulhat ütközés - de ennek a valószínűsége kisebb, mint annak, hogy jön egy földönkívüli hadsereg és elpusztítja az adatbázist.

jahogyjaaaaaa.....

Elhagynám a legutolsó bejegyzés keresést és nézném adott dátumra. pl:

select e.*,s.salary,t.title
from employees as e
left join salaries as s
    on e.emp_no = s.emp_no
    and '2020-10-01' between s.from_date and s.to_date
left join titles as t
    on e.emp_no = t.emp_no
    and '2020-10-01' between t.from_date and t.to_date;

Így van de ez egy gyakorló db szóval .....

Telepítettem Mysqlt + az employees db-t .

Ha hozzá adtam csak ezt a két indexet.

ALTER TABLE `employees`.`salaries`  ADD INDEX `s` (`salary` DESC) VISIBLE;

ALTER TABLE `employees`.`titles` ADD INDEX `t` (`title` DESC) VISIBLE;
 

Az eredeti query töredék idő alatt fut le :)

index nélkül 4 sec, indexel 0.5 sec.

SELECT `employees`.*, `titles`.`title`, `salaries`.`salary`
FROM `employees`
LEFT JOIN `titles` ON
	(`titles`.`emp_no` = `employees`.`emp_no`) AND
	(`titles`.`from_date` = (SELECT MAX(`from_date`) FROM `titles` WHERE `titles`.`emp_no` = `employees`.`emp_no`))
LEFT JOIN `salaries` ON
	(`salaries`.`emp_no` = `employees`.`emp_no`) AND
	(`salaries`.`from_date` = (SELECT MAX(`from_date`) FROM `salaries` WHERE `salaries`.`emp_no` = `employees`.`emp_no`))
WHERE (`titles`.`title`='Staff') AND (`salaries`.`salary`>='120000')
LIMIT 0, 20

a topic indító :)

Szerkesztve: 2020. 10. 12., h – 22:51

-

Szerkesztve: 2020. 10. 13., k – 14:41

Így sikerült összeállítani a denormalizált táblát:

DROP TABLE IF EXISTS `e`;
CREATE TABLE `e`
(
	`emp_no` int(11) NOT NULL,
	`birth_date` date NOT NULL,
	`first_name` varchar(14) NOT NULL,
	`last_name` varchar(16) NOT NULL,
	`gender` enum('M','F') NOT NULL,
	`hire_date` date NOT NULL,
	`title` varchar(50) NOT NULL,
	`salary` int(11) NOT NULL,
	`dept_no` char(4) NOT NULL,
	`dept_name` varchar(40) NOT NULL,
	PRIMARY KEY (`emp_no`),
	INDEX `birth_date` (`birth_date`),
	FULLTEXT `first_name` (`first_name`),
	FULLTEXT `last_name` (`last_name`),
	INDEX `gender` (`gender`),
	INDEX `hire_date` (`hire_date`),
	FULLTEXT `title` (`title`),
	INDEX `salary` (`salary`),
	FULLTEXT `dept_no` (`dept_no`),
	FULLTEXT `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `e`
(
	SELECT
		`employees`.*,
		(SELECT `titles`.`title` FROM `titles` WHERE `titles`.`emp_no`=`employees`.`emp_no` ORDER BY `titles`.`from_date` DESC LIMIT 1) AS `title`,
		(SELECT `salaries`.`salary` FROM `salaries` WHERE `salaries`.`emp_no`=`employees`.`emp_no` ORDER BY `salaries`.`from_date` DESC LIMIT 1) AS `salary`,
		(SELECT `dept_emp`.`dept_no` FROM `dept_emp` WHERE `dept_emp`.`emp_no`=`employees`.`emp_no` ORDER BY `dept_emp`.`from_date` DESC LIMIT 1) AS `dept_no`,
		(
			SELECT `departments`.`dept_name` FROM `departments` WHERE `departments`.`dept_no`=
			(
				SELECT `dept_emp`.`dept_no` FROM `dept_emp` WHERE `dept_emp`.`emp_no`=`employees`.`emp_no` ORDER BY `dept_emp`.`from_date` DESC LIMIT 1
			)
			LIMIT 1
		) AS `dept_name`
	FROM `employees`
);

Csak sajnos ez 25+ sec. Igaz, hogy csak egyszer kell lefuttatni.

Egyébként a `dept_emp` táblában van egy ordas nagy bug az indexekkel, nevezetesen, hogy be van lőve PRIMARY-nak az `emp_code` és a `dept_no` együtt, így aztán ezzel a táblaszerkezettel olyat nem lehet, hogy valakit áthelyeznek egyik osztályról egy másikra, majd vissza, ugyanis ahhoz be kéne szúrni az ID-jét és az osztály ID-jét, ami kombó már létezik és nem engedi... Drakulálok. A másik két táblához hasonlóan itt is a `from_date` kellett volna, hogy legyen az `emp_code` párja.

Szerkesztve: 2020. 10. 14., sze – 21:35

Hogy ne csak beleokoskodjak:

select employees.*, salaries.salary, titles.title from salaries 
inner join (select emp_no, max(from_date) as from_date from salaries where salary >=120000 group by emp_no ) 
    as maxdate on maxdate.emp_no=salaries.emp_no and maxdate.from_date = salaries.from_date 

inner join (select titles.emp_no, titles.title from titles 
inner join (select emp_no, max(from_date) as from_date from titles where title='staff' group by emp_no ) 
    as maxdate on maxdate.emp_no=titles.emp_no and maxdate.from_date = titles.from_date) as titles on salaries.emp_no = titles.emp_no 

inner join employees on salaries.emp_no = employees.emp_no;

Eredménye:

1751 rows in set (3.525 sec)

Ha viszont limit 0,20- al futtatom, akkor 0.276 mp alatt lefut.

MariaDB [employees]> explain select employees.*, salaries.salary, titles.title from salaries inner join (select emp_no, max(from_date) as from_date from salaries where salary >=120000 group by emp_no ) as maxdat
e on maxdate.emp_no=salaries.emp_no and maxdate.from_date = salaries.from_date inner join (select titles.emp_no, titles.title from titles inner join (select emp_no, max(from_date) as from_date from titles where
title='staff' group by emp_no ) as maxdate on maxdate.emp_no=titles.emp_no and maxdate.from_date = titles.from_date) as titles on salaries.emp_no = titles.emp_no inner join employees on salaries.emp_no = employe
es.emp_no;
+------+-----------------+------------+--------+---------------+---------+---------+-------------------------------------------------------+--------+-----------------------------------------------------------+
| id   | select_type     | table      | type   | possible_keys | key     | key_len | ref                                                   | rows   | Extra                                                     |
+------+-----------------+------------+--------+---------------+---------+---------+-------------------------------------------------------+--------+-----------------------------------------------------------+
|    1 | PRIMARY         | employees  | ALL    | PRIMARY       | NULL    | NULL    | NULL                                                  | 299290 |                                                           |
|    1 | PRIMARY         | titles     | ref    | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no                            |      1 | Using index                                               |
|    1 | PRIMARY         | <derived2> | ref    | key1          | key1    | 4       | employees.employees.emp_no                            |      2 | Using where                                               |
|    1 | PRIMARY         | salaries   | eq_ref | PRIMARY       | PRIMARY | 7       | employees.employees.emp_no,maxdate.from_date          |      1 |                                                           |
|    1 | PRIMARY         | <derived4> | ref    | key2          | key2    | 8       | employees.employees.emp_no,employees.titles.from_date |     10 |                                                           |
|    4 | DERIVED         | titles     | index  | PRIMARY       | PRIMARY | 209     | NULL                                                  | 442248 | Using where; Using index; Using temporary; Using filesort |
|    2 | LATERAL DERIVED | salaries   | ref    | PRIMARY       | PRIMARY | 4       | employees.employees.emp_no                            |      4 | Using where                                               |
+------+-----------------+------------+--------+---------------+---------+---------+-------------------------------------------------------+--------+-----------------------------------------------------------+
7 rows in set (0.001 sec)

 

Feladat szöveges megfogalmazása: válaszd ki azokat akiknek az utolsó titulusa staff, és az utolsó fizetése >= 120000.

 

A konfigom (lusta voltam leszűrni a releváns részeket, Ctrl+C/Ctr+V):

Chassis:	Thermaltake V4 Black Edition (VM30001W2Z)
Supply:		Corsair CX430
MB:		GIGABYTE GA-970A-DS3 rev 1.0
BIOS:		Award, version F6 (23/10/2012)
CPU:		AMD FX-8350 Octa-Core 4.0GHz AM3+
DDR3_1:		Corsair Vengeance LP 4GB DDR3 1600MHz (CML8GX3M2C1600C9)
DDR3_2:		Corsair 2GB DDR3 1600MHz (CMX4GX3M2A1600C9)
DDR3_3:		Corsair Vengeance LP 4GB DDR3 1600MHz (CML8GX3M2C1600C9)
DDR3_4:		Corsair 2GB DDR3 1600MHz (CMX4GX3M2A1600C9)
PCIEX1_1:	Delock 89219
PCIEX16:	GIGABYTE GeForce GTX 960 (GM206) 4GB GDDR5 128-bit
PCIEX1_2:	-
PCIEX1_3:	-
PCIEX4:		Creative Sound Blaster Audigy RX (70SB155000001) [SB0400]
PCI1:		ASUS My Cinema-P7131
PCI2:		Gembird SPC-1 (MCS9865)
SATA #0:	ADATA SP900
SATA #1:	ADATA SP900
SATA #2:	WDC WD5000AUDX-73H9TY0
SATA #3:	Seagate ST3500412AS
SATA #4:	LG GH24NS70
SATA #5:	LG GH24NS70

8 éves gép, de azért még 2020-ban is megállja a helyét. Úgy-ahogy... :P

Sz*rk: Egyébként szerintem neked a második lekérés, amikor a limitet is ráhúztad, az már a cache miatt gyorsult annyit, hiszen ugyanazt a lekérést kérted le még egyszer, csak ezúttal limittel.