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

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

indexek a használt mezőkön?

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!!?!? :-)

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.

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

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.

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.

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

-

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.