CODEWITHSUNDEEP
mysqljoin
Brandon - Free Palestine
Brandon - Free Palestine

Reputation: 16656

Multiple simple joins Vs. Multiple simple selects

I have a meta table that looks like this

ID     post_id     meta_key      meta_value
1      1           key_01        val
2      1           key_02        val
3      1           key_03        val

and I want to get all 3 keys and values. Is it any slower to use JOIN then SELECT? JOIN would make it so much easier, for example:

SELECT M1.meta_value AS key_01, M2.meta_value AS key_02, M3.meta_value AS key_04 
FROM `meta` AS M1 
     JOIN `meta` M2 ON M2.post_id = 1 AND M2.meta_key = 'key_02' 
     JOIN `meta M3 ON M3.post_id = 1 AND M3.meta_key = 'key_03' 
WHERE M1.meta_key = 'key_01'

I didn't test that any, but you should get the idea.

Upvotes: 0

Views: 82

Answers (2)

Simon
Simon

Reputation: 9425

I beleive JOINS are more efficient than using nested SELECTS. Also indexing the foreign-key columns (joined columns) will improve performance as well. So pop an index on post_id and meta_key. Indexes may hurt INSERT performance so take this into consideration.

Upvotes: 1

davogotland
davogotland

Reputation: 2777

i think that the mysql engine has a better chance to create a good query plan when using joins than it has when doing sub-selects and/or unions.

Upvotes: 1

Related Questions