Is there a more efficient way than doing several JOINS (MySQL)?

Question

I have three tables:

1. USER: user_id (pk); username
2. FIELD: field_id (pk); name
3. METADATA: metadata_id (pk); field_id (indx); user_id (indx); value

The reasoning for this is that the application allows custom fields to be created for each user. To display all the information for a user I am building a dynamic query (via PHP) which ends up looking like this:

SELECT
  u.username, m1.value AS m1value, m2.value AS m2value
FROM user AS u
LEFT JOIN metadata AS m1
  ON (u.user_id=m1.user_id AND m1.field_id=1)
LEFT JOIN metadata AS m2
  ON (u.user_id=m2.user_id AND m2.field_id=2)

This example has only 2 user metadata fields, but you get the idea what how this would look if there were a dozen fields.

Is there another, better way to write this query? I'm worried about the performance of this query as the users and metadata fields grow.

EDIT: I'd like to have one user per row in the returned results.

Answer 1

You will have to have two queries. One for user retrivial (SELECT * FROM users) and then another that will pull user's custom fields (SELECT * FROM fields WHERE users_id = user_id).

You could pull it off with one query in certain cases though ... tell us more on what exactly is the result you are going after.

Answer 2

Why not just grab them all at once?

SELECT u.user_id,u.username, m.field_id,m.value FROM user u
LEFT JOIN metadata m
ON u.user_id=m.user_id 
WHERE 1 ORDER BY user_id

Or for a particular user:

SELECT u.user_id,u.username, m.field_id,m.value FROM user u
LEFT JOIN metadata m
ON u.user_id=m.user_id 
WHERE user_id = ? ORDER BY user_id

Beyond being indexed, make sure the user_id is exactly the same type and length between the two tables, or you still end up doing table scans.

What language is your server code?

A simple way to get one row per user (kinda) is in your loop returning the rows, check each user_id if it's the same as the last. If not, new row.

while ( $row = $sth->fetch_object() ) {
  $previous_user_id = '';
  if ( $row->user_id != $previous_user_id ) {
    # new row
  } else {
    # not new row
  }
  $previous_user_id = $row->user_id;
}

Answer 3

You'd normally return one meta data element per row, as in:

SELECT u.username, mi.field_id, m1.value
FROM user AS u
LEFT JOIN metadata AS m1 ON u.user_id = m1.user_id

This should perform fine up to thousands of users.

Answer 4

you might try something like...

SELECT
    u.username,
   (SELECT TOP 1 m.value
    FROM metadata m
    WHERE u.user_id=m.user_id AND m.field_id=1),
   (SELECT TOP 1 m.value
    FROM metadata m
    WHERE u.user_id=m.user_id AND m.field_id=2)
FROM user AS u

... but the performance will probably be similar (and may be worse) to what you have. Check to make sure both user_id and field_id are indexed if you are having performance issues.

Answer 5

(As a commenter pointed out, this is not valid in MySQL, so, sorry. But in case you're interested:)

Do one JOIN to the metadata table as suggested above, and then use PIVOT to change your many rows per user into one row with many columns, one per field. I think this is valid in SQL Server 2005 and later.