[(My)SQL] Complexe join query probleem

[Verwijderd]

Ik ben bezig met een website waar een database (MySQL) met telefoons en abonnementen achter zit. De abonnementen zijn afkomstig van verschillende datafeeds, de tabel bevat nu rond de 30.000 abonnementen maar dat worden er makkelijk 100.000 als alle feeds in gebruik genomen zijn.
Een aparte tabel met telefoons bevat per telefoon verschillende informatie.

Nu wil ik een query maken die de 5 meest populaire telefoons selecteerd (een integer veld geeft de populariteit van de telefoon aan) en daarbij uit de abonnement tabel een abonnement koppelt aan de telefoon. Het betreft abonnementen + telefoon combinaties die in de abonnementen tabel staan.

Telefoon tabel

phone_id (int, auto_incr)
populariteit enum(1,2,3,4,5)
merk (varchar)
model (varchar)
En nog meer velden die niet van toepassing zijn op mijn vraag

Abonnement tabel
sub_id (int auto_incr)
merk (varchar)
model(varchar)
en meer velden die niet van toepassing zijn.

Nu is het probleem dat niet iedere telefoon ook een abonnement heeft dus moet er eerst gekeken worden of de betreffende telefoon wel of niet een abonnement heeft. Ik heb de volgende query (ik haal nu niet alle velden op die ik normaal gesproken zou willen ophalen) die natuurlijk niet goed werkt, maar ik zou niet weten hoe ik het probleem oplos:

code:

SELECT phones.merk, phones.model, sub.sub_id
FROM phones
INNER JOIN abonnementen as sub
ON (phones.merk = sub.merk AND phones.model = sub.model)
ORDER by phones.populariteit DESC
LIMIT 5

Dit werkt niet natuurlijk omdat er voor iedere telefoon (totaal 1450 telefoon) gekeken moet worden of er een abonnement is. Er moet dus 1450 x 30.000 gekeken worden naar een match met deze query. Dat is alles behalve efficiënt, ik weet dat er een oplossing moet zijn maar het is me niet gelukt deze te vinden. Is er iemand die een antwoord heeft? Kan me voorstellen dat mijn verhaal enigzins onduidelijk is, als er verheldering nodig is hoor ik het graag.

Ik zelf had het idee dat ik per telefoon kijk of er een abonnement is en als er 5 combinaties zijn gevonden stopt de query. De telefoons moeten nog steeds op populariteit zijn gesorteerd.

[Voutloos]

Ten eerste: 1x joinen maakt nog geen complexe join query.

Ten tweede: datamodel, datatypes en indexen. Met een goede index stelt die query echt niets meer voor.

[Verwijderd]

Voutloos schreef op dinsdag 20 februari 2007 @ 15:42:
Ten eerste: 1x joinen maakt nog geen complexe join query.

Ten tweede: datamodel, datatypes en indexen. Met een goede index stelt die query echt niets meer voor.

Dus de velden merk en model index maken?

[cyberstalker]

Verwijderd schreef op dinsdag 20 februari 2007 @ 15:45:
[...]

Dus de velden merk en model index maken?

Ik zou die velden vervangen door een foreign key. Die telefoontabel heeft een phone_id. Als je bij het abonnement de phone_id opslaat van de betreffende telefoon in de telefoontabel, kun je daarop joinen.

Het veld met de foreign key naar de telefoontabel indexeer je dan idd.

[momania]

Verwijderd schreef op dinsdag 20 februari 2007 @ 15:45:
[...]

Dus de velden merk en model index maken?

Uhm ik zou eerst eens scheiden naar Merk - Model - Abonnement tabellen. Nu sla je nogal veel dubbele data op en join je op varchars.

Merk
id
label

Model
id
merk_id
label

Abonnement
id
model_id

etc..

[Voutloos]

De echte oplossing is een foreign key. En dan op de relevante kolommen een index.
Wellicht is een index op populariteit voor deze query ook heel effectief, omdat dankzij de limit meteen gestopt kan worden na de 5 populairste mobieltjes uitgezocht te hebben.

Test en vind uit. Voeg beide indexen toe en bestudeer de explain van je populairste queries.

Overigens zou ik ook een tabel Merken maken, met (id, merknaam), scheelt redundantie en kans op inconsistentie. Zie normalisatie/datamodel tutorials voor uitgebreidere onderbouwing.

edit:
en zie mensen boven mij.

[ Voor 3% gewijzigd door Voutloos op 20-02-2007 15:53 ]

[Verwijderd]

Ok bedankt voor de tips iedereen, ik denk dat ik er nu wel uit komt

[Verwijderd]

Ik zit met nog een probleem. Ik heb jullie adviezen opgevolgd en het werkt inderdaad een stuk sneller nu. Hetvolgende probleem is dat ik nu 6 populaire telefoons wil selecteren (random) en daarbij het abonnement met de laagste telefoonprijs wil verkrijgen. Ik weet alleen niet hoe ik tegelijkertijd een random telefoon kan selecteren en dan de rijen nog kan sorteren op goedkoopste telefoon. Als ik order by rand() komen er willekeurige combinaties tevoorschijn (natuurlijk). Ik kan dit makkelijk oplossen door gewoon 2 queries achterelkaar te doen maar ik denk dat dit ook in een query moet kunnen.

Stel tussen een van de 6 telefoons die ik selecteer komt een telefoon met het telefoon ID 100
ik join dan de abonnementen tabel op telefoon ID. Er zijn echter meerdere abonnementen met telefoon id 100. Ik wil dat de goedkoopste optie gekozen wordt en niet een willekeurige.

ik heb nu de volgende query als basis:

code:

SELECT p.merk, p.model,s.tel_price, s.s_name,s.provider,s.tel_price
FROM phones p
INNER JOIN subscriptions s ON (p.phone_id = s.phone_id)
GROUP BY p.phone_id
ORDER BY RAND()
LIMIT 6

met php weet ik wel 100 manieren waarop ik dit kan oplossen maar dat is niet efficient en zou het dus graag in één query willen doen. Iemand een idee?

[Voutloos]

Lees je in voor wat betreft de aggragate functions (bijv. MIN) en misschien wel subqueries en probeer het een en ander.

En tip om mee te nemen: ORDER BY RAND() met een iets hogere limit wil je _niet_ op meer dan een paar honderd rijen loslaten.

[Verwijderd]

Voutloos schreef op woensdag 21 februari 2007 @ 15:45:
Lees je in voor wat betreft de aggragate functions (bijv. MIN) en misschien wel subqueries en probeer het een en ander.

En tip om mee te nemen: ORDER BY RAND() met een iets hogere limit wil je _niet_ op meer dan een paar honderd rijen loslaten.

Ik heb net subqueries geprobeert maar dat werkte niet echt. Ik zal in ieder geval even de andere tips bekijken.

[bigbeng]

Hoofdquery op telefoons en abonnementen voor die telefoons en een subquery op abonnementen voor telefoons uit de hoofdquery geordend op prijs met een limit 1. Enige voorwaarde die je er nog bij moet plaatsen is dat de abo's uit de hoofdquery matcht met die uit de hoofdquery. In pseudo:

SQL:

select *
from phones p, abbos a1
where a1.id in
(
    SELECT * from abbos a2
    WHERE p.id = a2.p_id
    order by a2.price
    LIMIT 1
)
ORDER BY popularity (of rand(), zo je wilt)
LIMIT 6

Ik heb het even conceptueel opgeschreven, geen zin om het helemaal netjes uit te werken

[Verwijderd]

bigbeng schreef op woensdag 21 februari 2007 @ 16:32:
Hoofdquery op telefoons en abonnementen voor die telefoons en een subquery op abonnementen voor telefoons uit de hoofdquery geordend op prijs met een limit 1. Enige voorwaarde die je er nog bij moet plaatsen is dat de abo's uit de hoofdquery matcht met die uit de hoofdquery. In pseudo:

SQL:

select *
from phones p, abbos a1
where a1.id in
(
    SELECT * from abbos a2
    WHERE p.id = a2.p_id
    order by a2.price
    LIMIT 1
)
ORDER BY popularity (of rand(), zo je wilt)
LIMIT 6

Ik heb het even conceptueel opgeschreven, geen zin om het helemaal netjes uit te werken

Bedankt! ik zal eens kijken of dit werkt voor mij

[Verwijderd]

Helaas werkt dat niet. Mijn mysql versie ondersteund blijkbaar geen IN

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Heb mysql versie 5.0.27 community draaien

edit;
limit er uit gehaald, werkt niet snel genoeg dus dat wordt het niet.

[ Voor 16% gewijzigd door Verwijderd op 21-02-2007 17:17 ]

[Voutloos]

al.id = (select id from ... limit 1), oftewel geen * in subquery. Verder kan je ook in buitenste query het goede id selecteren dmv MIN() en vervolgens in de subquery die ene id ophalen, heb je ook geen limit meer nodig. En zo zijn er nog 42 mogelijke queries.

Als niet snel genoeg, kan je dus index (p_id, price) overwegen.