Keskustelu: Nettisivujen teko: [MySQL] rivien poiston tehostus

Moi,

Olen tehnyt järjestelmän, jonne laite syöttää tietoja aikaleimattuina. Tietokannan Distance-taulussa on sarakkeet: id, data, time. Id on laitekohtainen yksilöity id, data on float ja time on aikaleima (pvm ja klo).

Alunperin kysely näytti tältä:

DELETE Distance FROM Distance , Distance AS t2
	WHERE Distance.id = t2.id
	AND ABS(Distance.data) < ABS(t2.data)
	AND DATE(Distance.Time) = DATE(t2.Time)
	AND TIME(Distance.Time) BETWEEN  TIME(t2.Time)-1 AND TIME(t2.Time)+1;

Ja oma yritykseni parantaa:

DELETE Directions FROM Directions JOIN Directions AS t2
ON DATE(Directions.Time) = DATE(t2.Time)
WHERE
        Directions.id = t2.id
	AND ABS(Directions.data) < ABS(t2.data)
	AND TIME(Directions.Time) BETWEEN  TIME(t2.Time)-1 AND TIME(t2.Time)+1;

Mutta molemmat kyselyt kestävät saman n. 25 sekuntia. Taulussa on tällä hetkellä 7k riviä ja niitä tulee noin 3k päivässä lisää.
Laite antaa välillä virheellisesti sekunnin sisään toisen datan ja näistä pitäis valita kauempana rivi, jonka data on kauempana arvosta 0 ja toinen poistaa.

Suosittelisin yrittämään käsitellä virheen niin aikaiseen kuin mahdollista eikä korjaamaan niin myöhään kuin vain voi. Älä insertoi riviä, jos edellisestä insertistä on kulunut liian vähän aikaa.

The Alchemistin kanssa samoilla linjoilla.

Lisäksi mun mielestä aika erikoinen toi delete kysely, koska jos ajatellaan seuraavia aikaleimoja

14.3.2012 17:10:05.03
14.3.2012 17:10:06.00
14.3.2012 17:10:07.03
14.3.2012 17:10:08.00
14.3.2012 17:10:09.03
14.3.2012 17:10:10.00
14.3.2012 17:10:11.03
14.3.2012 17:10:12.00
14.3.2012 17:10:13.03
14.3.2012 17:10:14.00

Niin joka toinen noista poistettaisiin.

Grez kirjoitti:

The Alchemistin kanssa samoilla linjoilla.

Lisäksi mun mielestä aika erikoinen toi delete kysely, koska jos ajatellaan seuraavia aikaleimoja

14.3.2012 17:10:05.03
14.3.2012 17:10:06.00
14.3.2012 17:10:07.03
14.3.2012 17:10:08.00
14.3.2012 17:10:09.03
14.3.2012 17:10:10.00
14.3.2012 17:10:11.03
14.3.2012 17:10:12.00
14.3.2012 17:10:13.03
14.3.2012 17:10:14.00

Niin joka toinen noista poistettaisiin.

Laite toimii etäyhteydellä ja lähettää dataa tekemällä http-pyyntöjä serverille. Aikaleimat ovat siis n. sekunnin tarkkuudella yhteydestä riippuen. Ja tarkoitus oli nimenomaan poistaa noilla esimerkki-aikaleimoilla varustetusta datasta joka toinen, eli ei oikeaa arvoa ja poistaa ns. "haamua-arvo".
Ja kuten molemmat ehdotitte poistaa virheen siellä missä se syntyy, aijommekin poistaa laitteesta tuon virheen. Kiinostaa kuitenkin näin yleensä, että miten tuollainen ison taulun käsittely tehdään järkevästi.

Niin, en tiedä että katsoitko tarkemmin tuota listaa, mutta siinä on yksi arvo per sekunti, ja alkuperäisen viestisi vaatimusmäärittelyssä piti poistaa sellaiset tilanteet että samalle sekunnille oli useampia arvoja. Eli kerroin vaan että mielestäni kyselysi ei vastaa vaatimusmäärittelyä.

Jos kerran kyseessä on kertaluontoinen operaation kun saatte lähetyspään kuntoon, niin onko 25 sekuntia ongelma?

Toisaalta 25 sekuntia 7000 riville kuulostaa irvokkaalta. Olen vähän kahden vaiheilla, että jaksanko alkaa MySQLiin generoimaan testidataa.

Edit: Generoin nyt sitten testidatan. 7002 riviä, joista ensimmäinen kyselysi poisti 1219 riviä 57,5 sekunnissa.

Tämän kun ajoin, niin poisti ensimmäisellä ajokerralla 1201 riviä 2,5 sekunnissa ja toisella ajokerralla 26 riviä 1,7 sekunnissa.

set @plaite=0;
set @ptime='0:0:0';
set @pid = 0;
set @pdata = 0;
delete from distance where id in (
select id from (
select
    IF(abs(@pdata)<abs(data), id, @pid) id,
    (abs(timediff(`time`,@ptime))<=1 AND (@plaite-laiteid)=0) as P,
    (@ptime:=`time`),
    (@plaite:=laiteid),
    (@pid:=id),
    (@pdata:=`data`)
    from distance order by laiteid, `time`) as z WHERE P=1
)

Grez kirjoitti:

Niin, en tiedä että katsoitko tarkemmin tuota listaa, mutta siinä on yksi arvo per sekunti, ja alkuperäisen viestisi vaatimusmäärittelyssä piti poistaa sellaiset tilanteet että samalle sekunnille oli useampia arvoja. Eli kerroin vaan että mielestäni kyselysi ei vastaa vaatimusmäärittelyä.

tkok kirjoitti:

Laite antaa välillä virheellisesti sekunnin sisään toisen datan

Antamassasi esimerkissä laite on antanut sekunnin sisään toisen virheellisen datan.

Kiitos koodivinkistä, se auttoi minut oikeille jäljille tehokkuusajatuksessa!