[MySQL] Select would examine to many records...

[Verwijderd]

Een vaag probleem met een SQL database. Op tweakers vond ik er slechts 2 topics over waarvan deze het meest interessant is: [rml][ SQL] Rare SQL error[/rml]

Ik heb een site gemaakt voor een sportvereniging waarbij ik wedstrijdschema's opsla in een tabel. De database is volledig genormaliseerd, zodat alles slechts 1 keer ingevoerd hoeft te worden. Informatie wordt daardoor door verschillende where statements uit alle tabellen getoverd.

De tabel ziet er als volgt uit:

PHP:

CREATE TABLE `WIS_wedstrijdschema` (
`ID` int(11) NOT NULL auto_increment,
`wed_nr` int(11) NOT NULL default '0',
`c_ID` int(11) NOT NULL default '0',
`team_thuis` int(11) NOT NULL default '0',
`team_uit` int(11) NOT NULL default '0',
`datum` date NOT NULL default '0000-00-00',
`tijd` time default NULL,
`weeknr` int(11) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `wed_nr` (`wed_nr`),
KEY `c_ID` (`c_ID`)
) TYPE=MyISAM

De query ziet er als volgt uit:

PHP:

SELECT
DISTINCT(WIS_wedstrijdschema1.wed_nr),
WIS_wedstrijdschema1.datum as datum_sort,
DATE_FORMAT(WIS_wedstrijdschema1.datum, '%d-%m-%Y') as datum,
TIME_FORMAT(WIS_wedstrijdschema1.tijd, '%H.%i') as tijd,
WIS_wedstrijdschema1.team_thuis as thuisnr,
WIS_wedstrijdschema2.team_uit as uitnr,
WIS_vereniging1.naam as thuisnaam,
WIS_vereniging2.naam as uitnaam,
WIS_vereniging1.zwembad as zwembad,
WIS_vereniging1.locatie as locatie,
WIS_team1.team as teamnrthuis,
WIS_team2.team as teamnruit,
WIS_wedstrijdschema1.weeknr,
WIS_wedstrijdschema1.c_ID,
WIS_competitie.c_code
FROM
WIS_wedstrijdschema WIS_wedstrijdschema1,
WIS_wedstrijdschema WIS_wedstrijdschema2,
WIS_competitie,
WIS_competitieindeling,
WIS_team WIS_team1,
WIS_team WIS_team2,
WIS_vereniging WIS_vereniging1,
WIS_vereniging WIS_vereniging2
WHERE
WIS_vereniging1.ID = WIS_team1.v_ID AND
WIS_vereniging2.ID = WIS_team2.v_ID AND
WIS_competitie.ID = WIS_competitieindeling.c_ID AND
WIS_competitieindeling.t_ID = WIS_team1.ID AND
WIS_wedstrijdschema1.C_ID = WIS_competitieindeling.c_ID AND
WIS_wedstrijdschema1.team_thuis = WIS_team1.ID AND
WIS_wedstrijdschema1.team_uit = WIS_team2.ID AND
WIS_wedstrijdschema2.team_thuis = WIS_team1.ID AND
WIS_wedstrijdschema2.team_uit = WIS_team2.ID AND
WIS_wedstrijdschema1.c_ID = 1
ORDER BY datum_sort ASC, tijd ASC, wed_nr ASC

en de explain van deze query:

code:

table  type  possible_keys  key  key_len  ref  rows  Extra  
WIS_competitie const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort 
WIS_wedstrijdschema1 ref c_ID c_ID 4 const 147   
WIS_team1 eq_ref PRIMARY PRIMARY 4 WIS_wedstrijdschema1.team_thuis 1   
WIS_competitieindeling ALL NULL NULL NULL NULL 94 Using where 
WIS_team2 eq_ref PRIMARY PRIMARY 4 WIS_wedstrijdschema1.team_uit 1   
WIS_wedstrijdschema2 ALL NULL NULL NULL NULL 575 Using where 
WIS_vereniging1 eq_ref PRIMARY PRIMARY 4 WIS_team1.v_ID 1   
WIS_vereniging2 eq_ref PRIMARY PRIMARY 4 WIS_team2.v_ID 1

Nu krijg ik bij het uitvoeren van een query de volgende foutmelding

PHP:

The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

Dit is vreemd, omdat wanneer ik deze query niet voor iedere $c_code (zie query) krijg. Wanneer er niet veel records voldoen aan $c_code geeft ie wel een goed resultaat.

Ook wanneer ik de query op mijn thuis installatie uitvoer werkt het gewoon goed. De querytime bedraagt namelijk 0.251560926437 seconds.

Ik heb dit probleem voorgelegd aan mijn provider. Zij melden dat mijn query te groot is en antwoorden als volgt:

PHP:

Je zou in dit geval de query moeten aanpassen omdat de huidige query de server overmatig zal belasten.

Nu heb ik eigenlijk 2 vragen:

1. Hoe zal ik reageren richting mijn provider? Ik neem hier eigenlijk geen genoegen mee. Ik zit pas een week bij ze maar op deze manier voldoen ze niet aan mijn verwachtingen...

2. Hoe zou ik mijn query kunnen aanpassen / cq instellingen wijzigen / indeces maken zodat de query wel geaccepteerd wordt?

[gorgi_19]

1. Mja, ik zou niet zo reageren naar de hoster; dit kan best een enorme belasting zijn
Kijk eens naar Joins, want je maakt het zo wel zwaar en je vergt een onevenredige belasting. * gorgi_19 geeft in dit geval de hoster gelijk.
Verder kan je overwegen om te denormaliseren, als dat een enorme performance boost oplevert en kijken of alle indexen goed staan.

2. Kijk eens naar JOINS; in de PW-FAQ staat hier een stuk over geschreven Zie ook P&W FAQ - SQL

[ Voor 28% gewijzigd door gorgi_19 op 11-10-2004 18:14 ]

[jan-marten]

In je WHERE-clause staan spaties in de tabel-namen? Huh? Wist niet dat dat mag edit; laat maar ik denk dat ik het al begrijp wat je hier doet.

Je selecteerd data uit 8 tabellen terwijl enkele tabellen slechts wat eigenschappen opvragen. Je zou kunnen overwegen om deze later als losse query's uit te kunnen voeren.

En inderdaad, gebruik een JOIN. Scheelt al een hoop (aangezien je eigenschappen opvraagt zoals teamnaam bij het teamID lijkt mij).

[ Voor 9% gewijzigd door jan-marten op 11-10-2004 18:48 ]

[NMe]

jan-marten schreef op 11 oktober 2004 @ 18:47:
In je WHERE-clause staan spaties in de tabel-namen? Huh? Wist niet dat dat mag edit; laat maar ik denk dat ik het al begrijp wat je hier doet.

Ik zie geen spaties?

jan-marten schreef op 11 oktober 2004 @ 18:47:
Je selecteerd data uit 8 tabellen terwijl enkele tabellen slechts wat eigenschappen opvragen. Je zou kunnen overwegen om deze later als losse query's uit te kunnen voeren.

En inderdaad, gebruik een JOIN. Scheelt al een hoop (aangezien je eigenschappen opvraagt zoals teamnaam bij het teamID lijkt mij).

Als je uit 8 tabellen tegelijkertijd data ophaalt, dan is dat per definitie een JOIN, lijkt me zo. TS doet gewoon een zevendubbele impliciete INNER JOIN.

[Verwijderd]

NMe84 schreef op 11 oktober 2004 @ 18:51:
Als je uit 8 tabellen tegelijkertijd data ophaalt, dan is dat per definitie een JOIN, lijkt
me zo. TS doet gewoon een zevendubbele impliciete INNER JOIN.

Ook een prestatie toch . Joh ik was me er helemaal niet zo van bewust dat een dergelijke query zoveel resources vreet. Op eigen systeem ging het altijd prima. Nu zit ik alleen met het probleem om deze query om te bouwen naar eentje met joins... Mag ik bij deze om hulp vragen ?

[Verwijderd]

NMe84 schreef op 11 oktober 2004 @ 18:51:

Ik zie geen spaties?

Als je uit 8 tabellen tegelijkertijd data ophaalt, dan is dat per definitie een JOIN, lijkt me zo. TS doet gewoon een zevendubbele impliciete INNER JOIN.

Effectief wel ja. Maar je moet alleen even rekening houden met de volgorde. In principe voert hij 7 keer een CROSS JOIN uit. Daarna pas, als het hele geheugen als is volgepompt, gaat er pas naar de conditions gekeken worden.

Als je die conditions in elke join zelf zet, dan houdt MySQL intern een veel kleiner aantal records over dat nagekeken moet worden. Ik zou in geen geval de syntax met de komma gebruiken. Leer jezelf gewoon aan te selecteren uit één tabel, en als het er meerdere moeten zijn, gebruik je voor elke "toe te voegen" tabel een JOIN.

Een opzetje:

SQL:

SELECT
   -- kolommen --
FROM
   tabel1
   INNER JOIN tabel2 ON(
      tabel1.kolom = tabel2.kolom
   )

Leer dit even uit je hoofd. Op deze manier gaat het haast altijd wel goed.

[Verwijderd]

Verwijderd schreef op 11 oktober 2004 @ 19:25:
[...]

Effectief wel ja. Maar je moet alleen even rekening houden met de volgorde. In principe voert hij 7 keer een CROSS JOIN uit. Daarna pas, als het hele geheugen als is volgepompt, gaat er pas naar de conditions gekeken worden.

Als je die conditions in elke join zelf zet, dan houdt MySQL intern een veel kleiner aantal records over dat nagekeken moet worden. Ik zou in geen geval de syntax met de komma gebruiken. Leer jezelf gewoon aan te selecteren uit één tabel, en als het er meerdere moeten zijn, gebruik je voor elke "toe te voegen" tabel een JOIN.

Een opzetje:

SQL:

SELECT
   -- kolommen --
FROM
   tabel1
   INNER JOIN tabel2 ON(
      tabel1.kolom = tabel2.kolom
   )

Leer dit even uit je hoofd. Op deze manier gaat het haast altijd wel goed.

Oké dit principe begrijp ik, alleen hoe ga je om met aliassen? Ik moet nu namelijk verschillende tabellen meerdere keren koppelen.

Ik moet bijvoorbeeld vereniging1 koppelen aan team1, maar team1 moet ik ook aan wedstrijdschema1 koppelen. MySQL geeft dan aan dat team1 geen unieke alias is...

[jvdmeer]

Verwijderd schreef op 11 oktober 2004 @ 19:43:
[...]

Ik moet bijvoorbeeld vereniging1 koppelen aan team1, maar team1 moet ik ook aan wedstrijdschema1 koppelen. MySQL geeft dan aan dat team1 geen unieke alias is...

Net zoals je doet achter de FROM. De alias type je gewoon achter de tabelnaam in je join. Dus:

SQL:

SELECT tabel.veld, alias.veld
  FROM tabel
  JOIN tabel2 alias 
    ON tabel.id=alias.id

Tevens moet je even naar je query kijken, want volgens mij heb je de tabel WIS_wedstrijdschema maar 1 keer nodig i.p.v. 2 keer. En waarom heb jij een koppeling tussen team1 en competitieindeling? Is het niet logischer om de wedstrijdindeling aan een poule te koppelen?

Ik heb bijvoorbeeld de volgende query zelf in gebruik:

PHP:

$sql = 'SELECT v.omschrijving AS veld, r.id AS ronde, r.begintijd AS tijd, tp.omschrijving AS thuis, '
        . '     up.omschrijving AS uit, sc.omschrijving AS scheids, sc.SpeeltPloeg as scPloeg, '
        . '     sc.VoorVereniging as scVereniging, w.veld, w.ronde, w.thuisploeg, w.uitploeg, w.scheids, '
        . '     w.gewijzigd, p.kort, w.poule, w.thuispunten, w.uitpunten'
        . ' FROM `wedstrijden` AS w'
        . ' JOIN `poule` AS p ON w.poule = p.id'
        . ' JOIN `ploegen` AS tp ON w.thuisploeg = tp.id'
        . ' JOIN `ploegen` AS up ON w.uitploeg = up.id'
        . ' JOIN `scheids` AS sc ON w.scheids = sc.id'
        . ' JOIN `ronden` AS r ON w.ronde = r.id'
        . ' JOIN `velden` AS v ON w.veld = v.id'
        .$query;

waarbij de 'WHERE and ORDER' dynamisch worden opgebouwd in $query.

[ Voor 52% gewijzigd door jvdmeer op 11-10-2004 20:08 . Reden: zinslengte onacceptabel ]

[Verwijderd]

Zo net mijn eerste system crash gehad wegens een verkeerde join Dat gaat lekkerrrrrr

[Verwijderd]

Oke ik ben eruit. Het resultaat is als volgt:

PHP:

$sql = mysql_query ("SELECT 
                        " . $table_wedstrijdschema . ".wed_nr,
                        " . $table_wedstrijdschema . ".datum as datum_sort,
                        DATE_FORMAT(" . $table_wedstrijdschema . ".datum, '%d-%m-%Y') as datum,
                        TIME_FORMAT(" . $table_wedstrijdschema . ".tijd, '%H.%i') as tijd,
                        " . $table_wedstrijdschema . ".weeknr,
                        " . $table_competitie . ".c_code,
                        " . $table_team1 . ".team as teamnrthuis,
                        " . $table_team2 . ".team as teamnruit,
                        " . $table_vereniging1 . ".naam as thuisnaam,
                        " . $table_vereniging2 . ".naam as uitnaam,
                        " . $table_vereniging1 . ".zwembad,
                        " . $table_vereniging1 . ".locatie
                        FROM 
                            " . $table_competitie . " LEFT JOIN " . $table_wedstrijdschema . " ON " . $table_competitie . ".ID = " . $table_wedstrijdschema . ".c_ID
                            LEFT JOIN " . $table_team . " " . $table_team1 . " on " . $table_wedstrijdschema . ".team_thuis = " . $table_team1 . ".ID
                            LEFT JOIN " . $table_team . " " . $table_team2 . " on " . $table_wedstrijdschema . ".team_uit = " . $table_team2 . ".ID
                            LEFT JOIN " . $table_vereniging . " " . $table_vereniging1 . " on " . $table_team1 . ".v_ID = " . $table_vereniging1 . ".ID
                            LEFT JOIN " . $table_vereniging . " " . $table_vereniging2 . " on " . $table_team2 . ".v_ID = " . $table_vereniging2 . ".ID
                        WHERE 
                            " . $table_wedstrijdschema . ".c_ID = $c_ID
                        ORDER BY datum_sort ASC, tijd ASC, wed_nr ASC")
                        or die (mysql_error());

Dit resulteert in een query die werkt :-)

Tnx voor een ieder die heeft meegedacht met moi

[ Voor 15% gewijzigd door Verwijderd op 11-10-2004 21:22 ]

[Gert]

Snapt mysql niet dat als je from a, b where a.c = b.c doet dit een inner join moet zijn? Zover ik weet doen de meeste rdbms'en dit intern automagisch omschrijven, sybase heeft volgens mij geen eens een inner join.

[ Voor 5% gewijzigd door Gert op 12-10-2004 11:01 ]