I have two select queries I want to combine for faster performance. I'm a bit confused as I'm not sure if I could use a JOIN or LEFT JOIN or something else to make it faster.
Here are the two queries I want to combine:
Query 1:
SELECT id FROM users WHERE email = :email LIMIT 1
Then, if that row exists from query 1, then run query 2:
SELECT * FROM settings WHERE id = :id_from_query_1
thank you for any kind of help!
When making a join query you can specify what column to join on. In this case it would be:
select users.id, settings.*
from users
join settings on users.id = settings.id
You could also use a double from clause which I think results in the same speed background wise that would look like:
select users.id, settings.*
from users, settings
where users.id = settings.id
If proper indexes are set a join should be faster than 2 selects. Ofcourse there are many factors when it comes to speed like load, network, what is cached and what isn't etc.
From your description, join should work. Perhaps something like this:
select u.id, s.* from users as u join settings as s on u.id = s.id where email = ?;
Since the 1st subquery will return 1 row (or none):
SELECT * FROM settings
WHERE id = (SELECT id FROM users WHERE email = :email LIMIT 1)
SELECT u.id
FROM users u
WHERE email = :email
LEFT JOIN settings s
ON u.id = s.id
LIMIT 1
Here's the left join query for you.
Select s.* from settings s left join users u
ON u.id = s.id
Where email = :email
LIMIT 1;
This will return all the columns from Settings table with that particular ID which has the corresponding "email".