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

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.

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;
}

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.

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.

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.

(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.