Lassú lekérdezések miatt MySQL adatbázis újratervezése

Ez a téma lezárásra került a moderátor által.
Lassú lekérdezések miatt MySQL adatbázis újratervezése
2022-01-29T02:44:27+01:00
2022-02-09T08:33:48+01:00
2022-10-15T21:20:49+02:00
*deleted_23419333
Sziasztok!

Adatok:

Kiszolgáló: Localhost via UNIX socket Kiszolgáló típusa: MySQL Kiszolgáló verziója: 8.0.27-0ubuntu0.20.04.1 - (Ubuntu) Protokoll verzió: 10 A kiszolgáló karakterkódolása: UTF-8 Unicode (utf8mb4) Apache/2.4.41 (Ubuntu) Adatbázis-kliens verziója: libmysql - mysqlnd 7.4.3 PHP-kiterjesztés: mysqli PHP verzió: 7.4.3

A felhasználók tábla:

CREATE TABLE `profiles` ( `uid` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

A felhasználók adatainak táblája:

CREATE TABLE `profilemeta` ( `meta_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `uid` int NOT NULL, `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `meta_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

A két tábla közti kapocs:

`profiles`.`uid` = `profilemeta`.`uid`

1 profiles sorhoz alapból 17 profilemeta sor tartozik, de ha változnak az adatok, akkor ennél akár jóval több is lehet.

Körülbelül 100.000 profiles sor van és így több mint 1.700.000 profilemeta sor.

Listázni kellene. Ehhez kértem már segítséget a korábbi kérdéseimnél.

Minden tökéletesen működik. Egy dolgot leszámítva. Nagyon-nagyon lassú.

Jelenleg csak 375 profiles sor van és 6.381 profilemeta sor feltöltve, de már így is nagyon lassú.

Nincs értelme feltöltenem a többi adatot is. Újra kell gondolnom az egészet és ehhez szeretnék segítséget kérni.

Az első ötletem az volt, hogy mind a 17 féle adatot hozzáadom a profiles táblához (is), így az 2 helyett most 19 oszlopból állna és nem lenne 2 tábla, csak 1, illetve egy másik táblában tárolnám az adatok változását.

Ez nyilván jobb lenne mert lehetne INDEX-elni az oszlopokat, viszont ha már így alakult. Lehetne ezzel kezdeni valamit?

Az alábbiakra nem kell szűrni a lekérdezésben:

`profiles`.`uid` `profiles`.`added`

... viszont szinte az összes 17 tulajdonságra igen, LIKE-al, valamint szinte mind a 17 tulajdonság szerint tudni kell ORDER BY segítségével sorba rendezni.

Sokáig ezt használtam (⊓≡⊺∧⊓ɢ⋿∟):

SELECT `p2`.* FROM (SELECT `p`.`uid` , `p`.`username` , GROUP_CONCAT(IF(`pm`.`meta_key` = 'first_name', `pm`.`meta_value`, NULL)) AS `first_name` , GROUP_CONCAT(IF(`pm`.`meta_key` = 'last_name', `pm`.`meta_value`, NULL)) AS `last_name` FROM `profiles` AS `p` LEFT JOIN `profilemeta` AS `pm` ON ( `pm`.`uid` = `p`.`uid` ) INNER JOIN ( SELECT `uid`, `meta_key`, MAX(`added`) AS `added` FROM `profilemeta` GROUP BY `uid`, `meta_key` ) AS `t` ON ( `t`.`uid` = `pm`.`uid` AND `t`.`meta_key` = `pm`.`meta_key` AND `t`.`added` = `pm`.`added` ) WHERE `p`.`uid` = 2022) `p2` WHERE `p2`.`last_name`='Mézga'

Ezzel csak az volt a baj, hogy nekem kell a 17 aktuális adat és ehhez még kell 4 korábbi adat is, így áttértem ennek a használatára (Bzzs):

SELECT `p2`.* FROM ( SELECT `p`.*, (SELECT `meta_value` FROM `profilemeta` WHERE `meta_key`='last_name' AND `uid`=`p`.`uid` ORDER BY `added` DESC LIMIT 1 ) AS `last_name`, (SELECT `meta_value` FROM `profilemeta` WHERE `meta_key`='last_name' AND `uid`=`p`.`uid` ORDER BY `added` DESC LIMIT 1,1) AS `prev_last_name` FROM `profiles` AS `p` ) `p2` WHERE `p2`.`type_id`='1' ORDER BY cast( `score` as UNSIGNED ) ASC LIMIT 25

Tudom, hogy az itt leírtak megvalósításához kevés tudással rendelkezem, de ha már elkezdtem, akkor szeretném befejezni ezt a projektet, aztán továbbállni, vagyis programozás helyett inkább mással foglalkozni.

„Csak” ez a két dolog hiányzik a projektből:

1. jól felépített adatbázis
2. hozzá egy jó lekérdezés

Minden mást már megcsináltam.

Az említett témánál írta ⊓≡⊺∧⊓ɢ⋿∟, hogy: „Gondolom, az adatbázis kialakítását megfelelő tervezés előzte meg, gondosan ügyelve a megfelelő indexek/kulcsok használatára, ugye?!” - tudtam, hogy bajban leszek, de hogy ekkorában, azt nem gondoltam. Nem gondoltam, hogy egy lekérdezés lehet ennyire lassú. Ha tudom, akkor nyilván ezzel kezdem az egész projektet és csak aztán csinálom a többit, illetve valószínűleg el se vállalom. Így jártam és most segítséget kérek. Utoljára. A történtek után legfeljebb csak WordPress oldalak összekattintgatását fogom vállalni, bár szerintem inkább azt se. A WordPress oldalak összekattintgatásánál talán még lapátolni is jobb.

A helyzet egyébként egyszerű. Egy egyszerű példával szemléltetve: van 100.000 ember, mind a 100.000 embernek 17 tulajdonságát kell figyelni és ezekre tudni kell szűrni. Például: szemszín, hajszín, fogak száma, hajszálak száma stb és ebből a 17 adatból 4 korábbi adatra is kíváncsiak vagyunk. Tehát kell egy 17+4=21 oszlopból álló HTML táblázatot megjeleníteni. Minden oszlop fejléce alatt van egy beviteli mező, ebbe ha beír valamit a lekérdező akkor azt az oszlopot LIKE-al szűrni kell, illetve minden oszlopra rá lehet kattintani és aszerint rendezni (ASC/DESC). Ez már egyébként mind meg van és úgy működik ahogy kell.

Nézegettem a React Table-t, de szeretném natívan megvalósítani és tényleg minden működik már natívan, „csak” egyedül a lassúság a probléma.

A korábbi adatok egyébként HTML táblázatokból vannak kiszedve, tehát statikus HTML fájlokból, így ennek nem létezett még MySQL adatbázisa.

Nagyon szépen köszönöm azoknak akik veszik a fáradtságot és segítenek ebben az utolsó, nagy csatában.
Mutasd a teljes hozzászólást!
Általánosságban az indexelés előnye, hogy egy-egy keresésnél vagy táblák közötti kapcsolatnál az adatbáziskezelőnek nem kell az összes adatot bejárnia. A hátránya viszont hogy nagyobb lesz az adatbázis mérete, és új adat beszúrásánál, létező adat szerkesztésénél és törlésénél az indexeket is rendbe kell raknia, ezért az insert update és delete query-k lassabbak, mint indexek nélkül. Úgyhogy (főleg nagy adatbázisoknál) csak a legszükségesebb indexeket szabad létrehozni.

A minimum, hogy a táblákat el kell látni elsődleges kulccsal, ez a két tábládban megvan. A táblákat összekapcsoló mezőket idegen kulcsokkal kell összekapcsolni. A query-ben szereplő két tábla között az uid mező a kapcsolat. Ez a profiles táblában primary key, a profilemeta táblában legyen idegen kulcs:

ALTER TABLE profilemeta ADD FOREIGN KEY (uid) REFERENCES profiles (uid);
A szűrési feltételekben előforduló mezőket is indexelni kell, de hogy hogyan, az a feltételek jellegétől függ. A kérdésben lévő két query-ben a meta_key és a last_name teljes tartalmára keresel, ezért egy ilyen index segíthet:

CREATE INDEX meta_key ON profilemeta (meta_key); CREATE INDEX meta_key ON profilemeta (last_name);
De ha van összetettebb szűrési feltétel, pl AND kapcsolattal, akkor már többoszlopos indexre van szükség. A LIKE operátor használata sem olyan egyszerű egy ekkora adatmennyiségnél. Főleg akkor, ha %valami% jellegű keresést végzel, azaz a kifejezés előtt és után is tetszőleges karakterek lehetnek. Ilyenkor néha az egész where záradék optimalizálásán is gondolkodni kell.

A mysql ezeket az iránymutatásokat adja: 
MySQL :: MySQL 8.0 Reference Manual :: 8.3 Optimization and Indexes

Nem tudom hogy ez mennyire segített. Egy átfogó optimalizáláshoz az összes táblát és az összes queryt ismerni kéne. Ahogy korábban Netangel is írta, az adatbázist jól meg kell tervezni, ez az egyik alapja egy jó programnak. Ezt lehet csak igazán optimalizálni, és akár ilyen sok adatot is kezelni vele. Sajnos ami párszáz adattal tökéletesen működik, az pár százezer adattal már nem biztos hogy menni fog. A Te adatmennyiséged esetén, néhányan már a mysql cseréjén is elgondolkodnak.
Mutasd a teljes hozzászólást!

  • Az adatok megejelenítését hogy végzed? Egyszerre létrehozod az egész táblázatot a weboldalon, vagy van neki lapozója? Egy jQuery DataTable megoldáson nem gondolkodtál? Itt megoldható, hogy csak 10-20 adatot kell lekérni az adatbázisból, mert a táblázat egyszerre csak ennyit jelenít meg. Ez a keresés lassúságára nem jelent megoldást, de az adatok megjelenítésében segíthet.
    Mutasd a teljes hozzászólást!
  • Köszönöm, hogy írtál. Van lapozó LIMIT-tel. Oldalanként 25 sor van megjelenítve. Csak ennek a lekérdezésnek kell gyorsnak lennie és csak ennek a HTML táblázatnak. Az UPDATE/INSERT és egyéb SELECT-ek nem baj ha lassúak.
    Mutasd a teljes hozzászólást!
  • Csak ezt futtattam le egyelőre:

    ALTER TABLE profilemeta ADD FOREIGN KEY (uid) REFERENCES profiles (uid);

    ... és mindjárt elsírom magam annyira gyors lett. Eddig 20+ másodperc volt a lekérdezés, most 1 mp.

    Feltöltöm a többi adatot is, aztán majd jövök még ide. Ha bárkinek bármi ötlete, javaslata, tanácsa van, azt szívesen meghallgatom.

    Mutasd a teljes hozzászólást!
  • Én a következőt csinálnám:

    - A META adatokat idegen kulccsal összekötném az ALAP táblával. Ez ugye létrehozza az indexeket amiket megtehetsz magad is. Az idegen kulcsok abban segítenek, hogy az "árva" rekordot kialakulását kézben lehet tartani (azaz pl. elég az ALAP táblából törölni jelen esetben az UID mező alapján, aztán az húzza magával a META adatokat is).
    - A META adatokban történő változásokat triggerrel (AFTER UPDATE) követném le, és egy harmadik táblába tenném el (UID, METAKULCS, REGIERTEK, UJERTEK, IDOBELYEG).
    - Amely' mezőkre szövegesen keresni kell, arra indexet kell tenni. Minden SQL lekérdezést meg kell nézni EXPLAIN-el milyen QUERY PLAN-t generál belőle az SQL motor.
    - Ha sok az adat, akkor pedig még tovább fűszerezném:

    - 1: csinálnék egy kereső táblát amibe szintén triggerekkel bekerülnének a szöveges mezők. Ez MyISAM típusú lenne (nem InnoDB), és a szöveges mezőre FULLTEXT indexet tennék, majd az erre szolgáló MATCH / AGAINST SQL utasításokkal súlyoznám a találatokat. (Nem emlékszem pontosan, lehet hogy újabb MySQL / MariaDB InnoDB-vel is tudja a FULLTEXT-et.)
    - 2: Elasticsearch

    Amennyiben a szoftvernek a lelkét a tárolt adatok kezelése és azok manipulálása, az azokban keresés stb. adja, akkor az adatbázis struktúra tervezés kell legyen az első lépés. Ez sokszor nem triviális, kell gyakorlat hozzá. Az EXPLAIN sokat segít.
    Mutasd a teljes hozzászólást!
  • Hali!

    … aztán továbbállni, vagyis programozás helyett inkább mással foglalkozni.

    Ezt már említetted egyszer-kétszer. Ne tedd (bár a te dolgod). Inkább a hozzáálláson változtass kicsit: gondosabb tervezés/előkészítés, tanulás, információk szerzése, valamint nem elvállalni „túl nehéz” munkákat (értsd: a teherbírásodhoz/tudásodhoz túl nehéz).

    Minden tökéletesen működik. Egy dolgot leszámítva. Nagyon-nagyon lassú.

    Mennyire nagyon-nagyon lassú? Indexek vannak?

    … viszont szinte az összes 17 tulajdonságra igen, LIKE-al…

    Elképzelhető, hogy FULLTEXT indexet rakva a `meta_value` mezőre és MATCH (…) AGAINST (…) lehetőséget használva a LIKE helyett jobban jársz (persze, vizsgálni/mérni kellene, ha egyáltalán megfelelő ez neked).

    … így áttértem ennek a használatára (Bzzs)…

    Úgy tűnik, hogy megint elhallgatsz infókat. Az itt megadott tábla-létrehozó SQL-mondataid által megalkotott táblák nincsenek összhangban ezzel a lekérdezéssel, amire áttértél. Azaz, hol van/mi az a `type_id` és `score` mező?
    Egyébként, én – a pontos infók hiányában – kapásból azt mondanám, hogy ez a lekérdezés a sub-select-es oszlopok és a – valószínűsíthető – indexek hiánya miatt lassú.

    De ha tényleg nagyon-nagyon lassú, akkor én más megközelítést javasolnék. A legfontosabb: minden mezőre, ami szűrésben és/vagy kapcsolatban szerepel, indexet raknék (és igen, csatlakozva pamacs_01 egyik észrevételéhez: idegen kulcsot is használnék). Tehát, valami hasonló tábla-létrehozó SQL-mondatok lennének:
    CREATE TABLE `profiles` ( `uid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Azonosító', `username` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Felhasználó-név', `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Felvétel ideje', PRIMARY KEY `PK_uid` (`uid`), UNIQUE KEY `UK_username` (`username`), KEY `K_added` (`added`), CONSTRAINT `CHK_username` CHECK (TRIM(`username`) != '') ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci COMMENT 'Profilok táblája'; CREATE TABLE `profilemeta` ( `meta_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Azonosító', `uid` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Profil-azonosító', `meta_key` VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Jellemző megnevezése', `meta_value` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Jellemző értéke', `added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Jellemező felvételének/beállításának ideje', PRIMARY KEY `PK_metaId` (`meta_id`), KEY `K_metaKey` (`meta_key`), KEY `K_metaValue` (`meta_value`), KEY `K_added` (`added`), FOREIGN KEY `FK_profilemeta_uid_profiles` (`uid`) REFERENCES `profiles` (`uid`) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT `CHK_metaKey` CHECK (`meta_key` REGEXP '^[a-z][a-z0-9_]*$') ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci COMMENT 'Profil-jellemzők táblája';
    A lekérdezésben pedig window function-öket használnék. Főleg a LAST_VALUE() és az NTH_VALUE() lehet érdekes. Az előbbivel egy adott csoportosítás és rendezettség szerint az utolsó sor által meghatározott értéket, az utóbbival egy adott csoportosítás és rendezettség szerint az N. sor által meghatározott értéket kapod meg. A LAST_VALUE()-val pl. le tudod kérdezni a `profilemeta` táblában minden sorhoz az azonos `uid`-val és `meta_key`-jel rendelkező sorok utolsó `meta_id`-ját, és ha ez ugyanaz, mint az adott sor `meta_id`-ja, akkor az az utoljára megadott érték. Az NTH_VALUE()-val le tudod kérdezni minden sorhoz (azonos `uid` és `meta_key`) az utolsó előtti `meta_id`-t (fordított rendezettséggel), így ha ez ugyanaz, mint a sor `meta_id`-ja, akkor az utolsó előtt érték. Tehát:
    SELECT `p`.`uid` , `p`.`username` , GROUP_CONCAT(IF(`p`.`meta_key` = 'first_name' AND `p`.`last` = 1, `p`.`meta_value`, NULL)) AS `first_name` , GROUP_CONCAT(IF(`p`.`meta_key` = 'first_name' AND `p`.`previous` = 1, `p`.`meta_value`, NULL)) AS `first_name_previous` , GROUP_CONCAT(IF(`p`.`meta_key` = 'last_name' AND `p`.`last` = 1, `p`.`meta_value`, NULL)) AS `last_name` , GROUP_CONCAT(IF(`p`.`meta_key` = 'last_name' AND `p`.`previous` = 1, `p`.`meta_value`, NULL)) AS `last_name_previous` , GROUP_CONCAT(IF(`p`.`meta_key` = 'age' AND `p`.`last` = 1, `p`.`meta_value`, NULL)) AS `age` , GROUP_CONCAT(IF(`p`.`meta_key` = 'age' AND `p`.`previous` = 1, `p`.`meta_value`, NULL)) AS `age_previous` FROM ( WITH `profilemeta_recent` AS ( SELECT `pm`.* , IF(LAST_VALUE(`pm`.`meta_id`) OVER ( PARTITION BY `pm`.`uid`, `pm`.`meta_key` ORDER BY `pm`.`meta_id` ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) = `pm`.`meta_id`, 1, 0) AS `last` , IF(NTH_VALUE(`pm`.`meta_id`, 2) OVER ( PARTITION BY `pm`.`uid`, `pm`.`meta_key` ORDER BY `pm`.`meta_id` DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) = `pm`.`meta_id`, 1, 0) AS `previous` FROM `profilemeta` AS `pm` -- GROUP BY `pm`.`uid`, `pm`.`meta_key`, `pm`.`meta_id`, `pm`.`meta_value`, `pm`.`added` ) SELECT `p`.`uid` , `p`.`username` , `pmr`.`meta_key` , `pmr`.`meta_value` , `pmr`.`last` , `pmr`.`previous` FROM `profilemeta_recent` AS `pmr` INNER JOIN `profiles` AS `p` ON (`p`.`uid` = `pmr`.`uid`) WHERE `pmr`.`last` = 1 OR `pmr`.`previous` = 1 ) AS `p` GROUP BY `p`.`uid`
    … és mindjárt elsírom magam annyira gyors lett. Eddig 20+ másodperc volt a lekérdezés, most 1 mp.

    Hogy mennyit számítanak azok a „fránya” indexek, ugye?

    Mutasd a teljes hozzászólást!
  • Hali!

    A hátránya viszont hogy nagyobb lesz az adatbázis mérete, és új adat beszúrásánál, létező adat szerkesztésénél és törlésénél az indexeket is rendbe kell raknia, ezért az insert update és delete query-k lassabbak, mint indexek nélkül.

    Ez a „hátrány” nagyon ici-pici hátrány csak (sőt, túlnyomórészt észre sem veszed – az indexek hiányát sokkal előbb és hangsúlyosabban).

    Úgyhogy (főleg nagy adatbázisoknál) csak a legszükségesebb indexeket szabad létrehozni.

    Nem. Mindig a szükséges indexeket kell létrehozni – főleg nagy adatbázisoknál (pont ott a lényegesebb). Nem az adatbázis(-tábla) mérete, sokkal inkább a célja/felhasználása alapján kell mérlegelni index(ek) elhagyását (ha kell egyáltalán). Pl. abban az esetben lehet gázos az index-használat (főleg több/sok index használata), ha az adott tábla inkább „write-only” és nagyon gyakori az írás. Tipikusan ilyen lehet pl. egy log-tábla, bár log-táblára vannak célszerűbb adatbázis-motorok (persze, az igények, környezet, feltételek, stb. dönt itt is).

    A hozzászólásod többi részével nagyjából egyetértek.

    Mutasd a teljes hozzászólást!
  • Úgy tűnik, hogy megint elhallgatsz infókat.

    Köszönöm a hozzászólásokat. Megpróbálom majd értelmezni őket és jövök még, de addig is:

    profiles szerkezet (örülnék ha ez maradhatna a végleges):

    CREATE TABLE `profiles` ( `uid` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `upload` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    profiles adatok:

    INSERT INTO `profiles` ( `uid`, `upload` ) VALUES ( 1023, '2022-01-29 13:11:00' );

    profilemeta szerkezet (örülnék ha ez maradhatna a végleges):

    CREATE TABLE `profilemeta` ( `meta_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `upload` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `uid` int NOT NULL, `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `meta_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    profilemeta adatok (uid: 1023) [alap]:

    INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 1, '2022-01-29 13:11:00', 1023, 'last_name', 'Megszentségteleníthetetlenségeskedéseitekért' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 2, '2022-01-29 13:11:00', 1023, 'profile_photo_remote_url', 'https://*/mezga.jpeg' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 3, '2022-01-29 13:11:00', 1023, 'tags', '{ "Budapest": 1, "Debrecen": 2 }' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 4, '2022-01-29 13:11:00', 1023, 'score', '100' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 5, '2022-01-29 13:11:00', 1023, 'location', 'Hungary' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 6, '2022-01-29 13:11:00', 1023, 'date', '1455-11-12' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 7, '2022-01-29 13:11:00', 1023, 'position', '1' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 8, '2022-01-29 13:11:00', 1023, 'total_articles', '5' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 9, '2022-01-29 13:11:00', 1023, 'total_private_articles', '10' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 10, '2022-01-29 13:11:00', 1023, 'url', 'https://*/mezga' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 11, '2022-01-29 13:11:00', 1023, 'a', '50' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 12, '2022-01-29 13:11:00', 1023, 'b', NULL ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 13, '2022-01-29 13:11:00', 1023, 'c', NULL ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 14, '2022-01-29 13:11:00', 1023, 'd', NULL ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 15, '2022-01-29 13:11:00', 1023, 'type_id', '1' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 16, '2022-01-29 13:11:00', 1023, 'first_name', 'Aladár' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 17, '2022-01-29 13:11:00', 1023, 'username', 'M. Aladár' );

    Látható, hogy a dátum a profiles táblában és az első adathalmazban megegyezik és az is látható, hogy a `meta_value` TEXT típusú, de vannak jócskán szám típusúak is, illetve JSON és DATE. Ennek csak sorbarendezésnél van jelentősége (szerintem), de akkor használom ezt, a számoknál:

    ORDER BY cast( `score` as UNSIGNED ) ASC

    profilemeta adatok (uid: 1023) [frissítés:1]:

    INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 18, '2022-01-29 21:12:00', 1023, 'last_name', 'Mézga' );

    profilemeta adatok (uid: 1023) [frissítés:2]:

    INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 19, '2022-01-29 22:33:00', 1023, 'a', '60' ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 20, '2022-01-29 22:33:00', 1023, 'b', NULL ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 21, '2022-01-29 22:33:00', 1023, 'c', NULL ); INSERT INTO `profilemeta` ( `meta_id`, `upload`, `uid`, `meta_key`, `meta_value` ) VALUES ( 22, '2022-01-29 22:33:00', 1023, 'd', NULL );

    Ha az a|b|c|d közül csak egyben is változás történik, akkor mind a 4 hozzáadásra kerül. Később egy másik oldalon így könnyebb listázni. Látható a példában, hogy az 50 megváltozott 60-ra, vagyis az a adat, de mivel ez változott, így hozzáadásra került a b, c, d is, hol ott azok nem változtak mert maradtak NULL.

    Látható, hogy minden egyidejű frissítésnél egyezik a dátum. Így később visszakereshető lesz az, hogy egy adat változásakor még mennyi adat változott abban az időben.

    Alapjában 4 oldalon van HTML táblázat mert 4 féle `type_id` lehetséges: 1, 2, 3, 4.

    Nagyon örülnék ha nem kellene megváltoztatnom a 2 tábla szerkezetét, mármint INDEX-ek meg ilyen kiegészítések persze jöhetnek.

    Mivel 4 oldal van a type_id szerint, így biztos logikusabb lenne a profiles táblában tárolni ezt az adatot, de mivel ez is változhat, örülnék ha a profiles megmaradhatna csak ezzel a 2 oszloppal és minden adat menne a profilemeta táblába.

    A HTML táblázat fejléce:

    <tr> <th>profile_photo_remote_url</th> <!-- nem kell LIKE/szűrés --> <th>last_name</th> <th>username</th> <th>total_articles</th> <th>total_private_articles</th> <th>url</th> <!-- nem kell LIKE/szűrés --> <th>tags</th> <th>position</th> <th>score</th> <th>location</th> <th>prev_a</th> <!-- előző adat --> <th>a</th> <!-- utolsó, aktuális --> <th>prev_b</th> <!-- előző adat --> <th>b</th> <!-- utolsó, aktuális --> <th>prev_c</th> <!-- előző adat --> <th>c</th> <!-- utolsó, aktuális --> <th>prev_d</th> <!-- előző adat --> <th>d</th> <!-- utolsó, aktuális --> <th>date</th> </tr>

    Látható, hogy ezek nem kellenek:

    - type_id azért nem kell mert ez alapból eldől hogy melyik oldalon jelenítjük meg a táblázatot: $_GET [ 'type_id' ] - first_name nem kell megjeleníteni, se szűrni, se semmi (ez a profil adatlapján szerepel csak)

    A profile_photo_remote_url és az url kivételével mindre kell tudni szűrni, keresni.

    A HTML táblázat második sorában ilyenek szerepelnek:

    <tr> <th></th> <th><input type="text" name="last_name" /></th> <th><input type="text" name="username" /></th> <!-- stb. --> </tr>

    A HTML táblázat első sorára kattintáskor van ORDER BY ASC/DESC.

    A táblázatokba ide jönnek az adatok JS fetch használatával:

    <tbody id="data"></tbody>

    A JS fetch működik, csak néhány aprósággal kell kiegészítenem.
    Mutasd a teljes hozzászólást!
  • hátránya viszont hogy nagyobb lesz az adatbázis mérete

    Az adatbázis mérete nem számít. Ha 1 KB helyett 1 TB lesz, az se érdekes, csak a fő lekérdezés gyors legyen. Illetve nem számít az összes többi lekérdezésnek az ideje sem, csak ez a fő táblázat (illetve 4 táblázat) a lehető legrövidebb időn belül betöltődjön. Ha 5 másodpercen belül maradna a táblázat(ok) betöltése akkor mindenki boldog.
    Mutasd a teljes hozzászólást!
  • Amennyiben a szoftvernek a lelkét a tárolt adatok kezelése és azok manipulálása, az azokban keresés stb. adja, akkor az adatbázis struktúra tervezés kell legyen az első lépés. Ez sokszor nem triviális, kell gyakorlat hozzá. Az EXPLAIN sokat segít.

    Nem gondoltam, hogy ilyen lassú is lehet egy lekérdezés pedig egyszer már belefutottam egy ilyen lassú lekérdezésbe és akkor is az INDEX-elés mentette meg az életemet. Legközelebb mindenképp az adatbázistervezéssel fogom kezdeni. Még kisebb projektek esetében is mert később kitudja mire híznak meg ezek a táblázatok.

    Valószínűleg azért alakult ki bennem egy téves elképzelés az INDEX-ek használatáról mert régen volt egy nagyon nagy adatbázisom, de ott csak egy INDEX volt, egy `id` AUTO_INCREMENT PRIMARY KEY és ez anélkül, hogy tudtam volna, elégséges volt. De ott nem voltak táblák közti kapcsolatok, csak nagyon egyszerű lekérdezések és mind az `id`-re fókuszálva.
    Mutasd a teljes hozzászólást!
  • Erre tudna valaki érdemi magyarázatot?
    Hacsak nem változott valami mióta nyugdíjaztam magam, a foreign key constraint nem csinál egyebet, mint megakadályozza, hogy olyan adatot törölj/módosíts, amire a táblád hivatkozik, illetve hogy úgy vegyél fel sorokat a tábládba, hogy még nem létezik hozzá "szülő". A hivatkozás alapja meg a szülő tábla elsődleges kulcsa.
    Ez mitől gyorsítana bármit?
    Valami változott? Vagy valamite rosszul emlékszem?
    Mutasd a teljes hozzászólást!
  • Semmit se változtattam. Okostelefonon stopperórával lemértem és ~24 másodperc volt az adatok betöltése, majd jött ez, csak ez, semmi más:

    ALTER TABLE profilemeta ADD FOREIGN KEY (uid) REFERENCES profiles (uid);

    És a ~24 másodperc ~1 másodpercre rövidült.
    Mutasd a teljes hozzászólást!
  • Készítsd el a teszt adatbázist amit a kérdező nyitó hozzászólásában megtalálsz. Tedd rá azt az FK-t és nézd meg mit csinál a MySQL: automatikusan létrehozza az indexeket a kapcsolt mezőkre.
    Mutasd a teljes hozzászólást!
  • Igen, ez volt a tippem, hogy a nemlétező/hiányzó indexeket automatikusan berakja, ahelyett, hogy jajgatna a hiányuk miatt.

    Sajnos (?) én még úgy szocializálódtam, hogy  előbb a szükséges index, utána a constraint :)

    Upd: O.K., az ellenkező irányú indexről megfeledkeztem (a gyerek foreign key mezőjének indexéről)
    Mutasd a teljes hozzászólást!
  • Ez mitől gyorsítana bármit?

    A mysql foreign key esetén mind a hivatkozó, mind a hivatkozott oszlopokra tesz indexet, ha addig nem volt. Épp azért, hogy az ellenőrzést hatékonyan el tudja végezni. *ja, bocs már írták*

    A konkrét példában szerintem a profilemeta táblában nem volt index.

    Ilyen esetekben EXPLAIN tud még sokat segíteni.
    Mutasd a teljes hozzászólást!
  • Igen, meg én is kipróbáltam, hogy bár magának a foreign key-nek nincs köze a gyorsuláshoz, az automatikusan generált indexnek van. :)
    Mutasd a teljes hozzászólást!
  • Én személy szerint nem két táblát hoznék létre, hanem csak 1-et és azon belül módosítanám (ON  DUPLICATE KEY  UPDATE) a last_namet és first_namet.
    Igen kicsi a valószínűsége, hogy egy felhasználó folyamatosan változtatja a nevét.



    Ha meg mégis szükség van a régi adatokra akkor direktbe csinálnék egy

    uid | full_name | first_name | last_name | added
    uid=INT 10 PRIMARY KEY
    full_name= varchar 100 INDEX
    first_name= varchar 50 INDEX
    last_name= varchar 50 INDEX
    added=timestamp ON UPDATE CURRENT_TIMESTAMP


    uid | first_name | last_name | added
    uid=INT 10  INDEX
    first_name= varchar 50 INDEX
    last_name= varchar 50 INDEX
    added=timestamp ON UPDATE CURRENT_TIMESTAMP
    Mutasd a teljes hozzászólást!
  • Hali!

    Én személy szerint nem két táblát hoznék létre, hanem csak 1-et és azon belül módosítanám (ON  DUPLICATE KEY  UPDATE) a last_namet és first_namet.

    Valószínűleg jóval több tulajdonság van (nem csak a vezeték- és keresztnév), amit egy profilhoz tárolni kell/szeretne. Sőt, ha a sejtésem nem csal, akkor akár ez változhat is, tehát előfordulhat, hogy újabb tulajdonságok jönnek be (és ekkor még tábla-változtatás is kellene).

    Mutasd a teljes hozzászólást!
  • ha a sejtésem nem csal, akkor akár ez változhat is, tehát előfordulhat, hogy újabb tulajdonságok jönnek be

    Igen, (sajnos) pontosan így van.



    Nem merem feltölteni a ~100.000 sort a profiles táblába, illetve az ~1.700.000 sort a profilemeta táblába. Félek attól, hogy nagyon lassú lesz a lekérdezés.

    Mutasd a teljes hozzászólást!
  • Ha normálisan van indexelve akkor nem lesz lassú.

    Nekem a www.simplebreed.com -nál a statisztika táblám.

    összesen 3302634, A lekérdezés 0.0011 másodpercig tartott.
    Mutasd a teljes hozzászólást!
  • összesen 3302634

    Az szép kis mennyiség!
    Mutasd a teljes hozzászólást!
  • Korábban javasoltad:

    CREATE INDEX meta_key ON profilemeta (meta_key); CREATE INDEX meta_key ON profilemeta (last_name);

    Ez valóban működhet az alábbi szerkezetnél?

    CREATE TABLE `profilemeta` ( `meta_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `uid` int NOT NULL, `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `meta_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    Nem értek az INDEX-ekhez. Az alábbi nem elegendő?

    ALTER TABLE profilemeta ADD FOREIGN KEY (uid) REFERENCES profiles (uid);

    Lekérdezés (egyelőre):

    SELECT `p2`.* FROM ( SELECT `p`.*, (SELECT `meta_value` FROM `profilemeta` WHERE `meta_key`='last_name' AND `uid`=`p`.`uid` ORDER BY `added` DESC LIMIT 1 ) AS `last_name`, (SELECT `meta_value` FROM `profilemeta` WHERE `meta_key`='last_name' AND `uid`=`p`.`uid` ORDER BY `added` DESC LIMIT 1,1) AS `prev_last_name` FROM `profiles` AS `p` ) `p2` WHERE `p2`.`type_id`='1' ORDER BY cast( `score` as UNSIGNED ) ASC LIMIT 25

    ⊓≡⊺∧⊓ɢ⋿∟ tetszetős lekérdezését (és tábla szerkezeteit) még nem volt erőm feldolgozni.
    Mutasd a teljes hozzászólást!
  • Széljegyzetként annyi még, hogy 1700000 sornál szerintem javasolt a meta_key-re egy külön táblát gyártani "int" elsődleges kulccsal. Gyanítom, hogy száznál nincs többféle jellemződ, vagyis szinte teljesen feleslegesen tárolod a szöveges adatot minden soron. Az indexek is jóval kevesebb helyet foglalnak, és gyorsabbak is, ha varchar(255) helyett simán int-re vonatkoznak. Sokkal egyszerűbb a gépnek két int-et összehasonlítani, mint két stringet.
    Mutasd a teljes hozzászólást!
  • 1700000 sornál szerintem javasolt a meta_key-re egy külön táblát gyártani "int" elsődleges kulccsal

    Köszönöm! Nem gondoltam volna, hogy ez is számíthat. Sokat tanulok a hozzászólásaitokból. Sokkal többet mintha doksikat olvasgatnék, illetve annak is örülök, hogy elvállaltam ezeket a projekteket mert így rá vagyok kényszerülve, hogy megoldjam a felmerülő problémákat. Igaz, nélkületek és a prog.hu nélkül ez nem menne. Sajnos van egy hátránya annak, hogy így tanulok: foghíjasabb a tudásom mintha doksikat tanulmányoznék.

    Most egy kicsit magam alatt vagyok. Sajnos nagyon lassú lett a lekérdezésem. Elkezdtem feltölteni az adatokat. A feltöltés gyors. Egy profil 2 táblába INSERT-el, az egyikbe 1 sort, a másikba 17-et és 1 profil 1-2 másodperc alatt bent van.

    A lekérdezés: minden ezredik profil után 1 másodpercet lassul, tehát most az egyik táblában 20.000 sor van, a másikban 340.000 és így ~20 másodperc az első 25 sor betöltése például (JavaScript Fetch-el).

    ⊓≡⊺∧⊓ɢ⋿∟ lekérdezését majd most este fogom kipróbálni. Olyan jó lenne ha megoldódna vele ez a lassúsági probléma.

    Mutasd a teljes hozzászólást!
  • Ha az indexek nem segítenek, akkor a legnagyobb probléma a LIKE-al lesz.

    Nekem csak 600e soros táblám van (de óránként minden rekorddal történik valami), abban is 0,4 másodperc egy keresés, Ha a LIKE "%"-val kezdődik. Akkor ügye nincs index. De érdemes a fulltext keresést kipróbálnod.

    Én azt tapasztaltam hogy annyira nem szerencsés ha mindent külön táblába szervezek. Nem tudtam utána olyan selectet írni, ami ugyanarra az alapra épül, hanem kénytelen lettem volna minden szűrési feltételnél egy másikat használni.  Így nálam nagyon nincs join. Ellenben vannak összetett indexek, mert ügye nem csak 1 mezőre lehet tenni, hanem tehetsz többre is egy közöst, de akkor úgy is kell felsorolnod a feltételek között.
    Mutasd a teljes hozzászólást!
  • Eddig csak ez volt:

    ALTER TABLE `profilemeta` ADD FOREIGN KEY( `uid` ) REFERENCES `profiles`( `uid` );

    Kb. 52.000 profilnál ~50 másodperc a lekérdezés.

    A `meta_key` kapott egy INDEX-et:

    TABLE `profilemeta` ADD INDEX(`meta_key`);

    A `meta_value` leghosszabb értéke 179 volt:

    SELECT LENGTH( `meta_value` ) AS `l` FROM `profilemeta` ORDER BY `l` DESC;

    Így csináltam a TEXT-ből egy 256-os VARCHAR-t:

    ALTER TABLE `profilemeta` CHANGE `meta_value` `meta_value` VARCHAR(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;

    Majd hozzáadtam egy INDEX-et (mert a TEXT-hez nem engedte):

    ALTER TABLE `profilemeta` ADD INDEX(`meta_value`);

    Talán 5 másodperccel gyorsabb lett így a lekérdezés, de még ez is nagyon-nagyon lassú.

    Tegnap este elaludtam vagy inkább elájultam, úgyhogy majd most próbálok háborúzni egyet ⊓≡⊺∧⊓ɢ⋿∟ lekérdezésével.

    Mutasd a teljes hozzászólást!
  • A legfontosabb lemaradt. Jelenleg LIKE nélkül ilyen lassú a lekérdezésem. Csak WHERE van meg ORDER BY meg egy talicskányi SELECT. Fogok csinálni egy összefoglalót, egy részletes leírást, hogy mik lehetnek a bemenetek a lekérdezésbe és mi kell hogy legyen a kimenet. Ha a megbízóm meglátja a közel 1 perces lekérdezést és még csak a fele adat sincs betöltve, akkor szerintem nekem annyi. Az a szerencsém, hogy messze van, nagyon messze.



    Bár lehet, hogy ez már annyira, de annyira nagyon lassú lekérdezés, hogy csináltatott egy PCR tesztet és már repülőn van... vagy már az ajtóm előtt.

    Mutasd a teljes hozzászólást!
  • A konkrét lassú lekérdezésekre csinálj egy explain-t, és másold ide az eredményt, lássuk, hol van a kutya elásva.
    Mutasd a teljes hozzászólást!
  • csinálj egy explain-t

    Hogyan kell? Még nem csináltam.
    Mutasd a teljes hozzászólást!
Ez a téma lezárásra került a moderátor által.
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd