MYSQL one to one relation join better than individual

Question

For efficiency point, if one to one relation take place, which thing is better, keep this in mind there is happening in non indexed fields,individual queries i.e

select email from users where name="John";

then doing this

select * from jobs where email="{first one}";

Or this single query is better:

select j.* from jobs j inner join users u on u.id=j.id where j.email="{email}";

What is `{first one}` supposed to represent? The efficiency of a query depends on many things. It really depends. — Tim Biegeleisen, Mar 22 '18 at 12:25
@TimBiegeleisen, I think he means the result of the first query. The question need to be edited! — Hamza Abdaoui, Mar 22 '18 at 12:26
@TimBiegeleisen, "then" and "than" are often confused, but he really does mean "then". The comparison is doing the first two, or just the third. (I'd lean towards single-query, unless there's other logical concerns in between.) — Marc L., Mar 22 '18 at 12:34

score 1 · Answer 1 · answered Mar 22 '18 at 12:38

I will answer from the point of view of an application which is hitting your MySQL database (maybe PHP). In this case, your second query is by far preferable, because you get the data you need in a single query, in a single network call. On the other hand, doing two separate queries requires two full round trips to the database. This may seem like a light point, but keep in mind that this could really drag down performance if you have many users and you use such MySQL etiquette throughout your application.

As for the speed of your join query, yes it would probably be slower than either of the two single queries, but if you tune the join query you can probably make it work for you. And two roundtrips is also probably a bigger performance hit than a slightly longer query.

MYSQL one to one relation join better than individual

1 Answers1