MySQL táblából duplikált sorok törlése több lekérdezéssel
2021-12-23T23:25:23+01:00
2022-01-13T01:22:24+01:00
2022-08-12T06:41:56+02:00
*deleted_23419333
Sziasztok!

Egy korábbi, általam nyitott témánál kaptam ezt a segítséget:

DELETE t1 FROM posts t1 INNER JOIN posts t2 WHERE t1.id < t2.id AND t1.post_id = t2.post_id;

Ez törli a táblából a duplikált sorokat, de sajnos csak nagyon lassan.

Maximum 1.800 másodpercig tudom futtatni ezt a lekérdezést PHP-val, ami kevésnek bizonyul:

$mysqli-> query( "DELETE t1 FROM..." );

Körülbelül 25 ezer sor van, 12.500 sor duplikálva.

Hogyan tudnám többszöri PHP/MySQL lekérdezéssel törölni a duplikált sorokat, tehát nem egy lekérdezéssel?



Egy lekérdezés is jó lenne, ha gyorsabban, legfeljebb 1.800 másodperc alatt lefutna.
Mutasd a teljes hozzászólást!
Eleve PHP (pláne browser+httpd) nem kellene a történetbe, másrészt viszont miért ilyen lassú? Nincsen index ezekre a mezőkre? Vagy a feltétel nélküli JOIN direkt szorzatot eredményez?

Egy alternatíva:

DELETE t1.* FROM posts t1, posts t2 WHERE t1.id < t2.id AND t1.post_id = t2.post_id;

Egy másik:

DELETE FROM posts t1 WHERE EXISTS (SELECT * FROM posts t2 WHERE t1.id < t2.id AND t1.post_id = t2.post_id);
Mutasd a teljes hozzászólást!

  • Azon a linken, ahonnan az eredeti megoldást is nyúltam, van még két másik módszer. Azok nem jöhetnek szóba esetleg?
    Mutasd a teljes hozzászólást!
  • Eleve PHP (pláne browser+httpd) nem kellene a történetbe, másrészt viszont miért ilyen lassú? Nincsen index ezekre a mezőkre? Vagy a feltétel nélküli JOIN direkt szorzatot eredményez?

    Egy alternatíva:

    DELETE t1.* FROM posts t1, posts t2 WHERE t1.id < t2.id AND t1.post_id = t2.post_id;

    Egy másik:

    DELETE FROM posts t1 WHERE EXISTS (SELECT * FROM posts t2 WHERE t1.id < t2.id AND t1.post_id = t2.post_id);
    Mutasd a teljes hozzászólást!
  • Nincsen index ezekre a mezőkre?

    Csak az id-re van PRIMARY KEY (AUTO_INCREMENT).
    Mutasd a teljes hozzászólást!


  • Leírom, hogy mi a helyzet.

    Ezt a projektet úgy vettem át, hogy a programozó lelépett. Se híre, se hamva.

    Működni működött a volt programozó által kreált gépezet, de csak egy ideig, legfeljebb néhány ezer soros táblával.

    Az adatok API-val vannak lekérdezve, 4 óránként cronjob-okkal.

    Egy-egy cronjob xxx-xx.xxx sort eredményez.

    4 óránként újra meg újra betöltődnek az adatok és minden adat bekerül a táblába újra meg újra.

    A lekérdezésnél GROUP BY van a post_id-re, így korábban az se volt gond, ha 500-szor szerepelt ugyanaz a post_id-s sor.

    Azt találtam ki, hogy hagyom így egyelőre a cronjob-okat, de minden 5. órában (a 4. órák után) lefuttatok egy PHP-t, ami törli a duplikált sorokat.

    Mindent törölhettem a táblából, így az első 4. órás futtatás után minden csak egyszer szerepel a táblában, de a második 4. órás futtatásnál már keletkezik duplikáció (1-2 új sort leszámítva szinte minden sor duplikált), így az 5. órában már szeretném törölni a duplikált sorokat, de nem tudom mert nagyon lassú a lekérdezés.
    Mutasd a teljes hozzászólást!
  • Hali!

    Azt találtam ki, hogy hagyom így egyelőre a cronjob-okat, de minden 5. órában (a 4. órák után) lefuttatok egy PHP-t, ami törli a duplikált sorokat.

    Ezt a „PHP-t” szintén cron-ból/-nal futtatod, ugye? Ha nem, miért nem?

    De még egyszerűbb: miért nem raksz egyedi kulcsot a post_id-ra?

    Mutasd a teljes hozzászólást!
  • Ha nincs index a post_id mezőre, az okozhat ilyet, de esetleg érdemes lenne megnézni, hogy az EXPLAIN PLAN mit mond erre a delete-re.
    Nem ismerem a mysql-t, csak tipp, de nem zárnám ki azt sem, hogy a t1.id<t2.id feltétel elég sok sort lefedhet, ha elég hülye a mysql optimalizálója, akár az is okozhat gondot, hogy ez áll elöl a where-ben. (Remélem,  nincs így, de neves cég RDBMS-ében - igaz, jó rég volt - találkoztam már olyannal, hogy sebesség szempontjából  nem volt mindegy a feltételek sorrendje)
    Mutasd a teljes hozzászólást!
  • Ezt a „PHP-t” szintén cron-ból/-nal futtatod, ugye?

    Igen, minden időzítve van, de a megbízó miatt az easycron.com-ot kell használnom.

    A kettő közül melyiket válasszam?

    ALTER TABLE `posts` ADD UNIQUE( `post_id` ); ALTER TABLE `posts` ADD INDEX( `post_id` );

    Mi történik akkor ha a UNIQUE-t választom és mondjuk 16.00-kor lefutnak ezek:

    cj.php?id=1

    $mysqli-> multi_query( "INSERT INTO `posts` ( `post_id` ) VALUES ( 42 ); INSERT INTO `posts` ( `post_id` ) VALUES ( 43 )" );

    cj.php?id=2

    $mysqli-> multi_query( "INSERT INTO `posts` ( `post_id` ) VALUES ( 42 ); INSERT INTO `posts` ( `post_id` ) VALUES ( 44 )" );

    cj.php?id=3

    $mysqli-> multi_query( "INSERT INTO `posts` ( `post_id` ) VALUES ( 42 ); INSERT INTO `posts` ( `post_id` ) VALUES ( 45 )" );

    Ha háromszor is szerepelne mondjuk ugyanabban az időben a 42-es post_id sor beszúrása különböző multi_query lekérdezésekben? Hiba nélkül lefutna? Tehát lenne a táblában 42, 43, 44, 45?

    Nem akarok belenyúlni az elődöm által kreált cj.php fájlba.
    Mutasd a teljes hozzászólást!
  • Tedd rá az indexet (várd is meg, hogy elkészüljön):

    ALTER TABLE `posts` ADD INDEX( `post_id` );
    Futtasd le a DELETE-et:

    DELETE t1 FROM posts t1 INNER JOIN posts t2 WHERE t1.id < t2.id AND t1.post_id = t2.post_id;
    Tedd rá a UNIQUE constraint-et:

    ALTER TABLE `posts` ADD UNIQUE( `post_id` );
    És készítsd fel a PHP scriptet, hogy hibára futhat az insert, ha már van rekord az adott post_id-vel.
    Mutasd a teljes hozzászólást!
  • Bonyolult lenne elmagyaráznom, hogy miért, de sajnos nem lesz jó az UNIQUE. Kell, hogy a táblában szerepeljenek a duplikált sorok (legalábbis egy ideig). Az INDEX-et megpróbálom majd.
    Mutasd a teljes hozzászólást!
  • Hali!

    Bonyolult lenne elmagyaráznom, hogy miért, de sajnos nem lesz jó az UNIQUE.

    Azért, csak próbáld meg. Nem gondolom, hogy annyira bonyolult lenne. Viszont nagyon érdekes lenne (legalábbis, engem nagyon érdekel), hogy milyen feladat lehet az, aminek a végrehajtásához/megoldásához kell, hogy „a táblában szerepeljenek a duplikált sorok (legalábbis egy ideig)”, aztán utána már nem.

    Mutasd a teljes hozzászólást!
  • Az INDEX megoldotta a problémát, a lassúságot. Nem gondoltam, hogy ekkora jelentősége van. Köszönöm.
    Mutasd a teljes hozzászólást!
  • A probléma az, hogy az adatbázis régóta olyan amilyen és ezt az adatbázist több webes alkalmazás is használja. Nem háborúzhatok az alkalmazások fejlesztőivel mert akkor fel is út, le is út. Programozói vizsgán vagy szakdolgozatban ez a duplikációs dolog valószínűleg bukta lenne, de ebben az anyagias világban jelenleg ez az egyetlen út.
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd