[MySQL] Query in Statistics mode

[Depress]

Hallo.

We hebben onlang de mogelijkheid gekregen om ruwe data van onze provider te ontvangen. We zijn dit nu aan het opzetten maar we lopen tegen een probleem aan bij het lezen uit de tabel met de gegevens(Dit zijn niet de hele logs, maar samengevatte gegevens speciaal voor een specifiek doel). In de tabellen zitten rond de 20 miljoen rijen.

Wanneer we de volgende query draaien, dan blijft die in MySQL tussen de 8 en 10 minuten op statistics staan.
Dit is de query:

code:

SELECT
      sum(Impressions) as Imps,
      country
      FROM
      auctions
      WHERE
        userid in (11,45,45...,564) //about 1 million id's; no duplicates.
        AND( PublisherId = 10 OR PublisherId = 12 )
        AND Country = "NL" //wordt niet altijd mee gegeven
        AND Date = "2010-10-04"
      GROUP BY Country

De tabel waar de data uitkomt:

code:

DROP TABLE IF EXISTS `adx_raw_data`.`auctions`;
CREATE TABLE  `adx_raw_data`.`auctions` (
  `UserId` bigint(20) unsigned NOT NULL,
  `PublisherId` int(10) unsigned NOT NULL,
  `Date` date NOT NULL,
  `Country` varchar(2) NOT NULL,
  `Impressions` int(10) unsigned NOT NULL,
  PRIMARY KEY (`Country`,`Date`,`PublisherId`,`UserId`),
  KEY `Index_CombinedPub` (`Date`,`UserId`,`PublisherId`,`Country`),
  KEY `Index_CombinedCountry` (`Date`,`UserId`,`Country`),
  KEY `Index_Date` (`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

De 1 miljoen rijen komen uit de onderstaande tabel, de userid's worden geselecteerd op segmentid, 1 user kan meerdere segmenten hebben en natuurlijk geld dit andersom ook. We kunnen de queries niet combineren omdat wanneer we dit doet de query elke keer de 1 miljoen rijen gaat ophalen voor elke rij die hij controleert.
Table:

code:

DROP TABLE IF EXISTS `adx_raw_data`.`segments`;
CREATE TABLE  `adx_raw_data`.`segments` (
  `SegmentId` int(10) unsigned NOT NULL,
  `UserId` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`SegmentId`,`UserId`) USING BTREE,
  KEY `Index_UserId` (`UserId`),
  KEY `Index_Segment` (`SegmentId`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

We hebben al van alles geprobeerd om dit te versnellen.
Terwijl we dit aan het doen waren hebben we ook dit ontdenkt: Wanneer we het statement "Date = '2010-10-04'" veranderen in "('2010-10-05' >= Date AND Date >= '2010-10-04')" loopt de query minder dan 1 minuut.

De keys zijn ook gecontroleerd, hij gebruikt gewoon keys bij deze query staat bij het explain statement uitgelegd.

Heeft iemand een idee waar het aan kan liggen dat deze query zolang in statistics blijft hangen wanneer we een enkele datum mee geven? En heeft iemand een ander idee dan een subquery in de probleem query te stoppen om niet 1 miljoen userid's te hoeven mee geven aan de probleem query.

[cariolive23]

userid in (11,45,45...,564) //about 1 million id's; no duplicates.

Ga joinen, dat is lichtjaren sneller.

En nog een tip: Gooi alle indexen in de tabel auctions weg en hou alleen de primary key over (wat ook een index is). Ga vervolgens met EXPLAIN achterhalen wat de beste aanpak is voor de indexen, jouw PK bevat al alle kolommen waar je nu nog aparte indexen voor aanmaakt. Met teveel indexen bestaat er een kans dat de database de verkeerde index kiest voor het uitvoeren van de query, hierdoor kan de verkeerde aanpak worden gekozen wat veel tijd kost.

[ Voor 16% gewijzigd door cariolive23 op 05-10-2010 10:53 ]

[NMe]

Hij spreekt over een data provider dus de kans dat hij direct inspraak heeft in het datamodel lijkt me klein. Daarnaast: waarom is volgens jou joinen sneller dan een IN (<set>)? Bij mijn weten is een join even snel als een subquery-variant, en die subquery-variant zou ik in essentie ook in die vorm schrijven.

offtopic:
"Lichtjaar" meet afstand, niet tijd.

[ Voor 7% gewijzigd door NMe op 05-10-2010 11:08 ]

[cariolive23]

NMe schreef op dinsdag 05 oktober 2010 @ 11:07:
Daarnaast: waarom is volgens jou joinen sneller dan een IN (<set>)?

Ervaring.

Bij mijn weten is een join even snel als een subquery-variant, en die subquery-variant zou ik in essentie ook in die vorm schrijven.

Een subquery is een mogelijkheid, maar MySQL is hier geen ster in en je blijft zitten met de IN()-constructie. Je kunt dan nog beter met EXISTS aan de slag gaan, dat zou sneller moeten zijn.

EXPLAIN laat zien hoe de database e.e.a. gaat interpreteren, die kan dus doorslag geven.

offtopic:
"Lichtjaar" meet afstand, niet tijd.

offtopic:
Neem de fiets, leg deze afstand af en meet de tijd

[Depress]

Probleem met de subquery variant is dat deze inderdaad langzamer is dan de set eerst zelf op te halen en dan opnieuw mee te geven.

Tevens is mij niet duidelijk hoe ik de volgende query in een JOIN zou moeten gieten:

code:

SELECT userid FROM segments WHERE segmentid in ( 35114, 55616) group by userid HAVING ( COUNT(userid) > 1 );

Ik ben nu bezig met de indexes nogmaals te doorlopen. We hebben er voor gekozen om de index nu alleen primary te nemen met Date, Publisher, Userid, Country. Dit omdat er puur en enkel de bovenstaande query op zal worden uitgevoerd en deze index altijd gebruikt kan worden(Country komt niet altijd voor).

Maar het feit blijft dus nog steeds dat hij heel lang in statistics blijft hangen bij Date = "2010-10-04". Wat dus zeer vreemd is. Of dit is het resultaat van de 1 miljoen userid's. Wat mij dus terug brengt, hoe krijg ik in vredes naam de bovenstaande query in een join gegiet en geen subquery(Is geprobeerd, maar duurt nog langer dan IN())

[NMe]

cariolive23 schreef op dinsdag 05 oktober 2010 @ 11:21:
[...]

Ervaring.

WHERE x IN (y, z) is in feite niets anders dan een korte vorm van WHERE x = y OR x = z. Hoe wou je dat makkelijker maken voor je CPU om te vergelijken? Ook met EXISTS zul je een vergelijkbare vergelijking () moeten maken dus het gaat er bij mij niet echt in dat een join performanter is dan een IN-query.

Een subquery is een mogelijkheid, maar MySQL is hier geen ster in

MySQL 3 niet nee. MySQL 5 kan het wel prima, dus tenzij je dat statement kan onderbouwen zou ik het maken van dat statement nog eens heroverwegen.

EXPLAIN laat zien hoe de database e.e.a. gaat interpreteren, die kan dus doorslag geven.

Dat sowieso inderdaad.

[storeman]

Depress schreef op dinsdag 05 oktober 2010 @ 11:41:
Maar het feit blijft dus nog steeds dat hij heel lang in statistics blijft hangen bij Date = "2010-10-04". Wat dus zeer vreemd is. Of dit is het resultaat van de 1 miljoen userid's. Wat mij dus terug brengt, hoe krijg ik in vredes naam de bovenstaande query in een join gegiet en geen subquery(Is geprobeerd, maar duurt nog langer dan IN())

Klinkt een beetje als een geheugen probleem. Op het moment dat ie een paar rijen meer pakt kan het zijn dat er niet genoeg geheugen gebruikt mag worden/beschikbaar is om alles daar te houden. Als je gaat swappen ben je weg... Kijk je MySQL config eens na en experimenteer daar eens mee.

[Depress]

storeman schreef op dinsdag 05 oktober 2010 @ 11:52:
[...]

Klinkt een beetje als een geheugen probleem. Op het moment dat ie een paar rijen meer pakt kan het zijn dat er niet genoeg geheugen gebruikt mag worden/beschikbaar is om alles daar te houden. Als je gaat swappen ben je weg... Kijk je MySQL config eens na en experimenteer daar eens mee.

Klinkt logisch, maar is geprobeerd. Het rare is, als ik een tijd spanne pak, van 2-3 dagen, wat dus veel meer rows zijn, wel binnen 1 minuut klaar is.

[cariolive23]

Levert dit de gewenste resultaten op?

SQL:

SELECT
    sum(Impressions) as Imps,
    country
FROM
    auctions
        INNER JOIN segments ON auctions.userid = segments.userid 
WHERE
    PublisherId IN(10, 12 )
AND 
    segmentid in ( 35114, 55616)
AND 
    country = 'NL'
AND 
    Date = '2010-10-04'
GROUP BY 
    country
HAVING 
    COUNT(segments.userid) > 1 ;

En ga met EXPLAIN aan de slag.

[Voutloos]

NMe schreef op dinsdag 05 oktober 2010 @ 11:45:
MySQL 3 niet nee. MySQL 5 kan het wel prima, dus tenzij je dat statement kan onderbouwen zou ik het maken van dat statement nog eens heroverwegen.

In ieder geval <= 5.1 wordt een subquery echt veel te vaak onnodig als dependant subquery verwerkt, terwijl de join in die gevallen wel meestel op een slimme manier verwerkt wordt. IN() met een lijst van constanten gaat overigens wel altijd goed, behalve dat je huidige indexen er niet optimaal voor zijn.

(Let op de 'vaak' en 'meestal'; Ik zeg niet dat joins altijd sneller zijn, je zal het gewoon moeten testen. )

[Depress]

Thanks, het levert de resultaten op, maar is absoluut niet sneller dan het IN() Statement, het uitvoeren duurde 11 minuten.

Ik heb daarna de indexes bekeken met Explain, maar hij lijkt heel moeilijk te kunnen kiezen welke index het moet en kan gebruiken. Ik onderzoek nu de mogelijkheden met FORCE INDEX(). Wat ik me afvraag is of de indexes nog werken door de join, "weet" mysql dat hij de data uit de join ook mee moet nemen in de index. Ik heb nu het gevoel dat hij dat niet doet..

[NMe]

Wat precies is het dat explain je vertelt over je query?

[Depress]

NMe schreef op dinsdag 05 oktober 2010 @ 13:01:
Wat precies is het dat explain je vertelt over je query?

code:

1, 'SIMPLE', 'auctions', 'ALL', 'Index_CombinedPub,Index_CombinedCountry,Index_Date', '', '', '', 18531130, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'segments', 'ref', 'PRIMARY,Index_UserId,Index_Segment', 'Index_UserId', '8', 'adx_raw_data.auctions.UserId', 1, 'Using where'

Hij doorloopt dus alle rijen in de auctions tabel. En in de laatste kolom(Extra) Staat dat hij dus geen index gebruikt.

[cariolive23]

Depress schreef op dinsdag 05 oktober 2010 @ 12:53:
Ik heb daarna de indexes bekeken met Explain,

Indexen kun je niet bekijken met EXPLAIN, EXPLAIN laat het queryplan zien. En wat is dit plan? Dit is voor jou input om te zien waar het fout gaat en wat in theorie beter zou kunnen werken. Vervolgens ga je aan de indexen sleutelen, de ene weggooien een andere toevoegen, etc. etc.

Ik onderzoek nu de mogelijkheden met FORCE INDEX().

Hmmmm, geen aanrader, dit gaat vroeg of laat dan weer fout. Zorg er voor dat je de juiste indexen hebt en dat deze vanzelf worden gevonden door de queryplanner. Een FORCE INDEX is niet meer dan een pleister, het is geen oplossing.

Wat ik me afvraag is of de indexes nog werken door de join, "weet" mysql dat hij de data uit de join ook mee moet nemen in de index. Ik heb nu het gevoel dat hij dat niet doet..

Zo dom is zelfs MySQL niet, ook die gebruikt indexen wanneer er een JOIN in een query staat. Wanneer dat niet zo zou zijn, zouden JOIN's totaal onbruikbaar zijn in MySQL en dat zijn ze niet.

[cariolive23]

In de tabel auctions heb je nu de volgende indexen staan:

PRIMARY KEY (`Country`,`Date`,`PublisherId`,`UserId`),
KEY `Index_CombinedPub` (`Date`,`UserId`,`PublisherId`,`Country`),
KEY `Index_CombinedCountry` (`Date`,`UserId`,`Country`),
KEY `Index_Date` (`Date`)

Gooi deze allemaal weg.

Ga vervolgens één index aanmaken (of een primary key) met daarin éérst de meest unieke data, waarschijnlijk het userid. Vervolgens de iets minder unieke data, de nog iets minder unieke data, etc. Bovenstaande query heeft een index op het userid nodig, zie de JOIN, en wil daar ook mee beginnen.

Wanneer je dat hebt aangepast, ga je nogmaals met EXPLAIN de query uitvoeren.

[NMe]

Depress schreef op dinsdag 05 oktober 2010 @ 13:10:
[...]

code:

1, 'SIMPLE', 'auctions', 'ALL', 'Index_CombinedPub,Index_CombinedCountry,Index_Date', '', '', '', 18531130, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'segments', 'ref', 'PRIMARY,Index_UserId,Index_Segment', 'Index_UserId', '8', 'adx_raw_data.auctions.UserId', 1, 'Using where'

Hij doorloopt dus alle rijen in de auctions tabel. En in de laatste kolom(Extra) Staat dat hij dus geen index gebruikt.

Mja, die filesort en temporary table doen je ongetwijfeld de das om. Komt dat niet omdat je combined indices gebruikt? Dit gaat iets verder dan mijn kennis van SQL-optimalisatie maar ik kan me voorstellen dat het lekkerder zou werken als er aparte indices op de velden zouden staan.

[Depress]

cariolive23 schreef op dinsdag 05 oktober 2010 @ 13:21:
In de tabel auctions heb je nu de volgende indexen staan:

[...]

Gooi deze allemaal weg.

Ga vervolgens één index aanmaken (of een primary key) met daarin éérst de meest unieke data, waarschijnlijk het userid. Vervolgens de iets minder unieke data, de nog iets minder unieke data, etc. Bovenstaande query heeft een index op het userid nodig, zie de JOIN, en wil daar ook mee beginnen.

Wanneer je dat hebt aangepast, ga je nogmaals met EXPLAIN de query uitvoeren.

Ik heb een zelfde table gemaakt met een index zoals jij zegt:

code:

  PRIMARY KEY (`UserId`,`PublisherId`,`Date`,`Country`)

Maar hier krijg ik het zelfde, neemt geen index. en geeft het volgende bij Explain:

code:

1, 'SIMPLE', 'auctions_dump', 'ALL', 'PRIMARY', '', '', '', 1000000, 'Using where'
1, 'SIMPLE', 'segments', 'ref', 'PRIMARY,Index_UserId,Index_Segment', 'Index_UserId', '8', 'adx_raw_data.auctions_dump.UserId', 1, 'Using where'

Hier is het dus zelfs compleet onmogelijk voor mysql om een index te gebruiken. Dit terwijl wel gewoon alle velden in de query overeen komen met de index.
De query:

code:

explain SELECT
    sum(Impressions) as Imps,
    country
FROM
    auctions_dump
    INNER JOIN segments ON auctions_dump.userid = segments.userid
WHERE
    (Date = '2010-09-29')
AND
    PublisherId IN( 523 )
AND
    segmentid IN ( 35114, 55616)
AND
    Country = "NL"
GROUP BY
    country
HAVING
    COUNT(segments.userid) > 1 ;

Er zit dus toch ergens iets in dat niet het juiste neemt uit de indexes. Ik heb nu op elk veld een aparte index gemaakt. Daar neemt de bovenstaande query ipv 1.000.000 rijen nog maar 108.000, hij gebruikt de index met alleen de datum
Als test case heb ik ook een extra index gemaakt met Country, Userid, PublisherId, Date. Deze wordt gebruikt maar levert niet minder rijen op. Tevens is de snelheid nog steeds treurig, aangezien het ruim 2 minuten duurt om alle rijen op te halen. Hierbij heb ik dan nog een country mee gegeven, terwijl dit in veel gevallen niet het geval is.

Tevens ben ik er achter gekomen dat de query niet de juiste resultaten terug geeft. Hij lijkt de having te negeren..

[Wolfboy]

NMe schreef op dinsdag 05 oktober 2010 @ 11:45:
[...]

WHERE x IN (y, z) is in feite niets anders dan een korte vorm van WHERE x = y OR x = z. Hoe wou je dat makkelijker maken voor je CPU om te vergelijken? Ook met EXISTS zul je een vergelijkbare vergelijking () moeten maken dus het gaat er bij mij niet echt in dat een join performanter is dan een IN-query.

Bij veel ID's kan het sneller zijn ja. Aangezien dan het doorsturen van de IDs richting MySQL een grotere bottleneck gaat worden dan een join.

Maar ik weet uit ervaring dat je zeker tot 10.000 items nog steeds beter een IN kan gebruiken dan een JOIN.

Geen idee hoe het met iets als 1e6 zit

[cariolive23]

De GROUP BY was nog niet goed, dit mag het worden:

SQL:

GROUP BY
    segments.userid, 
    country

Waarom MySQL weigert om de userid's in de index van auctions te gebruiken, is mij een raadsel. Dit kan alleen normaal gedrag zijn wanneer het overgrote deel van de id's hetzelfde is, wat ik niet verwacht.

Een index beginnend met country lijkt mij niet heel erg zinvol, of je moet vele tientallen landen in de tabel hebben staan. Wanneer het overgrote deel uit NL komt, ga dit dan vooral niet indexeren.

Ps. Gebruik geen dubbele quotes " rondom de content in SQL, dubbele quotes zijn gereserveerd voor objecten zoals tabel- en kolomnamen. Gebruik enkele quotes ', die zijn daar volgens de standaarden voor bedoelt.

[NMe]

cariolive23 schreef op dinsdag 05 oktober 2010 @ 14:44:
De GROUP BY was nog niet goed, dit mag het worden:

SQL:

GROUP BY
    segments.userid, 
    country

Waarom? Hij select het userid niet?

Ps. Gebruik geen dubbele quotes " rondom de content in SQL, dubbele quotes zijn gereserveerd voor objecten zoals tabel- en kolomnamen. Gebruik enkele quotes ', die zijn daar volgens de standaarden voor bedoelt.

Maakt in MySQL niet uit, MySQL gebruikt zowel enkele als dubbele quotes voor strings en backticks voor identifiers. Voor zover ik weet is het ook geen standaard om dubbele quotes te gebruiken voor identifiers, aangezien bijvoorbeeld T-SQL ook een ander systeem gebruikt d.m.v. brackets.

[cariolive23]

NMe schreef op dinsdag 05 oktober 2010 @ 14:50:
[...]

Maakt in MySQL niet uit, MySQL gebruikt zowel enkele als dubbele quotes voor strings en backticks voor identifiers. Voor zover ik weet is het ook geen standaard om dubbele quotes te gebruiken voor identifiers, aangezien bijvoorbeeld T-SQL ook een ander systeem gebruikt d.m.v. brackets.

Zie de SQL standaarden, ook MySQL kent dit, alleen zul je ANSI_QUOTES even moeten aanzetten in je configuratie. Mocht je de SQL_MODE ANSI gebruiken, dan worden ook de dubbele quotes gebruikt voor objecten. Zie ISO/IEC 9075-2:2003, 5WD-01-Framework-2003-09

[Depress]

Er zijn 220 landen, plus nog wat staten in de USA. Vandaar dat er een index op zit.

Ook met het toegevoegde veld klopt de query niet, dit had ik zelf al geprobeerd. Als ik het parameter getal in de having verhoog naar bijvoorbeeld 3, zou ik geen of minder resultaten moeten krijgen, dit is niet het geval.

@Voutloos:
Als ik een mijn query ombouw tot een query met subquery krijg ik dat ook als ik er explain voor tik, heb je een manier om dit te omzeilen?

[cariolive23]

Zou je eens in normale nederlandse tekst kunnen uitleggen wat voor data je zoekt, welke resultaten je wilt opvragen? Dan vergeten we even bovenstaande query en gaan gewoon kijken hoe jouw eigenlijke vraag is op te stellen in SQL. Wellicht gaat het daar al fout.

Hier nog een poging met een derived table:

SQL:

SELECT
    SUM(Impressions) as Imps,
    country
FROM
    auctions
        INNER JOIN (
            SELECT userid FROM segments WHERE segmentid in ( 35114, 55616) GROUP BY userid HAVING COUNT(segments.userid) > 1
        ) AS seg ON auctions.userid = seg.userid 
WHERE
    PublisherId IN(1, 3 )
AND 
    country = 'NL'
AND 
    Date = '2010-10-04'
GROUP BY
    country ;

[Depress]

In mensen taal:

Ik wil mensen die zijn ingedeeld in een segment(1 of meerdere) terug vinden in de auctions tabel. Van deze wil ik alle impressies hebben opgeteld per land. Welk dus al gegroepeerd zijn per UserId, Country en Publisher, dit om de data te minimaliseren.

Op die manier kan ik per segment en per land vergelijken met het totaal.

Edit, als extra, de explain van je laatste query, die ook al ruim 5 min loopt zonder resultaten te leveren:

code:

1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 67517, ''
1, 'PRIMARY', 'auctions', 'eq_ref', 'PRIMARY,Index_CombinedPub,Index_CombinedCountry,Index_Date', 'PRIMARY', '19', 'const,const,const,seg.userid', 1, 'Using where'
2, 'DERIVED', 'segments', 'range', 'PRIMARY,Index_Segment', 'PRIMARY', '4', '', 1199566, 'Using where; Using index; Using temporary; Using filesort'

[ Voor 41% gewijzigd door Depress op 05-10-2010 15:41 ]

[cariolive23]

Tja, zonder de data te kennen kan ik je niet verder helpen. Het is mij een raadsel waarom er geen indexen worden gebruikt, ook niet daar waar dat extreem logisch zou zijn. Ik heb hier een opzetje gemaakt met dummy data (2x één miljoen records) in PostgreSQL en de index scans vliegen me hier om de oren. Maar dat is wel andere data en dat heeft invloed op het queryplan en dus de performance.

Succes!

[Depress]

PostGre is dan ook niet te vergelijken met MySQL in mijn opinie, gebruiken beide andere engines. Zo was het verschil tussen innodb en myisam binnen MySQL al een wereld van verschil.

Over de data: Het zijn logs, userid's zijn integers van het type BIGINT deze zijn tevens geheel gevuld.

Tevens is 2 miljoen maar 25% van de data. Wellicht dat MySQL denkt dat het sneller is wanneer het gewoon een file scan doet ipv een Index scan bij zulke bestanden.

Mocht ik nog nieuwe bevindingen doen, zal ik deze hier plaatsen.

[cariolive23]

Depress schreef op dinsdag 05 oktober 2010 @ 15:53:
PostGre is dan ook niet te vergelijken met MySQL in mijn opinie, gebruiken beide andere engines.

Het zijn andere producten, heel simpel. Verder heet het PostgreSQL, ook wel Postgres genoemd (wat de oorspronkelijk naam was: Post Ingres, Ingres was een vorig project van Stonebraker).

Zo was het verschil tussen innodb en myisam binnen MySQL al een wereld van verschil.

Heb je dan al gekeken of het met innoDB beter werkt? Ik verwacht van niet, de queryplanner is hetzelfde, dat heeft niets met de wijze van opslag te maken.

Over de data: Het zijn logs, userid's zijn integers van het type BIGINT deze zijn tevens geheel gevuld.

Het datamodel heb ik gezien, maar het gaat om de inhoud. 1 miljoen keer de waarde 1 is compleet zinloos voor een index, 1 miljoen unieke waardes is perfect voor een index. Het gaat dus echt om de data zelf.

Tevens is 2 miljoen maar 25% van de data.

Met bruikbare indexen maakt het niet veel uit of je nu 2 miljoen of 8 miljoen records hebt. Het gaat om de data en de spreiding van deze data binnen de tabellen. Op basis van deze gegevens gaat de database bepalen wat het beste queryplan is en gaat deze uitvoeren. De database moet dus ook wel beschikken over de metadata, heb je al een ANALYZE uitgevoerd?

Wellicht dat MySQL denkt dat het sneller is wanneer het gewoon een file scan doet ipv een Index scan bij zulke bestanden.

Dat zou complete waanzin zijn, iedereen kan zien dat je met veel verschillende userid's in de JOIN een index nodig hebt.

Mocht ik nog nieuwe bevindingen doen, zal ik deze hier plaatsen.

Ik kijk er naar uit!

[Depress]

cariolive23 schreef op dinsdag 05 oktober 2010 @ 16:12:
[...]

Het zijn andere producten, heel simpel. Verder heet het PostgreSQL, ook wel Postgres genoemd (wat de oorspronkelijk naam was: Post Ingres, Ingres was een vorig project van Stonebraker).


[...]

Heb je dan al gekeken of het met innoDB beter werkt? Ik verwacht van niet, de queryplanner is hetzelfde, dat heeft niets met de wijze van opslag te maken.

Hebben innodb als eerste gehad, maar deze kon niet op tegen de snellere selects van myisam op.

[...]

Het datamodel heb ik gezien, maar het gaat om de inhoud. 1 miljoen keer de waarde 1 is compleet zinloos voor een index, 1 miljoen unieke waardes is perfect voor een index. Het gaat dus echt om de data zelf.

Segements bevat enkel unieke rijen, zie ook de primary index.
Auctions bevat tevens ook allemaal unieke rijen. Impressions telt hoevaak er een zelfde key is geweest als het ware.

[...]

Met bruikbare indexen maakt het niet veel uit of je nu 2 miljoen of 8 miljoen records hebt. Het gaat om de data en de spreiding van deze data binnen de tabellen. Op basis van deze gegevens gaat de database bepalen wat het beste queryplan is en gaat deze uitvoeren. De database moet dus ook wel beschikken over de metadata, heb je al een ANALYZE uitgevoerd?

As we speak loopt de analyze, ik hoop dat ik met de uitkomst weer wat kan uitsluiten.

[...]

Dat zou complete waanzin zijn, iedereen kan zien dat je met veel verschillende userid's in de JOIN een index nodig hebt.


[...]

Ik kijk er naar uit!

Ik heb inmiddels uitgevonden dat door de hoeveelheid data de uitvoerings tijd toeneemt. 6 uur data(vandaag) is 25 seconden. 1 dag is 50 seconden, 3 dagen is meer dan 20 minuten. Echter bij 3 dagen is de query aangepast, maar in de explain veranderd niets noemens waardig, nog steeds de zelfde indexes en alleen een toegenomen row count, wat logisch is omdat er meer data voldoet aan de criteria.

[ Voor 10% gewijzigd door Depress op 05-10-2010 16:36 ]

[GlowMouse]

NMe schreef op dinsdag 05 oktober 2010 @ 11:45:
[...]

WHERE x IN (y, z) is in feite niets anders dan een korte vorm van WHERE x = y OR x = z. Hoe wou je dat makkelijker maken voor je CPU om te vergelijken? Ook met EXISTS zul je een vergelijkbare vergelijking () moeten maken dus het gaat er bij mij niet echt in dat een join performanter is dan een IN-query.

Een JOIN kan gebruikmaken van een index. De verwachte tijd voor het uitvoeren van IN neemt lineair toe, de verwachte tijd voor het uitvoeren van een JOIN logaritmisch.


Voor de query in de OP zal een index op (date,country) wel werken. Dat kun je al bereiken door de eerste twee kolommen in je primary key om te wisselen. Dan heb je gelijk de gebruikte rijen veelal in dezelfde page staan.

Voor de join op segmentid zul je een index op (user,segment) willen hebben. Wissel dus je primary key ook daar om. Als je al een index op (user,segment) hebt, is een aparte index op user niet nodig.

[ Voor 14% gewijzigd door GlowMouse op 12-10-2010 14:55 . Reden: MyISAM 8)7 ]

[Depress]

De key is inderdaad nu gewisseld en veranderd in een enkele primary key met de volgende key's:
Date, UserId, Publisher, Country. De query wordt nu ook binnen afzienbare tijd uitgevoerd en is er van 2 queries een enkele query gemaakt door middel van een Join.

De query ziet er nu als volgt uit, netheid zal wel tegenvallen maar performance is goed.

code:

SELECT
      sum(a.Impressions) as Imps,
      a.country
      FROM
      auctions a, segments s1, segments s2, segments s3
      WHERE
      a.UserId = s1.UserId
  AND a.publisherId = 523
  AND a.Country= 'NL'
  AND date='2010-10-04'
      AND s1.segmentid = 35114
    AND (s2.segmentid = 39994 AND s2.userid = s1.userid)
    AND ((s3.segmentid = 55616 AND s3.userid = s2.userid)
    OR( s3.segmentid = 55618 AND s3.userid = s2.userid)
    OR( s3.segmentid = 55620 AND s3.userid = s2.userid));

**Query kan afwijken van daadwerkelijke query omdat de huidige query dynamisch wordt opgebouwd en dit de enige nog overzijnde draft is.

[GlowMouse]

Zonder GROUP BY zal het opeens wel sneller zijn ja, ondanks je slechte PK volgorde. Toch zal ook deze query sneller zijn met Date, Country, Publisher, UserId.

[ Voor 5% gewijzigd door GlowMouse op 12-10-2010 14:01 ]