Am I joining these 4 tables correctly?

Question

My query is terribly big:

SELECT n1.*, n2.rating, n3.*, n4.*
FROM stats n1
INNER JOIN members n2
ON n1.n_id = n2.id
INNER JOIN add_info n3
ON n1.n_id = n3.n_id
INNER JOIN other_table n4
ON n1.code = n4.code
WHERE n1.n_id = {$member_id} AND n1.code='{$some_code}'

Am I doing this correctly? If yes, can I make it better, more optimal? Because I really need information from all of 4 tables.

Thank you.

Answer 1

Honestly it doesn't look too bad.

When you ask about optimization, keep in mind that you can optimize code for different goals. For instance, you can optimize for performance--you want the code to accomplish its purpose as fast as possible. You can also optimize for code that's easy to read. You can optimize for maintainability or testability. In the real world, you usually balance each of these goals to achieve something appropriate for the situation.

If you find yourself joining all those tables often, consider using a view to make individual queries easier to write, read and maintain.

Answer 2

It's not bad if getting data from all four tables is what you need. Make certain you have indexes on your join keys, and consider using prepared statements both for improved performance and to prevent SQL injection.

You could use subqueries in the SELECT clause, but I doubt that would aid performance.

Answer 3

This query looks quite OK. You might want to see if you can restrict your select list a bit, explicitly naming columns instead of simply asking for n1.*, n3.* and n4.*.

How long does your query take? If you are dissatisfied with performance, check the indices on your joins.