gistfile1.txt

Which performs better (in general) and why in a PHP app (WordPress) across many different potential web hosting scenarios?  

The code is obviously psuedo-code mixing MYSQL+PHP.  Reply via gmail to mikeschinkel. 

1.) 
$slug = 'actor'

SELECT p.post_title FROM wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id	
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id
INNER JOIN wp_terms t ON tt.term_id=t.term_id
WHERE t.slug=$slug

2.) 
$slug = 'actor'

$result = SELECT tr.object_id FROM wp_term_relationships tr 
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id
INNER JOIN wp_terms t ON tt.term_id=t.term_id
WHERE t.slug=$slug

foreach($result as $row)
	$ids[] = $row->object_id

SELECT post_title FROM wp_posts WHERE ID IN (ids)

3.) 
SELECT p.post_title FROM wp_posts p WHERE p.ID IN (
SELECT tr.object_id FROM wp_term_relationships tr 
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id=tt.term_taxonomy_id
INNER JOIN wp_terms t ON tt.term_id=t.term_id
WHERE t.slug=$slug)

1. If the schema is solid and good indexes exist this is the best option.
2. This would be bad for lots of rows.
3. Sub-queries = BIG HUGE BALL OF FLAMING DEATH.

I would add that an EXPLAIN for each of these queries would help in making the decision.

Hey Brian,

Thanks for commenting.

Care you review this thread then?

http://groups.google.com/group/wp-hackers/browse_thread/thread/9fa53ada01217b9b

Especially these?

http://groups.google.com/group/wp-hackers/browse_thread/thread/9fa53ada01217b9b/0f85c1459889e82d#msg_309f6b8722fdf92d
http://groups.google.com/group/wp-hackers/browse_thread/thread/9fa53ada01217b9b/0f85c1459889e82d#msg_493260c169e49ce6

wow, i haven't worked with the guts of wordpress in a while. that went over my head pretty fast. i'll just say this:

1 should be fine as long as the correct indexes are present and they are valid. I would avoid the fragility of 2 and the peril of 3. :)

well, after reading the follow up discussion from otto i'm less freaked out by (2) but I think really what this problem points out is that the database is in the wrong normal form. in an enterprise solution you would have 2 databases with different normal forms and a process for migrating data from 1 to the other. that's probably not helpful here though...

Everyone is right in that thread. =) More queries == bad. Complex joins == bad. Its all about what is the least bad. Schemas and explains really need to make these decisions, not opinions. Load up a Wordpress blog with 1,000,000 posts and start running queries. After you think you know what is best, hit it with siege or openload or ab to see how it works with concurrency.