Een serie sql queries, hoe te versnellen / vereenvoudigen

  • Onderwerp starter Onderwerp starter JDVB
  • Startdatum Startdatum
Status
Niet open voor verdere reacties.

JDVB

Gebruiker
Lid geworden
20 aug 2003
Berichten
89
Hoi,

Ik wil mijn website voor me laten bepalen wat er in een nieuwsbrief moet komen.
De criteria zijn hiervoor:
Het artikel moet recenter zijn dan 'datum'
Het artikel mag niet eerder in een nieuwsbrief gemailed zijn.
Het artikel mag niet op 'niet mailen' lijst staan
De producent of coproducent moeten op een lijst staan waarop de bezoeker kan filteren wat er gemailed moet worden.

Dit zijn 4 filters die ik over een artikelenlijst van ~8000 records wil gooien, de queries:
Code:
Query													aantal resultaten	benodigde tijd

select products_id from products where products_date_added > (SELECT date from lijst where id = '$nieuwsbrief')			2672	0.0020
SELECT date from lijst where id = '$nieuwsbrief'										1	0.0006
select man_id from manufacturers_mail where lijst = '$nieuwsbrief'								218	0.0027
select pid from products_ignore where lijst in ('0', '$nieuwsbrief')								913	0.0005
select distinct np.pid from newsletters_pid np left join newsletters n on (n.id = np.id) where n.lijst = '$nieuwsbrief'		1016	0.0004

select products_id from products where products_date_added > (
								SELECT date from lijst where id = '$nieuwsbrief'
								) and (producent in (
								select man_id from manufacturers_mail where lijst = '$nieuwsbrief'
								) or coprodcent in (
								select man_id from manufacturers_mail where lijst = '$nieuwsbrief'
								)) and products_id not in (
								select pid from products_ignore where lijst in ('0', '$nieuwsbrief')
								) and products_id not in (
								select distinct np.pid from newsletters_pid np left join newsletters n on (n.id = np.id) where n.lijst = '$nieuwsbrief'
								)								82	1.2238
Deze laatste query loopt alleen wat langzaam, hoe kan ik deze versnellen? Met left joins en condities als where np.pid is null etc loopt de query ruim 3,5 seconde. De optimale oplossing zou de minus operator zijn, echter wordt deze niet ondersteund in mysql.
Wat zou optimaal zijn?
Vier afzonderlijke queries uitvoeren en binnen php deze van elkaar scheiden?
Code:
$negeer --> select pid from products_ignore where lijst in ('0', '$nieuwsbrief')
$negeer --> select distinct np.pid from newsletters_pid np left join newsletters n on (n.id = np.id) where n.lijst = '$nieuwsbrief'
$moet --> select man_id from manufacturers_mail where lijst = '$nieuwsbrief'

while($totaal --> select products_id, producent, coproducent from products where products_date_added > (SELECT date from lijst where id = '$nieuwsbrief')){
	if($totaal['products_id'] !in_array($negeer) && ($totaal['productent'] in_array($moet) || $totaal['coproducent'] in array($moet)) {
		$mailen[] = $totaal['products_id'];
	}
}
Maar dat lijkt me wat inefficient, hoe zou dit beter kunnen?
 
Naast bovenstaand vraagstuk heb ik nog een andere vraag.
Om een lijstje met artikelen te genereren moet ik meer gegevens hebben dan de bovenstaande, nu heb ik een probleem met een left join die nogal langzaam wordt.
De waarde waarop ik join is in de eerste tabel een niet unieke index, in de tweede een primary key.
Code:
SELECT p.products_id, p.products_model, p.Beschikbaarheid, pd.products_name, a.adatum, a.mdatum
FROM products p
LEFT JOIN products_description pd ON ( p.[U]products_id[/U] = pd.[U]products_id[/U] )
LEFT JOIN artikel a ON ( a.[U]primarykey[/U] = p.[U]nummer[/U] )
WHERE p.products_date_added > (
SELECT date
FROM lijst
WHERE id = '$nieuwsbrief' )
AND pd.[U]language_id[/U] = '1'
ORDER BY a.[U]adatum[/U]
Deze query loopt 187.6519 seconden, ofwel meer dan drie minuten. Ik snap niet waarom omdat de naar mijn idee goede velden indexen zijn aangemaakt. (onderstreept is index)

De onderstaande query loopt veel sneller:
Code:
SELECT p.products_id, p.products_model, p.Beschikbaarheid, pd.products_name
from products p left join 
products_description pd on (p.products_id = pd.products_id) where p.products_date_added > (SELECT date from lijst where id = '$nieuwsbrief') and pd.language_id = '1'
0.0033 seconden.
 
Laatst bewerkt:
Ik vind dat lastig beoordelen, maar waar wordt elke variabele bepaald?

Waarom is er een "niet mailen lijst"? is die per gebruiker? zo niet kan dit een kolom in de artikel tabel zijn. Hetzelfde geld voor de datum.

De volgorde van de queries is ook belangrijk, omdat de juiste volgorde de dataset flink kan verkleinen. Als je LEFT joins will gebruiken, bedenk dan dat je de left joins in een enkele query kan zetten en dat kan de snelheid ook flink verhogen.

[sql]
SELECT * FROM items LEFT JOIN ignorelijst ON ignorelijst.id=items.id LEFT JOIN maillist ON maillist.id = items.id WHERE ignorelijst.id IS NULL AND maillist.id IS NULL
[/sql]

Eventueel kun je producent en coproducent ook nog joinen in die query (producent not null or coproducent not null) en dan hen je in een enkele select al de meeste exclusions gedaan.
 
De niet mailen lijst bestaat voornamelijk uit oude artikelen die wel recent op het internet gezet zijn.
De keuze om een artikel niet te mailen kan per nieuwsbrief of alle direct voor alle nieuwsbrieven gemaakt worden, vandaar dat dit een aparte tabel is.
Vandaar ook dat het filter hiervoor 'where lijst in (0, $nieuwsbrief)' twee opties heeft. Als 0 dan naar geen enkele lijst, als lijstnummer dan alleen niet naar die ene lijst.

Ook de datum is per nieuwsbrief verschillend.
Helaas zijn de joins ook iets complexer, omdat nieuwsbrieven bijvoorbeeld over twee tabelen heen staan opgeslagen:
Een tabel voor de titel (onderwerp), de nieuwsbrief (waar gebruikers uit kunnen kiezen), een volgnummer, een aanmaakdatum, een verzenddatum.

Daarnaast een tabel om artikelen in op te slaan, per nieuwsbrief:
Nieuwsbriefvolgnummer, artikelnummer, artikeltext


Om met left joins direct te werken moet ik dus voor het uitsluiten van de reeds verzonden artikelen 2 left joins doen
Om de te negeren artikelen uit te sluiten de derde
om op producent te filteren de vierde
om op coproducent te filteren de vijfde

Dit werkt wel, maar duurt 3,5 seconden helaas (langer dus dan bovenstaande). Op dit moment heb ik de queries gesplitst waardoor ik de totale pagina nu in ongeveer 1,5-2 seconden kan genereren. (al sneller dan het was) maar omdat alle oude artikelen die wel op voorraad zijn nu langzaamaan ook online komen wordt de te negeren artikelen tabel steeds zwaarder.
Omdat er steeds meer artikelen op komen, wordt de artikel tabel zwaarder.
Omdat er meer nieuwsbrieven verzonden worden worden ook die tabellen zwaarder.

En helaas kan ik niet heel eenvoudig zeggen dat ik de initiële request waarbij ik op aanmaakdatum filter kan verkrappen om direct het aantal resultaten drastisch in te perken. Ik mail namelijk pas wanneer een artikel leverbaar is en soms duurt dat nog jaren nadat het al wel aangekondigd is en online verschijnt.

En de query uit post #2 die de aanvulling is op de eerste om mij goed te kunnen laten kiezen, is ook niet al te snel wanneer ik deze left join. De laatste tabel die ik hier joinen wil is dan wel een wat grotere van 5MB.
 
Laatst bewerkt:
Ik denk dat er dan niet veel mogelijkheden om het echt te verkorten tenzij je de database opnieuw gaat normaliseren of een aparte tabel maakt die je vult met nieuwe artikelen en alleen uit deze ID's select voor toekomstige nieuwsbrieven. Zodra een artikel is verzonden verplaats je het naar een backup tabel.

Toch heb je wel gelijk over de performance. Sommige queries lijken wel lang te duren. Ik kan dat echter niet zo direct verklaren. Mogelijk loop je soms tegen een hardware beperking aan?
 
Ik denk dat er dan niet veel mogelijkheden om het echt te verkorten tenzij je de database opnieuw gaat normaliseren of een aparte tabel maakt die je vult met nieuwe artikelen en alleen uit deze ID's select voor toekomstige nieuwsbrieven.
Dat is op zich wel een idee, alle nieuwe artikelen in een tabel, met hierbij een kolom per nieuwsbrief, als artikel in die tabel en bij de kolom voor de nieuwsbrief een 1 dan mailen, als 0 dan niet. o.i.d.

Nadeel is dan natuurlijk wel dat ik niet meer een real time verandering kan doorvoeren in of een uitgever wel of niet gemaild moet worden. Hiervoor zou dan namelijk een nogal complexe query uitgevoerd moeten worden om te controleren of en zo ja welke artikelen in de nog_te_mailen tabel bijgevoegd zouden moeten worden, zonder hierbij reeds verzonden artikelen nogmaals te willen zenden. Al zou ik dan geen optie meer hebben om de te negeren artikelen daaruit weg te filteren, wat dan toch ook weer nadelen heeft.
Ik ga dit idee nog eens een nachtje laten rusten.
 
Status
Niet open voor verdere reacties.
Terug
Bovenaan Onderaan