Optimizing Small MySQL Join Query

Question

I have a list of venues in the Venues table, and a list of a cities/states in the Locations table. The venue is associated with an area code unique to my organization, referred to as a SOYID. The SOYID is made up of a geographical area - each row in the Locations table has a City, State, and the corresponding SOYID. Some Venues rows have a SOYID, others do not; for those that do not, I need to find the SOYID for the city and state listed. I only want to select those Venues in a specific SOYID.

This query works, however, it takes a few seconds to load; I don't think I am writing the query correctly. Currently Venues has approx 140 rows, Locations has 40,000.

    $sql = "SELECT DISTINCT a.VenueID, a.Name, a.PhotoID, a.City, a.StateAbbr
            FROM Venues AS a LEFT JOIN Locations AS c ON a.City = c.city
            WHERE a.SOYID = '" . mysql_real_escape_string($SOYID) . "'
            OR ((c.city = a.City) AND  (c.state = a.StateAbbr) AND (c.SOYID = '" . mysql_real_escape_string($SOYID) . "'))
            ORDER BY a.Name ASC";

Joe Stefanelli · Accepted Answer · 2011-09-08 15:26:41Z

2

Any time you reference a column from a LEFT JOINed table (c.state and c.SOYID in your specific case) in the WHERE clause, you force that join to behave like an INNER JOIN. Instead, make those tests part of the join condition:

"SELECT DISTINCT a.VenueID, a.Name, a.PhotoID, a.City, a.StateAbbr
    FROM Venues AS a 
        LEFT JOIN Locations AS c 
            ON a.City = c.city
                AND a.StateAbbr = c.state
                AND c.SOYID = '" . mysql_real_escape_string($SOYID) . "'
    WHERE a.SOYID = '" . mysql_real_escape_string($SOYID) . "'
        OR c.SOYID IS NOT NULL /* LEFT JOIN found a matching row */
    ORDER BY a.Name ASC"

EDIT: Based on comments, this version should allow you do remove the DISTINCT requirement:

"SELECT a.VenueID, a.Name, a.PhotoID, a.City, a.StateAbbr
    FROM Venues AS a 
    WHERE a.SOYID = '" . mysql_real_escape_string($SOYID) . "'
        OR EXISTS(SELECT NULL
                      FROM Locations AS c 
                      WHERE a.City = c.city
                          AND a.StateAbbr = c.state
                          AND c.SOYID = '" . mysql_real_escape_string($SOYID) . "') 
    ORDER BY a.Name ASC"

edited Sep 8, 2011 at 15:26

answered Sep 8, 2011 at 14:58

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ypercubeᵀᴹ Over a year ago

I guess he can drop the DISTINCT now, can't he? Or use a GROUP BY a.VenuID instead of teh DISTINCT.

Joe Stefanelli Over a year ago

@ypercube: Yes, I think it's probably safe to drop the DISTINCT.

James Glass Over a year ago

If I drop the distinct, I still get repeat rows, so no, I still need to keep the distinct. Thanks!

Joe Stefanelli Over a year ago

@John: I added an alternate query that should eliminate the need for the distinct.

Collectives™ on Stack Overflow

Optimizing Small MySQL Join Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related