Keskustelu: Nettisivujen teko: Aikojen vertailu MySQL:ssä

Ideana, että poistettaisiin taulusta kaikki yli 2 kk vanhat rivit.

$nyt = time();

DELETE FROM uutiset WHERE $nyt - lisatty => 60 * 60 * 24 * 30 * 2; ei toiminut.

Mysqlin aikaleima-funktiota en löytänyt.

WHERE lisatty + INTERVAL 2 MONTH < NOW()

Ja lisätty on tässä tapauksessa date tyyppinen?

Jep

$poistolause = "DELETE FROM uutiset WHERE lisatty + INTERVAL 2 MONTHS < NOW();";

mysql_query($poistolause, $yhteys);

Ei poista riviä, jonka lisatty on 2006-08-21?

Joo, se on MONTH, eli yksikössä. Kirjotin sen tohon aluks väärin ja kerkesit lukemaan ennen ku korjasin.

ajv kirjoitti:

WHERE lisatty + INTERVAL 2 MONTH < NOW()

Mieluummin muodossa WHERE lisatty + INTERVAL 2 MONTH < '2007-10-24'

Syy: query cache osaa cachettaa kyselyn. Käytettäessä now() functiota, query cache ei toimi. Nämä kyselyt ovat hieman ongelmallisia sillä MySQL saattaa hieman sivuuttaa indexejä näissä myös (joka on OIKEASTI ongelmallista ja tuottaa suorituskyvyn kohdalla harmaita hiuksia ylläpidolle).

Jos luot kyselyn dynaamisesti, niin käytä esim. PHP:n funktiota ottamaan tuo oikea päiväys tuohon lauseeseen. Älä kuitenkaan käytä MySQL:n aikaleima funktiota sillä se todellakin aiheuttaa query cache ongelmia ja sivuutuksia (suomeksi: cachet eivät toimi).

Ihan hyvä pointti tuosta NOW():n käytöstä, mutta onko DELETE-lauseissakin olemassa välimuisti?

ajv kirjoitti:

Ihan hyvä pointti tuosta NOW():n käytöstä, mutta onko DELETE-lauseissakin olemassa välimuisti?

Tietokannan pitää löytää rivi taulusta, se löytyy nopeiten indeksien avulla. Nuo ajat ovat sikäli siis ongelmallisia, että niillä voidaan sivuuttaa jopa indexien käytöt.

Mutta suoranaisesti ei delete käskyillä ole välimuisteja, indexejä nekin tosin käyttävät jos nyt ei ihan kaikkia rivejä tuhota. Jäi tuossa alkuperäisessä tuo DELETE huomaamatta, tosin index säännöt ovat samat select,insert,update,delete käskyillä (yleensä).

-W-

Muutenkin tuntuu turhalta tuo kakkuilu, jos kyselyitä tulee vain yhden kerran samalla arvolla. Siis eihän tuota mitään hyötyä ole ajaa montaa kertaa päivässä.

Grez kirjoitti:

Muutenkin tuntuu turhalta tuo kakkuilu, jos kyselyitä tulee vain yhden kerran samalla arvolla. Siis eihän tuota mitään hyötyä ole ajaa montaa kertaa päivässä.

Jos nyt takerrutaan asiaan, niin välimuistituksen idea on nopeuttaa kyselyitä x ajan. Muuttuja x voi olla sekunti, tunti, päivä, viikko tai mikä tahansa muu aika-arvo. Aika-arvoa voidaan ohjata parametreillä joista yleisin lienee se, että tyhjennetään välimuistia vanhemmasta päästä kun tulee uutta välimuistitettavaa dataa (kyselyitä).

Indexien ja niiden tarkoitus on nopeuttaa kyselyitä olemassa olevasta datasta. Ilman indeksejä jonkin rivin etsiminen tietokannasta voi kestää useita minuutteja jos dataa on riittävästi. Indeksien avulla voidaan kyseinen rivi löytää huomattavasti lyhyemmässä ajassa, jopa sekunneissa (verrattuna minuutteja kestäviin hakuihin).

Ja kuten edellisessä viestissä kirjoitin, en huomannut tuota DELETE osaa originaalissa viestissä. Indeksien käytössä ja niiden kohdalla tämä aika probleema myös pätee, NOW() tyyliset funktiot ovat näppäriä siihen, että ohitetaan tietokannan indeksien käyttö. Kun haettava aika syötetään suoraan stringinä (2007-10-24), niin käytetään indeksejä. Jos se syötetään funktiona, indeksit voivat näppärästi siirtyä sivuun. (Tästä on muuten tehty ihan konkreettisia esimerkkejä esim. Zendin sivuilla).

-W-

No, tietysti jos se tosiaan sivuttaa indeksinkin, niin sitten se on huono. Tuntuu (lievästi sanoen) oudolta tuollainen indeksin sivuuttaminen, mutta eipä ole ensimmäinen käsittämätön ominaisuus MySQL:ssä. Onneksi ei tarvitse mitään vakavampaa yrittää tehdä sillä.

"Oikeilla" tietokannoilla joilla kokeilin ei ole vaikutusta tuleeko WHERE lauseen päivämäärärajaus kiinteällä määrityksellä vai funktiosta.

Saisko jostain faktaa, että MySQL toimii noin. Tuntuu tosiaan hieman järjettömältä, että se sivuuttaisi indexit tuolla tavoin. Ainakaan mitä dokumentaatiota nopeasti katsoin, en löytänyt mainintaa tuosta.

Testasin ja näyttää tosiaan MySQL toimivan kyllä jotenkin hölmösti tuossa tilanteessa. Eli taulussa ~70k riviä ja id indeksinä. Haku, jossa RAND():lla arvottu hakuehto kestää huomattavasti kauemmin, kuin kiinteällä arvolla toteutettu haku. Kummatkin kuitenkin EXPLAINin mukaan kyllä käyttävät indeksiä.

mysql> SELECT id FROM taulu WHERE id = FLOOR( (RAND( ) *70000) );
+-------+
| id    |
+-------+
| 28405 |
+-------+
1 row in set (0.07 sec)

mysql> SELECT id FROM taulu WHERE id = 54321;
+-------+
| id    |
+-------+
| 54321 |
+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM taulu WHERE id = FLOOR( (RAND( ) *70000) );
+----+-------------+------------+-------+--------------- ---------+---------+------+-------+--------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+------------+-------+--------------- ---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | taulu      | index | NULL          | PRIMARY | 4       | NULL | 71622 | Using where; Using index |
+----+-------------+------------+-------+--------------- ---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM taulu WHERE id = 54321;
+----+-------------+------------+-------+--------------- ---------+---------+-------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+------------+-------+--------------- ---------+---------+-------+------+-------------+
|  1 | SIMPLE      | taulu      | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+------------+-------+--------------- ---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

(mod. taulukon kehyksiin välilyöntejä, jotta sivu ei levenisi liikaa)

Asian vierestä ihan, mutta ehkä tällaisissa tapauksissa toi sivun leviäminen ei haittaa. Ois paljon selkeämpi tuo taulukko ku olis ihan luonnollisessa muodossaan. Noihin koodibokseihinhan vois laittaa overflow: auto, niin olis tuokin ongelma poissa.

Minä tärvelin tuon taulukon, mutta sivu vääristyi todella rumasti ja taulukosta toivottavasti näkee edelleen olennaisen asian. Määritys "overflow:auto" ei taida olla täydellinen ratkaisu ongelmaan, jos olen ymmärtänyt sen toiminnan oikein. Tällä hetkellä jos joku kirjoittaa hyvin pitkän rivin, jossa on kuitenkin siellä täällä välilyöntejä, rivi näkyy kunnolla useammalle riville jaettuna, mutta määritystä "overflow:auto" käyttämällä riviä pitäisi vierittää edestakaisin, jos siitä haluaisi saada selvää. Lisäksi joillakin selaimilla tuntuu olevan tapana piirtää vierityspalkki toisinaan virheellisesti liian ylös, jolloin se peittää osittain tai kokonaan koodin. Mutta jos olen tässä asiassa väärässä, minua saa mielellään korjata, ja muutenkin jos on ideoita, miten koodit voisi näyttää nykyistä paremmin, niistä kannattaa kertoa.

Ihan hyvältähän tuo taulukko näyttää nytkin, eli ei haittaa ainakaan minua että pari +-merkkiä on muutettu välilyönneiksi. Ei noin kapea vielä rivity ainakaan millään minun näytöistäni, ellen sitten laita selainta pienempään ikkunaan.

Kokeilitko ajaa tuon kyselyn useampaan kertaan? Ensimmäisellä kerralla on ihan luonnollista, että kestää pidempään jos palvain joutuu lataamaan taulun tai indeksit levyltä.

Jos tuo tosiaan skippaa indeksit, niin sitten ainakin tuo explain kivasti valehtelee. Ehkä ongelma löytyy jostain muualta..

Mitenkäs tuota explainia loppupeleissä luetaan? Vrt. rows - kolumnin 71622 vs 1, ja extran "Using where; Using index" vs "Using index"?

Grez kirjoitti:

Kokeilitko ajaa tuon kyselyn useampaan kertaan? Ensimmäisellä kerralla on ihan luonnollista, että kestää pidempään jos palvain joutuu lataamaan taulun tai indeksit levyltä.

Kokeilin ajaa moneen kertaan ja tuolla RAND():lla haku kestää tasaisesti 0.06s - 0.07s. Kiinteällä arvolla (arvo muutettu jokaisessa haussa) haku kestää 0.0002s - 0.0003s.

Jotain hämärää tuossa on, täytyy katsastaa manuaalista tuon explainin toiminta kuhan kerkee.

Tulee mieleen, että ajaakohan se ton randin jokaiselle riville erikseen.

"rows - kolumnin 71622 vs 1"
Tuo RANDia käyttävä käy taulukon kaikki 71622 riviä läpi (hidasta), ilman RANDia tieto löytyy suoraan indeksistä (yksi rivi).

"Using where; Using index" Tarkoittaa että eka where ehdon täyttävä ja sitten käytetettin indexiä.

Kokeilin ihan huvikseni noita kyselyitä MySQL 6.0.2-alphalla suunilleen samankokoisella taululla.

RANDilla (0.0725 sek)
select_type = SIMPLE
type = ALL
possible_keys, key, key_len, ref = NULL
rows = 70345
Extra = Using where

Joskus tosin optimoija erehty käyttämään lisäksi indexiä, jolloin kysely oli puolet hitaamp. Kun sanoi kyselyssä IGNORE INDEX(PRIMARY) niin tuota ei esiintyny.

Kiinteellä numerolla 0.0010 sek, ja kun käski jättää indexin huomioimatta niin oli yhtä hidas kuin RANDilla.

Lyhyesti sanottuna kyselyn optimoija tekee tuommosessa kyselyssä virheitä, joka näkyy hitautena.

http://dev.mysql.com/doc/refman/5.1/en/query-cache-how.html selviää minkä functioiden käytössä kannattaa olla tarkkana.

Tuossa käyttämässäni MySQL on jännä bugi, sillä tuo RAND kysely palautti joskus useamman rivin joskus taas ei mitään.

Opiskelija kirjoitti:

Tuossa käyttämässäni MySQL on jännä bugi, sillä tuo RAND kysely palautti joskus useamman rivin joskus taas ei mitään.

Testasin vielä tuolla omalla 5.0.45 MySQL:llä ja siinä esiintyy sama bugi. Eli kuten Grez sanoi, niin se taitaa ajaa sen randin jokaiselle riville erikseen ja joskus käy tsäkä, et se rand palauttaa juuri sen id:n arvon jota ollaan tarkistamassa, joskus ei.

Tuo "bugi" on mainittu myös MySQL:n ohjeessa, jos tulkitsen oikein:

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand:

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

Eli periaatteessa tuollainen kysely voi tuottaa tulokseksi vaikka taulun kaikki rivit.

Noimpas se näyttää olevan, ja tuo ei mitään selittyy nollalla kertomisella.

Kun tässä painittiin tuon satunnaisen rivin valinnan kanssa, niin laitanpa mahdollisille tulevaisuuden googlettajille tähän vielä sen MySQL:n tapauksessa "oikean" tavan toteuttaa tuo satunnainen rivi:

SELECT id FROM taulu ORDER BY RAND() LIMIT 0, 1 (0.1499 s)

Tämä tosin käy myös ne 70k riviä läpi jne., eli ei kovin optimaalinen tämäkään.

Kannattaa lukasta vaikkapa http://jan.kneschke.de/projects/mysql/order-by-rand ennenkuin tuota ORDER BY RAND() käyttää.

ajv kirjoitti:

Saisko jostain faktaa, että MySQL toimii noin. Tuntuu tosiaan hieman järjettömältä, että se sivuuttaisi indexit tuolla tavoin. Ainakaan mitä dokumentaatiota nopeasti katsoin, en löytänyt mainintaa tuosta.

Esim. http://devzone.zend.com/public/view osoitteesta löytyy pieni kapea artikkeli asiasta. Taisi olla ZendCon Slides '07 alla muutaman PowerPoint dian "artikkeli" myös.

Mitä tulee muihin tietokantoihin, niin testailin joskus samaa asiaa MS SQL Server ja Oracle servereillä, sama ongelma. Pistetään vain riittävästi taustadataa (tyyliin miljoona riviä +++).

Mitäs tapahtuu jos tekee kutsun tyylillä

SELECT id FROM taulu LIMIT RAND(COUNT(*)), 1

En tosin tiedä COUNT(*) toimivuudesta tuossa kohdin, mutta eiköhän tästä joku saa ideaa kokeilla etiöpäin.