Hello! I keep running into situations where I use the "in" operator which results in really _slow_ queries. I need to find a graceful way to work around it.
Here's my example.. We have a table called "campaigns" (for marketing campaigns) and another table called "tokens" (representing uuids that are embedded in URLs for online marketing.)
campaigns
---------
campaign_id
name
tokens
------
token_id
uuid
campaign_id
As you can probably see, each token is associated with a campaign. Selecting which campaign a specific token is associated with can be done using:
SELECT campaigns.name
FROM campaigns, tokens
WHERE tokens.campaign_id = campaigns.campaign_id
AND tokens.uuid = 'DC84A2FA-3048-2FCD-167A16251DEFC0CA'
That's easy. However, now I have another table (visitors) that represents visitors coming from our campaigns. It looks like:
visitors
--------
visitor_id
uuid
I have to write a query that shows me all visitors coming from a particular campaign. I've done it this way:
SELECT *
FROM visitors
WHERE visitor.uuid IN (
SELECT campaigns.uuid
FROM campaigns, tokens
WHERE tokens.campaign_id = campaigns.campaign_id
AND campaigns.name = 'yahoo' )
This works, but it started running very slowly once my data set got large. Can anyone suggest an alternative query that's faster?
Thanks!
- Bret
Here's my example.. We have a table called "campaigns" (for marketing campaigns) and another table called "tokens" (representing uuids that are embedded in URLs for online marketing.)
campaigns
---------
campaign_id
name
tokens
------
token_id
uuid
campaign_id
As you can probably see, each token is associated with a campaign. Selecting which campaign a specific token is associated with can be done using:
SELECT campaigns.name
FROM campaigns, tokens
WHERE tokens.campaign_id = campaigns.campaign_id
AND tokens.uuid = 'DC84A2FA-3048-2FCD-167A16251DEFC0CA'
That's easy. However, now I have another table (visitors) that represents visitors coming from our campaigns. It looks like:
visitors
--------
visitor_id
uuid
I have to write a query that shows me all visitors coming from a particular campaign. I've done it this way:
SELECT *
FROM visitors
WHERE visitor.uuid IN (
SELECT campaigns.uuid
FROM campaigns, tokens
WHERE tokens.campaign_id = campaigns.campaign_id
AND campaigns.name = 'yahoo' )
This works, but it started running very slowly once my data set got large. Can anyone suggest an alternative query that's faster?
Thanks!
- Bret