[MySQL] Querytijd varieert sterk bij specifiek aantal rijen

[egonolieux]

Ik heb een database voor het bijhouden van ruilkaarten. Hierbij heb ik expansion_series waaronder meerdere expansions zitten. De kolom main_series van de expansion_series tabel duidt aan of de serie in kwestie een hoofdserie is (0 of 1).



Als ik met volgende query alle main_series opvraag:

code:

SELECT *
FROM expansion_series
    INNER JOIN expansion ON expansion_series.id = expansion.expansion_series_id
WHERE expansion_series.main_series = 1
ORDER BY expansion_series.release_period_begin DESC

en het aatal rijen is 0 tot 67, dan bedraagt de querytijd slechts +- 2ms.
Raar genoeg wordt deze querytijd +- 100ms voor 68 tot 75 rijen.
Vanaf 75 wordt dit terug +- 2ms.
Als ik alle expansion_series ofwel main_series = 0 of 1 maak, is de querytijd altijd +- 2ms.

Filteren op de primary key waar een index op staat i.p.v. main_series geeft hetzelfde probleem.

Voor alle duidelijkheid ook even de explain:



MySQL versie is 5.6.32 (FreeBSD).

Wat kan hier de oorzaak van zijn? Is dit een bug of zie ik iets over het hoofd?

[ Voor 10% gewijzigd door egonolieux op 23-10-2016 05:09 ]

[DJMaze]

Waar is je main_series index?

[Freeaqingme]

Wat zegt 'explain extended' over alle 3 je queries? Vermoedelijk verwacht de optimizer dat bij 68 tot 75 rijen het gebruik van index X (of juist een full table scan) sneller zal zijn, en doet ie dat bij de andere queries niet.

[egonolieux]

Ik had op main_series inderdaad nog geen index, niet dat het veel uitmaakt aangezien de tabel max 200 rijen gaat bevatten. Met index krijg ik hetzelfde resultaat (had dit reeds eerder getest door op de primary key te filteren i.p.v. main_series met hetzelfde aantal rijen als resultaat).

Hieronder het execution plan voor de eerder vermelde query nu met index op main_series:

code:

id          select_type                             table
--------------------------------------------------------------------------------------
1           SIMPLE                                  expansion_series
1           SIMPLE                                  expansion

type        possible_keys                           key
--------------------------------------------------------------------------------------
ref         PRIMARY, main_series1_idx               main_series1_idx
ref         fk_expansion_expansion_series1_idx      fk_expansion_expansion_series1_idx

key_len     ref                                     rows                
--------------------------------------------------------------------------------------
1           const                                   11                  
4           tcgcollector.expansion_series.id        3                   

filtered    Extra
--------------------------------------------------------------------------------------
100.00      Using where; Using filesort
100.00      NULL

In ieder geval; het resultaat blijft hetzelfde. 67 tot 75 rijen geeft telkens boven de 100ms execution time.

Voor een totaalbeeld te krijgen heb ik ook even een dump gemaakt van de tabellen in kwestie en hun data. Misschien is daar iets in terug te vinden dat ik over het hoofd zie.

http://pastebin.com/CpF0sVtM

[ Voor 6% gewijzigd door egonolieux op 23-10-2016 00:24 ]

[NMe]

Weet je zeker dat die 2ms niet vanwege caching is en die 100ms-tijden optreden wanneer de query cache niet meer voldoet?

[egonolieux]

NMe schreef op zondag 23 oktober 2016 @ 00:53:
Weet je zeker dat die 2ms niet vanwege caching is en die 100ms-tijden optreden wanneer de query cache niet meer voldoet?

Zelf nog niet aan gedacht, maar het zou me verbazen aangezien ik het zeer consistent kan reproduceren bij dat specifiek aantal rijen.

Even SQL_NO_CACHE bij gezet:

code:

SELECT SQL_NO_CACHE *
FROM expansion_series
    INNER JOIN expansion ON expansion_series.id = expansion.expansion_series_id
WHERE expansion_series.main_series = 1
ORDER BY expansion_series.release_period_begin DESC

... en het resultaat blijft onveranderd.

code:

71 rows (0.105 s)

[ Voor 5% gewijzigd door egonolieux op 23-10-2016 01:05 ]

[NMe]

Ja, die zou je daar ook verwachten. Een select zonder cache in de situatie waarin het eerder maar 2ms duurde is interessanter, want als cache de reden is gaat die ook naar 100ms.

[egonolieux]

NMe schreef op zondag 23 oktober 2016 @ 04:45:
Ja, die zou je daar ook verwachten. Een select zonder cache in de situatie waarin het eerder maar 2ms duurde is interessanter, want als cache de reden is gaat die ook naar 100ms.

Oeps, heb nog de vorige output van een andere openstaande tab gekopieerd .
Het resultaat was in ieder geval niet trager (ook iets rond de 2ms).

Ik heb ondertussen al wat verder gezocht en voorlopig lijkt alles er naar te wijzen dat het een netwerk(configuratie) of systeemspecifiek probleem is.

De MySQL installatie in kwestie draait binnen een FreeBSD jail als development omgeving op mijn server (die ik van buitenaf query). Als ik de probleemquery (met 67 - 75 resultaten) uitvoer binnen de jail zelf, krijg ik hier wel normale querytijden (2ms).

Ik heb de database ook eens aangemaakt op mijn Windows installatie, en bij het lokaal opvragen lijken de querytijden ook normaal. Extern moet ik nog eens uittesten. Het komt hem in ieder geval dus op 2 mogelijke oorzaken neer:

• Probleem is specifiek aan de FreeBSD installatie
• Probleem treedt pas op bij queryen van buitenaf (niet localhost)

Maar voor ik verder zoek, ga ik eerst even wat nachtrust opdoen

[ACM]

Dan is het wel raar dat het met meer records weer wel veel sneller wordt. Heb je ook al gekeken naar een call als 'analyze table' (index-stats bijwerken) of 'optimize table' (tabel opnieuw op disk zetten).

Verder werd er al gesuggereerd dat een index op 'main_series' nuttig is, maar je 'filesort' in je resultaten hint naar een potentiele inefficientie aldaar. Wellicht is een gecombineerde index op 'main_series, release_period_begin' (in die volgorde, niet andersom) nog effectief. Dan kan MySQL die records uitlezen via de index en gelijk op de goede volgorde.

Als je trouwens de hoeveelheid output gelijk wilt houden, kan je zoiets doen:

SQL:

select count(*) from (
  select *
  from ...
) as foo

Dan is voor iedere query de hoeveelheid data die over het netwerk moet gelijk; 1 row. Er is natuurlijk wel een kleine kans dat MySQL hier wat kan optimaliseren mbt de hoeveelheid data die gelezen wordt. In dat geval kan je ook nog zoiets proberen:

SQL:

CREATE TEMPORARY TABLE foo AS
  select *
  from ...

Dan introduceer je wel wat extra tijd ivm de administratie rond die tabel, maar ben je alsnog van je netwerkoutput af.

[ Voor 34% gewijzigd door ACM op 23-10-2016 10:08 ]

[JeroenE]

ACM schreef op zondag 23 oktober 2016 @ 10:05:
Dan is het wel raar dat het met meer records weer wel veel sneller wordt.

Misschien iets als Nagle's algorithm in de network stack?

[GlowMouse]

Freeaqingme schreef op zaterdag 22 oktober 2016 @ 14:44:
Wat zegt 'explain extended' over alle 3 je queries? Vermoedelijk verwacht de optimizer dat bij 68 tot 75 rijen het gebruik van index X (of juist een full table scan) sneller zal zijn, en doet ie dat bij de andere queries niet.

Hierop wachten we nog steeds.

Hoe meet je de querytijd?

Wat geeft een profile?

[egonolieux]

Ondertussen even wat verder gezocht en het probleem ligt bij de PDO MySQL driver.
Tot nu toe had ik al mijn querytijden uitgelezen m.b.v de Symfony profiler & Adminer, i.p.v. ze direct met een MySQL client uit te voeren (beetje dom achteraf gezien). Als ik de MySQLi driver gebruik verdwijnt het probleem.

Het execution plan is met PDO als met MySQL client identiek.

ACM schreef op zondag 23 oktober 2016 @ 10:05:
Dan is het wel raar dat het met meer records weer wel veel sneller wordt. Heb je ook al gekeken naar een call als 'analyze table' (index-stats bijwerken) of 'optimize table' (tabel opnieuw op disk zetten).

Verder werd er al gesuggereerd dat een index op 'main_series' nuttig is, maar je 'filesort' in je resultaten hint naar een potentiele inefficientie aldaar. Wellicht is een gecombineerde index op 'main_series, release_period_begin' (in die volgorde, niet andersom) nog effectief. Dan kan MySQL die records uitlezen via de index en gelijk op de goede volgorde.

Als je trouwens de hoeveelheid output gelijk wilt houden, kan je zoiets doen:

SQL:

select count(*) from (
  select *
  from ...
) as foo

Dan is voor iedere query de hoeveelheid data die over het netwerk moet gelijk; 1 row. Er is natuurlijk wel een kleine kans dat MySQL hier wat kan optimaliseren mbt de hoeveelheid data die gelezen wordt. In dat geval kan je ook nog zoiets proberen:

SQL:

CREATE TEMPORARY TABLE foo AS
  select *
  from ...

Dan introduceer je wel wat extra tijd ivm de administratie rond die tabel, maar ben je alsnog van je netwerkoutput af.

De gecombineerde index is geeft geen verandering in het execution plan.
Als ik de output gelijk houdt zoals je suggereerde, verdwijnt het probleem.

In eerste instantie dacht ik dat het probleem misschien lag aan type casting, zoals hier vermeld:
http://stackoverflow.com/a/21338800/2350238. Na het weglaten van de datums uit mijn resultaat lijkt het probleem inderdaad opgelost, maar raar genoeg treed het probleem niet op als ik afzonderlijk alles uit de expansion of expansion_series tabel inclusief datums opvraag. Wat ook raar is, is dat het probleem verdwijnt wanneer ik bij de query bij één van de twee tabellen de datums weglaat.

UPDATE

Ik heb het probleem ondertussen (ongeveer) kunnen reproduceren.

Ik heb een nieuwe database met 2 tabellen aangemaakt die net zoals expansion_series en expansion 2 identiek genaamde datetime velden bezitten, waarbij de ene tabel een foreign key naar de andere heeft.

Terug bij de oorspronkelijke database; na ondertussen wat aan de datums te hebben gesleuteld om wat tests uit te voeren, treedt het probleem niet specifiek meer op tussen de 67 en 75 maar ook bij het gehele resultaat. Als ik echter enkel expansion_series 0 ophaal, verdwijnt het probleem weer. Dit doet me vermoeden dat het misschien te maken heeft met de variatie in de datums.

[ Voor 14% gewijzigd door egonolieux op 27-10-2016 10:47 ]

[DJMaze]

egonolieux schreef op donderdag 27 oktober 2016 @ 10:11:
Dit doet me vermoeden dat het misschien te maken heeft met de variatie in de datums.

Ik gebruik eigenlijk altijd een INTEGER ipv DATETIME als dit mogelijk is, omdat dit veel sneller is dan DATETIME (met name in de WHERE).

[RobIII]

DJMaze schreef op donderdag 27 oktober 2016 @ 11:04:
[...]

Ik gebruik eigenlijk altijd een INTEGER ipv DATETIME als dit mogelijk is, omdat dit veel sneller is dan DATETIME (met name in de WHERE).

Heb je daar (niet-pre-historische) bronnen / bewijs van?
Een DateTime is, DB-technisch, "onderhuids" dus, natuurlijk ook gewoon een int/float(-achtig iets): Gewoon een stapeltje enen en nullen.

[NMe]

RobIII schreef op donderdag 27 oktober 2016 @ 11:13:
[...]

Heb je daar (niet-pre-historische) bronnen / bewijs van?
Een DateTime is, DB-technisch, "onderhuids" dus, natuurlijk ook gewoon een int/float(-achtig iets): Gewoon een stapeltje enen en nullen.

Sterker nog, dat is in het geval van MySQL de reden dat je 2016-02-30 als datum kan invullen en het "gewoon" werkt.

[DJMaze]

RobIII schreef op donderdag 27 oktober 2016 @ 11:13:
Heb je daar (niet-pre-historische) bronnen / bewijs van?
Een DateTime is, DB-technisch, "onderhuids" dus, natuurlijk ook gewoon een int/float(-achtig iets): Gewoon een stapeltje enen en nullen.

Als ik bijvoorbeeld zoek op

code:

2016 = YEAR(datumveld)

of

code:

datumveld >= '2016-01-01 00:00:00' AND datumveld < '2017-01-01 00:00:00'

bleken deze langzamer dan bijvoorbeeld

code:

intveld >= 1451606400 AND intveld < 1483228800

Maar de volgende is dan weer het langzaamste

code:

intveld >= UNIX_TIMESTAMP('2016-01-01 00:00:00') AND intveld < UNIX_TIMESTAMP('2017-01-01 00:00:00')

Vooral zodra je datetime functies gaat aanroepen wordt het een trage bende.
Nadeel is dat je dus niet goed kan zien in de database welke datum/tijd het is.

Ik heb ook direct even gezocht en er blijkt een test online te staan. Deze zou op de huidige DB's nog eens getest kunnen worden.
http://gpshumano.blogs.dr...benchmarking-with-myisam/

Ik doe dit dus niet bij DATE velden, alleen bij DATETIME/TIMESTAMP.
Ik moet er ook vertellen dat ik met tables te maken heb met 1.000.000+ records.

P.S. Nog maar te zwijgen over de timezone instellingen

[ Voor 10% gewijzigd door DJMaze op 27-10-2016 14:25 . Reden: P.S. toegevoegd ]

[RobIII]

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
Als ik bijvoorbeeld zoek op

code:

2016 = YEAR(datumveld)

of

code:

datumveld >= '2016-01-01 00:00:00' AND datumveld < '2017-01-01 00:00:00'

bleken deze langzamer dan bijvoorbeeld

code:

intveld >= 1451606400 AND intveld < 1483228800

Maar de volgende is dan weer het langzaamste

code:

intveld >= UNIX_TIMESTAMP('2016-01-01 00:00:00') AND intveld < UNIX_TIMESTAMP('2017-01-01 00:00:00')

'Langzamer'.... als in 50%? Of 0.03%?

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
Vooral zodra je datetime functies gaat aanroepen wordt het een trage bende.

Again: definieer 'trage bende'.

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
Nadeel is dat je dus niet goed kan zien in de database welke datum/tijd het is.

O.a. ja. En dat je als developer de hele tijd eraan moet denken op allerlei random plaatsen in je code ints om te moeten zetten naar datetimes/timestamps.

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
Ik heb ook direct even gezocht en er blijkt een test online te staan. Deze zou op de huidige DB's nog eens getest kunnen worden.
http://gpshumano.blogs.dr...benchmarking-with-myisam/

Het artikel heeft 't nog over de archaïsche MYISAM engine; da's al 't eerste dat me opvalt. Daarnaast is 't artikel IMHO maar een slap artikeltje met niet eens denderende resultaten om over naar huis te schrijven. En 't eindigt ook nog eens met: "DATETIME alleviates extra responsability/care from the developer. Programmers don’t usually care about this" en maakt 't geen case voor danwel tegen DATETIME...

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
Ik doe dit dus niet bij DATE velden, alleen bij DATETIME/TIMESTAMP.

DATE/DATETIME/TIMESTAMP: alles is gewoon een bult enen-en-nullen hoor. Er zijn i.t.t. een INT veld wel misschien iets-of-wat overhead op, maar dat is IMHO verwaarloosbaar en valt al helemaal weg tegen 't extra werk dat je hebt met je INT's overal.

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
Ik moet er ook vertellen dat ik met tables te maken heb met 1.000.000+ records.

Ik werk nog wel met grotere tabellen hoor (Alhoewel ik het geluk heb met MSSQL te werken).

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
P.S. Nog maar te zwijgen over de timezone instellingen

En dat lossen INTS hoe op
(Daarbij is ook dat artikel (zie ook de comments eronder en de disclaimer erboven) behoorlijk discutabel).

Al met al maak je niet bepaald een sterke case voor INTs

[NMe]

RobIII schreef op donderdag 27 oktober 2016 @ 15:44:
Het artikel heeft 't nog over de archaïsche MYISAM engine; da's al 't eerste dat me opvalt.

Het eerste dat mij opvalt is de datum van 7½ jaar (!!!) geleden. We zitten inmiddels op MySQL 5.7 en er zijn echt enorme stappen gezet op het gebied van performance sinds de beta van 5.4...

En dat lossen INTS hoe op
(Daarbij is ook dat artikel (zie ook de comments eronder en de disclaimer erboven) behoorlijk discutabel).

Niet alleen dat, maar ook alleen al de schrijver. Hij is geen programmeur of DBA maar heeft wel een (rare) perceptie over hoe datetimes in een RDBMS werken. Ik zou liever een bron zien van iemand die wél dagelijks zijn brood verdient aan dit soort spul.

[ Voor 23% gewijzigd door NMe op 27-10-2016 16:38 ]

[emnich]

DJMaze schreef op donderdag 27 oktober 2016 @ 13:51:
[...]

Als ik bijvoorbeeld zoek op

code:

2016 = YEAR(datumveld)

of

code:

datumveld >= '2016-01-01 00:00:00' AND datumveld < '2017-01-01 00:00:00'

bleken deze langzamer dan bijvoorbeeld

code:

intveld >= 1451606400 AND intveld < 1483228800

Maar de volgende is dan weer het langzaamste

code:

intveld >= UNIX_TIMESTAMP('2016-01-01 00:00:00') AND intveld < UNIX_TIMESTAMP('2017-01-01 00:00:00')

Vooral zodra je datetime functies gaat aanroepen wordt het een trage bende.
Nadeel is dat je dus niet goed kan zien in de database welke datum/tijd het is.

Ik heb ook direct even gezocht en er blijkt een test online te staan. Deze zou op de huidige DB's nog eens getest kunnen worden.
http://gpshumano.blogs.dr...benchmarking-with-myisam/

Ik doe dit dus niet bij DATE velden, alleen bij DATETIME/TIMESTAMP.
Ik moet er ook vertellen dat ik met tables te maken heb met 1.000.000+ records.

P.S. Nog maar te zwijgen over de timezone instellingen

Ik heb even een test gedaan 8M records.

Met een int veld:

code:

SELECT SQL_NO_CACHE count(*) FROM intTbl WHERE date<=1483142400 AND date>=1451606400;
+----------+
| count(*) |
+----------+
|   141998 |
+----------+
1 row in set (0.06 sec)

date:

code:

SELECT SQL_NO_CACHE count(*) FROM dateTbl WHERE date<=1483142400 AND date>=1451606400;
+----------+
| count(*) |
+----------+
|   141998 |
+----------+
1 row in set (0.06 sec)

datetime:

code:

SELECT SQL_NO_CACHE count(*) FROM datetimeTbl WHERE date<='2016-12-31 00:00:00' AND date>='2016-01-01 00:00:00';
+----------+
| count(*) |
+----------+
|   144012 |
+----------+
1 row in set (0.06 sec)

Ook zonder index is het resultaat gelijk (8.77s voor datetime n 8.90 voor int en date).

[GlowMouse]

Where-clauses kunnen tegenwoordig door de storage-engine worden afgehandeld. Het resultaat kan dus van de storage engine afhangen. Daarnaast kun je geen index gebruiken als je de YEAR(date)-formulering gebruikt.

[ Voor 30% gewijzigd door GlowMouse op 27-10-2016 18:05 ]

[RobIII]

GlowMouse schreef op donderdag 27 oktober 2016 @ 18:03:
Daarnaast kun je geen index gebruiken als je de YEAR(date)-formulering gebruikt.

Waarom niet?

where x = Year(2016-10-27) ⇒ where x between 2016-01-01 and 2017-01-01
Of, in INTS:
where x = Year(2016-10-27) ⇒ where x between 1451606400 and 1483228800

Lijkt me dat een beetje queryplanner dat prima kan omschrijven?
(Hoewel ik bekend ben met / weet dat 't niet altijd gebeurt, maar dat is vers 2).

NMe schreef op donderdag 27 oktober 2016 @ 16:35:
[...]

Het eerste dat mij opvalt is de datum van 7½ jaar (!!!) geleden. We zitten inmiddels op MySQL 5.7 en er zijn echt enorme stappen gezet op het gebied van performance sinds de beta van 5.4...

Dat ook ja; dat ben ik alleen vergeten vermelden

[ Voor 32% gewijzigd door RobIII op 27-10-2016 18:33 ]

[GlowMouse]

RobIII schreef op donderdag 27 oktober 2016 @ 18:27:
[...]

Waarom niet?

where x = Year(2016-10-27) => where x between 2016-01-01 and 2017-01-01
Of, in INTS:
where x = Year(2016-10-27) => where x between 1451606400 and 1483228800
Lijkt me dat een beetje queryplanner dat prima kan omschrijven?

Laat ik het anders formuleren. MySQL gebruikt in die situatie geen index

De vraag is hoeveel een queryplanner kan herschrijven. Een "WHERE x+1 >= 0" is identiek aan "WHERE x >= -1" bij integers, maar niet bij floats. De vraag is ook hoeveel tijd een queryplanner moet besteden aan het analyseren van functies (inverteerbaar? monotoon?).

[RobIII]

GlowMouse schreef op donderdag 27 oktober 2016 @ 18:33:
Laat ik het anders formuleren. MySQL gebruikt in die situatie geen index

Dan zijn we 't eens

GlowMouse schreef op donderdag 27 oktober 2016 @ 18:33:
De vraag is ook hoeveel tijd een queryplanner moet besteden aan het analyseren van functies (inverteerbaar? monotoon?).

Queryplanners zijn sowieso complexe beesten; en dan hebben we 't nog niet eens gehad over kardinaliteit van indexen en weet-ik-veel waar zo'n ding nog meer rekening mee houdt.

[ Voor 10% gewijzigd door RobIII op 27-10-2016 18:36 ]

[DJMaze]

emnich schreef op donderdag 27 oktober 2016 @ 17:57:
Ook zonder index is het resultaat gelijk (8.77s voor datetime n 8.90 voor int en date).

Zelf ook even een test gedaan met PHP op mijn Samsung 840Pro SSD met een standaard MySQL 5.6.30 (Fedora 23):
Dit houd in dat PHP de queries zelf buffert.

code:

CREATE TABLE test_datetime (
    v_int INT NOT NULL,
    v_datetime DATETIME NOT NULL,
    v_timestamp TIMESTAMP NOT NULL,
    KEY (v_int),
    KEY (v_datetime),
    KEY (v_timestamp)
) ENGINE=InnoDB;

SET time_zone = '+0:00';

SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200;

SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00';

SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00';

Ik heb 98304 records er in gezet.

Zonder een SET time_zone (oftewel gebruik server tijd):

code:

SELECT SQL_NO_CACHE * FROM ...
integer  : 0.0090811252593994 s
datetime : 0.011210918426514 s
timestamp: 0.012892961502075 s

Hieronder allemaal MET een SET time_zone

code:

SELECT SQL_NO_CACHE * FROM ...
integer  : 0.0067379474639893 s
datetime : 0.0082790851593018 s
timestamp: 0.0082089900970459 s

code:

SELECT SQL_NO_CACHE v_int FROM .....
integer  : 0.0036468505859375 s
datetime : 0.0084710121154785 s
timestamp: 0.011408090591431 s

code:

SELECT SQL_NO_CACHE v_datetime FROM .....
integer  : 0.0073649883270264 s
datetime : 0.0052590370178223 s
timestamp: 0.008587121963501 s

code:

SELECT SQL_NO_CACHE v_timestamp FROM .....
integer  : 0.0080900192260742 s
datetime : 0.0092301368713379 s
timestamp: 0.0051760673522949 s

Het maakt op mijn systeem dus wel uit hoe je de queries uitvoert.
MySQL doet iets met de time_zone en date/time velden, maar wat?

[emnich]

DJMaze schreef op donderdag 27 oktober 2016 @ 19:14:
[...]


Zelf ook even een test gedaan met PHP op mijn Samsung 840Pro SSD met een standaard MySQL 5.6.30 (Fedora 23):
Dit houd in dat PHP de queries zelf buffert.

code:

CREATE TABLE test_datetime (
    v_int INT NOT NULL,
    v_datetime DATETIME NOT NULL,
    v_timestamp TIMESTAMP NOT NULL,
    KEY (v_int),
    KEY (v_datetime),
    KEY (v_timestamp)
) ENGINE=InnoDB;

SET time_zone = '+0:00';

SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200;

SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00';

SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00';

Ik heb 98304 records er in gezet.

Zonder een SET time_zone (oftewel gebruik server tijd):

code:

SELECT SQL_NO_CACHE * FROM ...
integer  : 0.0090811252593994 s
datetime : 0.011210918426514 s
timestamp: 0.012892961502075 s

Hieronder allemaal MET een SET time_zone

code:

SELECT SQL_NO_CACHE * FROM ...
integer  : 0.0067379474639893 s
datetime : 0.0082790851593018 s
timestamp: 0.0082089900970459 s

code:

SELECT SQL_NO_CACHE v_int FROM .....
integer  : 0.0036468505859375 s
datetime : 0.0084710121154785 s
timestamp: 0.011408090591431 s

code:

SELECT SQL_NO_CACHE v_datetime FROM .....
integer  : 0.0073649883270264 s
datetime : 0.0052590370178223 s
timestamp: 0.008587121963501 s

code:

SELECT SQL_NO_CACHE v_timestamp FROM .....
integer  : 0.0080900192260742 s
datetime : 0.0092301368713379 s
timestamp: 0.0051760673522949 s

Het maakt op mijn systeem dus wel uit hoe je de queries uitvoert.
MySQL doet iets met de time_zone en date/time velden, maar wat?

Volgens mij time jij in PHP? Doe het nog eens maar dan direct in MYSQL (dus via de command line). Je krijgt dat betrouwbaardere resultaten.

Ik heb mijn test net nogmaals gedaan en met SET time_zone maakt helemaal niets uit voor mijn resultaten. Ik kan me ook niet goed voorstellen waarom de SET time_zone het langzamer zou maken, er is altijd een default time_zone, het enige wat je doet is deze veranderen.

[DJMaze]

emnich schreef op donderdag 27 oktober 2016 @ 19:36:
Volgens mij time jij in PHP? Doe het nog eens maar dan direct in MYSQL (dus via de command line). Je krijgt dat betrouwbaardere resultaten.

code:

MySQL [mysql_tests]> set profiling=1;
MySQL [mysql_tests]> SHOW PROFILES;
+------------+-----------------------------------------------------------------------------------------------------------------------------+
| Duration   | Query                                                                                                                       |
+------------+-----------------------------------------------------------------------------------------------------------------------------+
| 0.01313800 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
| 0.01583800 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.01206050 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                   |
| 0.00794750 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
| 0.01118750 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
| 0.01398275 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
| 0.01215525 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.01572575 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.01527425 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.00908450 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                   |
| 0.00872450 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                   |
| 0.00923600 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                   |
| 0.00413875 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                         |
| 0.00483900 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                         |
| 0.00304000 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                         |
| 0.00853450 | SELECT SQL_NO_CACHE v_timestamp FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.00979050 | SELECT SQL_NO_CACHE v_timestamp FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.00691275 | SELECT SQL_NO_CACHE v_timestamp FROM test_datetime WHERE v_timestamp >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
| 0.00426000 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'    |
| 0.00403250 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'    |
| 0.00512675 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'    |
+------------+-----------------------------------------------------------------------------------------------------------------------------+

Die op regel 9 is wel een uitschieter.
Ik doe ook expres geen COUNT(*) omdat je meestal echt data ophaalt en niet alleen counts.
Daarnaast valt het mij op dat als je alleen de kolom uit de index ophaalt, dit veel sneller is, daarom dan ook nog het volgende geprobeerd, en dan is er bijna geen verschil.

code:

+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Duration   | Query                                                                                                                         |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| 0.01071775 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00' |
| 0.00862250 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00' |
| 0.00739125 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00' |
| 0.00804075 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                            |
| 0.00798625 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                            |
| 0.00894725 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                            |
+------------+-------------------------------------------------------------------------------------------------------------------------------+

En dan de laatste

code:

MySQL [mysql_tests]> SET time_zone = '+0:00';

+------------+---------------------------------------------------------------------------------------------------------------------------+
| Duration   | Query                                                                                                                     |
+------------+---------------------------------------------------------------------------------------------------------------------------+
| 0.01033800 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                 |
| 0.00830825 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                 |
| 0.00975225 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_int >= 1451649600 AND v_int < 1451653200                                 |
| 0.01331425 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00' |
| 0.00937700 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00' |
| 0.01337950 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE v_datetime >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00' |
+------------+---------------------------------------------------------------------------------------------------------------------------+

[ Voor 54% gewijzigd door DJMaze op 27-10-2016 21:07 ]

[DJMaze]

De resultaten uit het eerste blok gesorteerd op snelheid geeft:

code:

int       | 0.00304000 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                         |
datetime  | 0.00403250 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'    |
int       | 0.00413875 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                         |
datetime  | 0.00426000 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'    |
int       | 0.00483900 | SELECT SQL_NO_CACHE v_int FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                         |
datetime  | 0.00512675 | SELECT SQL_NO_CACHE v_datetime FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'    |
timestamp | 0.00691275 | SELECT SQL_NO_CACHE v_timestamp FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
datetime  | 0.00794750 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
timestamp | 0.00853450 | SELECT SQL_NO_CACHE v_timestamp FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
int       | 0.00872450 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                   |
int       | 0.00908450 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                   |
int       | 0.00923600 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                   |
timestamp | 0.00979050 | SELECT SQL_NO_CACHE v_timestamp FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
datetime  | 0.01118750 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
int       | 0.01206050 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= 1451649600 AND v_int < 1451653200                                   |
timestamp | 0.01215525 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
datetime  | 0.01313800 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
datetime  | 0.01398275 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_datetime < '2016-01-01 13:00:00'   |
timestamp | 0.01527425 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
timestamp | 0.01572575 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |
timestamp | 0.01583800 | SELECT SQL_NO_CACHE * FROM test_datetime WHERE  >= '2016-01-01 12:00:00' AND v_timestamp < '2016-01-01 13:00:00' |

[ Voor 6% gewijzigd door DJMaze op 27-10-2016 21:15 ]

[RobIII]

DJMaze schreef op donderdag 27 oktober 2016 @ 20:47:

Maar (behalve een onoverzichtelijke brei waar niet 1,2,3 iets uit af te leiden is*): wat probeer je nu te bewijzen? Je doet N=1 metingen op een relatief kleine dataset met relatief korte querytijden (waardoor afwijkingen (in beide richtingen) behoorlijk overdreven zullen zijn). Doe ditzelfde nog eens met een (vele) grotere set met véél meer metingen en kijk eens of je conclusies dan nog steeds steek houden (en significant genoeg zijn; want dat is de crux). Want, tbh, ik vind de verschillen die ik hier zie niet significant genoeg om DATETIMEs links te laten liggen ten faveure van INTs. En als je dan fatsoenlijk wil meten doe je die queries "random" door elkaar; behalve MySQL's cache (die je, terecht, buiten spel zet met SQL_NO_CACHE) spelen ook andere factoren als je OS en SSD's caches, hoeveel je CPU op dat moment naast MySQL nog te doen had, etc. nog een rol. Door telkens (variatie) A, A, A, B, B, B, C, C, C te meten krijg je (zeer waarschijnlijk) een heel ander beeld dan wanneer je A, C, B, B, A, C, C, B, A... x 1000** meet bijvoorbeeld.

* Maak er eens wat grafiekjes van; dan zie je verschillen veel sneller (en in verhouding).
** *kuch*

[ Voor 32% gewijzigd door RobIII op 27-10-2016 21:34 ]

[DJMaze]

RobIII schreef op donderdag 27 oktober 2016 @ 21:13:
Want, tbh, ik vind de verschillen die ik hier zie niet significant genoeg om DATETIMEs links te laten liggen ten faveure van INTs.

Klopt in dit geval. INT en DATETIME verschillen niet veel en TIMESTAMP hobbelt er achter aan.
De key length bij INT en TIMESTAMP is 4 bytes en bij DATETIME 5 bytes, dus dat maakt ook niet uit.
YEAR() en soortgelijken heeft inderdaad wel een gevolg.

Als ik ooit tijd heb, zal ik er eens een miljoen records in zetten.

[emnich]

RobIII schreef op donderdag 27 oktober 2016 @ 21:13:
[...]

Voor de fun, ik heb alle drie de query's elk 105 keer laten draaien op drie tabellen met elk ~8M records. De data in de records is identiek (op het formaat na dan). Mijn query haalt 4382 records op (tussen 1 en 2 december 2016).

De query's heb ik in batches van 15 laten draaien, dus 15 query's tegelijk in een redelijke willekeurige volgorde. Die 15 batches heb ik 7 keer herhaald (vandaar de 105 keer per query).

Het is uitgevoerd op een gewone PC met vrijwel alles afgesloten maar het is geen dedicated server die niets anders doet dus de resultaten zijn niet volledig betrouwbaar maar geeft wel een goede indicatie.

type	avg	min	max
date	1.83	1.34	2.645
datetime	1.88	1.09	1.88
int	0.39	0.026	0.693

Wat mij betreft toch een heel duidelijk voordeel voor de int. Bij klein gebruik (minder dan 100k records) zal het de moeite niet zijn maar bij grote tabellen toch zeker wel. Zeker ook door een kleinere index van de int tov de datetime.

Indien je alle informatie uit de index kan halen scheelt het helemaal niets maar als de informatie uit de tabel moet komen dan maakt het wel degelijk uit.

Als ik records selecteer voor heel december (68k records) dan is het voor int 21.65s en voor date 25.35s. Het is dus niet zo dat int x maal zo snel is maar het scheelt alsnog.

[edit]Voor de volledigheid, dit is gedaan op MariaDB 10.0.25

[Gomez12]

emnich schreef op donderdag 27 oktober 2016 @ 23:45:
[...]
De query's heb ik in batches van 15 laten draaien, dus 15 query's tegelijk in een redelijke willekeurige volgorde. Die 15 batches heb ik 7 keer herhaald (vandaar de 105 keer per query).

En voor de fun, draai hem nu nog eens... Ik voorspel andere resultaten, want je maakt het heel erg ingewikkeld door 15 willekeurige querys tegelijk te draaien, dan heb je ook nog eens kans op locking etc.
En in feite draai je enkel 7 random scenario's en dat is statistisch nou niet echt indrukwekkend...

Indien je alle informatie uit de index kan halen scheelt het helemaal niets maar als de informatie uit de tabel moet komen dan maakt het wel degelijk uit.

Als de info uit de tabel moet komen, dan moet je ook wel gelijke info tonen. Oftewel er moet dezelfde string uitkomen. Daar vermoed ik namelijk dat het grote verschil zit.
Datetime is onderliggend gewoon een int of een double, alleen gaan er conversie-functie's overheen die het representeren als een datum-waarde en die conversie functies kosten tijd en dat zie je vermoed ik.
Dan kan je wel gaan valsspelen door voor ints die conversie niet te doen in je db en in php te doen maar dat is een beetje jezelf voorliegen.

Maar sowieso, waarom niet gewoon een behoorlijke test doen maar alleen aankomen met totaal niet-representatieve tests?

[emnich]

Gomez12 schreef op vrijdag 28 oktober 2016 @ 00:24:
[...]

En voor de fun, draai hem nu nog eens... Ik voorspel andere resultaten, want je maakt het heel erg ingewikkeld door 15 willekeurige querys tegelijk te draaien, dan heb je ook nog eens kans op locking etc.
En in feite draai je enkel 7 random scenario's en dat is statistisch nou niet echt indrukwekkend...

natuurlijk zijn ze niet tegelijk en worden ze een voor een uitgevoerd. Ze worden alleen in eens geven met een ; er tussen.

[...]

Als de info uit de tabel moet komen, dan moet je ook wel gelijke info tonen. Oftewel er moet dezelfde string uitkomen. Daar vermoed ik namelijk dat het grote verschil zit.
Datetime is onderliggend gewoon een int of een double, alleen gaan er conversie-functie's overheen die het representeren als een datum-waarde en die conversie functies kosten tijd en dat zie je vermoed ik.
Dan kan je wel gaan valsspelen door voor ints die conversie niet te doen in je db en in php te doen maar dat is een beetje jezelf voorliegen.

Denk je nu werkelijk dat het parsen naar een date voor 4k records zo veel tijd kost? Ik zal het morgen nog een keer uitvoeren maar die conversie is echt peanuts

Maar sowieso, waarom niet gewoon een behoorlijke test doen maar alleen aankomen met totaal niet-representatieve tests?

Wat is er niet representatief aan? Het lijkt me een behoorlijke test die een goede indicatie geeft. Maar als je behalve het parsen nog andere aanpassingen wilt zien dan kan ik kijken of ik hem voor jou nog een keer kan draaien.

[DJMaze]

Gomez12 schreef op vrijdag 28 oktober 2016 @ 00:24:
Als de info uit de tabel moet komen, dan moet je ook wel gelijke info tonen. Oftewel er moet dezelfde string uitkomen. Daar vermoed ik namelijk dat het grote verschil zit.
Datetime is onderliggend gewoon een int of een double, alleen gaan er conversie-functie's overheen die het representeren als een datum-waarde en die conversie functies kosten tijd en dat zie je vermoed ik.
Dan kan je wel gaan valsspelen door voor ints die conversie niet te doen in je db en in php te doen maar dat is een beetje jezelf voorliegen.

Als je website localization toestaat (iemand ziet datum/tijd in zijn eigen tijdzone),
dan doe je:

PHP:

$tz_user = new DateTimeZone('Europe/Amsterdam');
$tz_sqlserver = new DateTimeZone('UTC');

// datetime field
foreach ($record) {
    $dt = new DateTime($record['datetime'], $tz_sqlserver);
    $dt->setTimezone($tz_user);
}

// integer field
foreach ($record) {
    $dt = new DateTime('@' . $record['integer']);
    $dt->setTimezone($tz_user);
}

Dit zou in theorie betekenen dat er 2x een conversie optreed bij datetime omdat hij de sql time_zone moet omzetten naar Epoch en vervolgens naar local.
En bij een integer geef je immers al aan dat het Epoch is.
Echter is dit incorrect, een DateTime class werkt anders en is helemaal niet Epoch:

code:

datetime: 0.0035309791564941 s
integer: 0.0091569423675537 s

Maar doe je het ouderwets:

PHP:

date_default_timezone_set('Europe/Amsterdam');
$t = microtime(1);
for ($i = 0; $i < 1000; ++$i) {
    $dt = date('Y-m-d H:i:s', strtotime('2016-01-01 12:00:00 UTC'));
}
echo "datetime: " . (microtime(1) - $t) . " s\n";
$t = microtime(1);
for ($i = 0; $i < 1000; ++$i) {
    $dt = date('Y-m-d H:i:s', 1451606400);
}
echo "integer: " . (microtime(1) - $t) . " s\n";

Dan is de integer versie sneller

code:

datetime: 0.0031061172485352 s
integer: 0.0011310577392578 s

Het maakt dus uit hoe je de waardes gebruikt in je programmeercode en of je buiten de Epoch (1901 - 2038) zone moet zijn.

[emnich]

Gomez12 schreef op vrijdag 28 oktober 2016 @ 00:24:
[...]
Als de info uit de tabel moet komen, dan moet je ook wel gelijke info tonen. Oftewel er moet dezelfde string uitkomen. Daar vermoed ik namelijk dat het grote verschil zit.

code:

|       17 | 0.01198383 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       18 | 0.00930838 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |
|       19 | 0.00833878 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       20 | 0.00832429 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |
|       21 | 0.01150096 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       22 | 0.01031692 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |
|       23 | 0.00802977 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       24 | 0.00804202 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |
|       25 | 0.00773045 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       26 | 0.00881986 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |
|       27 | 0.00979008 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       28 | 0.00845139 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |
|       29 | 0.01326745 | SELECT SQL_NO_CACHE date,test FROM avi_int WHERE date<=1480723199 AND date>=1480546801                 |
|       30 | 0.01020854 | SELECT SQL_NO_CACHE FROM_UNIXTIME(date), test FROM avi_int WHERE date<=1480723199 AND date>=1480546801 |

Het maakt echt geen verschil hoe je de string uiteindelijk parsed. Daarbij wil je het client-side over het algemeen nog wel een keer converteren (bijv naar dd-mm-yyyy) ook al komt het als een datestring uit de db.

Datetime is onderliggend gewoon een int of een double, alleen gaan er conversie-functie's overheen die het representeren als een datum-waarde en die conversie functies kosten tijd en dat zie je vermoed ik.
Dan kan je wel gaan valsspelen door voor ints die conversie niet te doen in je db en in php te doen maar dat is een beetje jezelf voorliegen.

Hier is hoe MySQL het opslaat:

DATE: A three-byte integer packed as YYYY×16×32 + MM×32 + DD
DATETIME encoding for nonfractional part:

1 bit sign (1= non-negative, 0= negative)
17 bits year*13+month (year 0-9999, month 0-12)
5 bits day (0-31)
5 bits hour (0-23)
6 bits minute (0-59)
6 bits second (0-59)
---------------------------
40 bits = 5 bytes

Er vindt dus hoe dan ook toch een conversie plaats, ook bij een date/datetime veld.

Let op, ik pleit er niet voor om altijd maar int te gebruiken ipv datetime. Het ligt, zoals altijd, volledig aan de case waarvoor je het wilt gebruiken. In 95% van de gevallen zal de performance winst nihil zijn en moet je lekker gebruiken wat je wilt. In sommige gevallen kan het wel degelijk handig zijn.

Ik heb wel eens twee datum velden in 3 tinyint velden gepropt met een enorme boost in performance, mede omdat daardoor wel de volledige index in RAM paste. De code werd iets ingewikkelder maar in dit geval was het dat ruim waard.

@mods: misschien moeten deze posts in een nieuw topic want we dwalen wel erg af van het subject.

[DJMaze]

emnich schreef op vrijdag 28 oktober 2016 @ 08:24:
@mods: misschien moeten deze posts in een nieuw topic want we dwalen wel erg af van het subject.

Aan één kant geef ik je gelijk, aan de andere kant staan hier nu wel voorbeelden hoe hij kan timen en wat de invloed is hoe je bepaalde data ophaalt en dat de load op server dat ook bepaalt.

[ Voor 5% gewijzigd door DJMaze op 30-10-2016 22:02 ]