Using keys on JOIN

Question

I want to get data that is separated on three tables:

app_android_devices: id | associated_user_id | registration_id

app_android_devices_settings: owner_id | is_user_id | notifications_receive | notifications_likes_only

app_android_devices_favorites: owner_id | is_user_id | image_id

owner_id is either the id from app_android_devices or the associated_user_id, indicated by is_user_id. That is because the user of my app should be able to login to their account or use the app anonymously. If the user logged in he will have the same settings and likes on all devices. associated_user_id is 0 if the device is used anonymously or the user ID from another table.

Now i've got the following query:

SELECT registration_id 
FROM app_android_devices d
JOIN app_android_devices_settings s 
 ON ((d.id=s.owner_id AND 
      s.is_user_id=0) 
   OR (
      d.associated_user_id=s.owner_id AND 
      s.is_user_id=1))
JOIN app_android_devices_favorites f
  ON (((d.id=f.owner_id AND 
        f.is_user_id=0)
      OR
         d.associated_user_id=f.owner_id AND 
         f.is_user_id=1)
    AND f.image_id=86)
WHERE s.notifications_receive=1 
 AND (s.notifications_likes_only=0 OR f.image_id=86);

To decide if the device should receive a push notification on a new comment. I've set the following keys:

app_android_devices: id PRIMARY, associated_user_id
app_android_devices_settings: (owner_id, is_user_id) UNIQUE, notifications_receive, notifications_likes_only
app_android_devices_favorites: (owner_id, is_user_id, image_id) UNIQUE

I've noticed that the above query is really slow. If I run EXPLAIN on that query I see that MySQL is using no keys at all, although there are possible_keys listed.

What can I do to speed this query up?

You should consider using table aliases. This is a nightmare to read. https://en.wikipedia.org/wiki/Alias_(SQL) — mawburn, Jul 07 '14 at 17:51
Looks like you aren't using any keys ni your where clause... — Cheruvian, Jul 07 '14 at 17:52
@Cheruvian Sorry about that I forgot that in the question. The keys are set in the database however. — Johann Bauer, Jul 07 '14 at 17:54
I don't understand why you'd OUTER JOIN tables from which you select no columns — Strawberry, Jul 07 '14 at 18:06
@Strawberry You're right, I changed it to INNER JOIN but that makes no difference either. — Johann Bauer, Jul 07 '14 at 18:10
AND OR AND OR etc. Screwy. Try adding some parentheses so we can begin to extract some semblance of meaning from it. — Strawberry, Jul 07 '14 at 18:16
Actually, this kind of mess is often indicative of poor design. — Strawberry, Jul 07 '14 at 18:21
the two occurrences of `d.associated_user` in both or statements suspect... are you sure the join shouldn't be on d.id? as in the first part of the or? ... or are they not even needed in once case (since it HAS to be 0 and the other case where it HAS to be > 0... is the > 0 even needed? — xQbert, Jul 07 '14 at 18:37
@xQbert Yes, you are right, those statements were redundant. I removed them from the question. — Johann Bauer, Jul 07 '14 at 18:46

Air · Accepted Answer · 2014-07-07T19:53:59.643

Having such complicated JOIN conditions makes life hard for everyone. It makes life hard for the developer who wants to understand your query, and for the query optimizer that wants to give you exactly what you ask for while preferring more efficient operations.

So the first thing that I want to do, when you tell me that this query is slow and not using any index, is to take it apart and put it back together with simpler JOIN conditions.

From the way you describe this query, it sounds like the is_user_id column is a sort of state variable telling you whether the user is or is not logged in to your app. This is awkward to say the least; what happens if s.is_user_id != f.is_user_id? Why store this in both tables? For that matter, why store this in your database at all, instead of in a cookie?

Perhaps there's something I'm not understanding about the functionality you're going for here. In any case, the first thing I see that I want to get rid of is the OR in your JOIN conditions. I'm going to try to avoid making too many assumptions about which values in your query represent user input; here's a slightly generic example of how you might be able to rewrite these JOIN conditions as a UNION of two SELECT statements:

SELECT ... FROM
    app_android_devices d
        JOIN
    app_android_devices_settings s ON d.id = s.owner_id 
        JOIN
    app_android_devices_favorites f ON d.id = f.owner_id
WHERE s.is_user_id = 0 AND f.is_user_id = 0 AND ...
UNION ALL
SELECT ... FROM
    app_android_devices d
        JOIN
    app_android_devices_settings s ON d.associated_user_id = s.owner_id
        JOIN
    app_android_devices_favorites f ON d.associated_user_id = f.owner_id
WHERE s.is_user_id = 1 AND f.is_user_id = 1 AND ...

If these two queries hit your indexes and are very selective, you might not notice the additional overhead (creation of a temporary table) required by the UNION operation. It looks as though one of your result sets may even be empty, in which case the cost of the UNION should be nil.

But, maybe this doesn't work for you; here's another suggestion for an optimization you might pursue. In your original query, you have the following condition:

WHERE s.notifications_receive=1 
    AND (s.notifications_likes_only=0 OR f.image_id=86);

This isn't too cryptic - you want results only when the notifications_receive setting is true, and only if the notifications_likes_only setting is false or the requested image is a "favorite" image. Depending on the state of notifications_likes_only, it looks like you may not even care about the favorites table - wouldn't it be nice to avoid even reading from that table unless absolutely necessary?

This looks like a good case for EXISTS(). Instead of joining app_android_devices_favorites, try using a condition like this:

WHERE s.notifications_receive = 1
    AND (s.notifications_likes_only = 0
        OR EXISTS(SELECT 1 FROM app_android_devices_favorites 
                  WHERE image_id = 86 AND owner_id = s.owner_id)

It doesn't matter what you try to SELECT in an EXISTS() subquery; some people prefer *, I like 1, but even if you gave specific columns it wouldn't affect the execution plan.

@jreuab Glad to hear it. I added another suggestion that you might find useful. — Air, Jul 07 '14 at 19:57

Using keys on JOIN

1 Answers1