I am searching to optimize my SQL.

My database schema is:

Houses

  • home_id
  • address
  • city
  • condition
  • zip
  • primary_photo_group_id

HOME_PHOTOS

  • photo_id (primary key)
  • home_id (home primary key)
  • photo_group_id (a photograph group is identical image, re-size from thumbnail to large size)
  • home_photo_type_id (how big the look whether it's a thumbnail or perhaps a large size)
  • photo_url_dir (the filesystem location in which the photo is saved)

Problem

It is extremely entirely possible that a 'home' doesn't have a photograph connected using the home. For the reason that situation, the primary_photo_group_id = . Otherwise, primary_photo_group_id` equals the audience_id from the photo for the main photo.

Slow SQL (b/c of UNION)

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    photo_id,
    photo_url_dir
FROM homes, home_photos
WHERE homes.home_id = home_photos.home_id
AND primary_photo_group_id = home_photo_group_id
AND home_photo_type_id = 2

UNION

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    null,
    null
FROM homes
WHERE primary_photo_group_id = 0

What I must do

I must eliminate the UNION since I am needing to search go back over the whole table 2x. How do i eliminate the UNION since I have to look for the situation where primary_photo_group_id = and when it isn't comparable to , then query the home_photos table

This is actually the pseudo code that must happen

SELECT  homes.home_id,
    address,
    city,
    state,
    zip,
    photo_id,  (include only if primary_photo_group_id != 0)
    photo_url_dir  (include only if primary_photo_group_id != 0)
FROM homes,
    home_photos (include only if primary_photo_group_id != 0)
WHERE 
    primary_photo_group_id = 0
ELSE
    homes.home_id = home_photos.home_id
        AND primary_photo_group_id = home_photo_group_id
        AND home_photo_type_id = 2
SELECT  homes.home_id,
        address,
        city,
        state,
        zip,
        photo_id,
        photo_url_dir
FROM    homes
LEFT JOIN
        home_photos
ON      home_photos.home_id = homes.home_id
        AND home_photo_group_id = CASE WHEN primary_photo_group_id = 0 THEN NULL ELSE primary_photo_group_id END
        AND home_photo_type_id = 2

Getting an amalgamated index on home_photos (home_id, home_photo_group_id, home_photo_type_id) will greatly improve this question.

Observe that using CASE is a little more efficient than left joining on 0, even when you will find no records with home_photo_group_id = 0 in home_photos.

When MySQL sees a JOIN on NULL (which could yield nothing obviously), it will not even consider the became a member of table. If this joins on 0, still it needs to look into the index and make certain no value is available.

This really is not so a performance impact, but nonetheless can enhance your query time by a number of percents, particularly if you have lots of 0's in homes.

Check this out entry during my blog for performance detail:

Also observe that your tables aren't in 2NF.

Your group_id is dependent on home_id, and including it into home_photos is 2NF breach.

It isn't always bad, however it might be harder to handle.

When the second totally reduced compared to first, it is because you possess an index on {home_id, primary_photo_group_id} (or possibly just {home_id}), but this is not on {primary_photo_group_id} alone. You'll need a catalog about this column if you wish to enhance the performance of searching for on that column.

Educate yourself about Joins

 Select * from table_a, table_b

is really a mix join - that you simply limit into an inner join using the where clauses. Turn your queries into inner joins, then educate yourself about outer joins.

Edit: I did not just provide you with the answer, because I believed it was homework, however i still will not just provide you with the answer because outer joins are essential enough to understand about even when you are just writing SQL for any website. you will be a much better person for learning it, and taking advantage of this for example.