1

Suppose I have a table structure like

enter image description here

I want to grab all user metas

is it better to do a big query joining all tables in the image ...

SELECT [columns ...] FROM usermetasections LEFT JOIN usermetakeys ON ...
LEFT JOIN usermetas ON ...
LEFT JOIN users ON ...
WHERE users.id = xxx

...

... or break them into smaller queries like ...

Loop through SELECT * FROM usermetasections
    Loop through SELECT * FROM usermetakeys WHERE section = xxx
        Loop thought SELECT * FROM usermetas WHERE key = xxx AND user = xxx

I think if I go the 1st approach, it will be faster for "machine" performance but bad for developer performance? harder to read, more code to parse queries into PHP objects?

JM at Work
  • 2,417
  • 7
  • 33
  • 46

1 Answers1

4

Usually, you let the database itself do the joining. That's what a relational database management system is made for. It contains countless optimizations that will perform better than your average application code.

Sometimes that does indeed create a lot of overhead in regards to information being sent over the network (say, you loop over 1000 records, and only if a few complex conditions are met, you need the info from the other table) in which case you could let your application-code do the join, but even then you can just re-query with SELECT yourfields FROM yourtables-joined WHERE yourtable.id IN (list,of,IDs)

Konerak
  • 39,272
  • 12
  • 98
  • 118