Optimizing a join on a NVP table

[delayedinsanity]

MySQL 5.1.37, this query works, I'm just wondering if it's the best way to do it? I'm not a huge fan of NVP style tables but I have to work on one in this case to pull the name of a customer in connection with their transaction ID from another table;

 

SELECT t.transaction_id, f.meta_value, l.meta_value
FROM `wp_purchase` AS t
LEFT JOIN `wp_usermeta` AS f ON t.user_id = f.user_id
LEFT JOIN `wp_usermeta` AS l ON f.user_id = l.user_id
WHERE f.user_id = '1'
AND f.meta_key = 'first_name'
AND l.meta_key = 'last_name'

 

Is there a more efficient query method for joining NVP tables to other properly built tables, or even just a better way to go about this particular one? It's not slowing me down by any means and as mentioned it gets the results I want but I just happen to have a strong feeling like there's a far better way to go about it.

 

[s0c0]

I'm not sure what you mean by NVP table, but I am familiar with the wordpress tables you posted.  Is there no way for you to make this INNER JOINs rather than LEFT JOINS?  For instance when I have written querries against this postmeta table I did the following:

 

                INNER JOIN
                    wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_key = 'views')
                INNER JOIN
                    wp_postmeta meta2 ON (meta2.post_id = wp_posts.ID and meta2.meta_key = '_aioseop_description')

 

Try just putting multiple parameters on your JOINS and use INNER instead ala http://blog.cnizz.com/2010/08/19/mysql-inner-join-on-multiple-parameters-and-condtions/

 

Looking more at this, the following should work:

SELECT t.transaction_id, f.meta_value, l.meta_value
   FROM `wp_purchase` AS t
   INNER JOIN `wp_usermeta` AS f ON (t.user_id = f.user_id AND f.meta_key='first_name')
   INNER JOIN `wp_usermeta` AS l ON (t.user_id = l.user_id AND l.meta_key='last_name')
   WHERE f.user_id = '1'

[fenway]

There isn't a more efficient way -- but yes, INNER JOIN wherever possible.

[delayedinsanity]

Thanks all! I could have sworn I wrote a reply to s0c0 yesterday but I must not have hit Post.

 

The last three projects I've worked on have all required PayPal integration so I defaulted to their terminology, NVP or Name Value Pair, aka Key Value or however you wish to refer to it. While there's a high probability of some obvious exceptions, my mentality is that optimization will often go hand in hand with a clean looking query. In this case there's not a huge difference though I think your version is a touch more readable and have chosen to go with that, and the INNER JOIN. Again, thanks.