WordPress & multiple queries or 1 query with join?

[mikeschinkel]

Hi all,

New to the forum, glad to find it.

I work with WordPress and we’re having a debate on the WordPress developers mailing list about which is better: 1 query with joins or multiple queries? This is a statement that was made on the list:

[B]Quote:[/B]

[I]Complex queries are the death of WP sites. Consider that in most of the webhosting in this world, database servers are shared amongst many websites. So you want to rely on the database as little as possible. Two simpler queries are often preferable to one query which adds load to the database server. Why? Because the DB server is generally your major bottleneck. Loading any sort of work onto it with JOINs and the like causes you nothing but trouble in the long run. Note, I’m not commenting on the specific code, just making a general observation. If I can do two or three queries with no joins, it’s usually a heck of a lot faster than one complicated query with two or three joins. Less queries is not always better.[/I]

That goes against everything I’d learned in the past, but maybe he is right?

Here’s some psuedo-code mixing MySQL and PHP showing the different choices we were discussing:

1.) $slug = 'actor’SELECT p.post_title FROM wp_posts pINNER 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_idINNER JOIN wp_terms t ON tt.term_id=t.term_idWHERE t.slug=$slug2.) $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_idINNER JOIN wp_terms t ON tt.term_id=t.term_idWHERE t.slug=$slugforeach($result as $row) $ids[] = $row->object_idSELECT post_title FROM wp_posts WHERE ID IN (ids)

Thanks in advance for your help in understanding this.

[gmouse]

He is not right.

The main problem of wordpress is that where and order by clauses can not be satisfied by indices because the where’s are on one table and order by on another. That is what makes wp a pita for hosters of large blogs. Optimization will involve major database changes and denormalization.

[mikeschinkel]

Thanks for the reply, but it doesn’t really answer the question. Instead is sounded just like an anti-WordPress rant…

[B]Quote:[/B]

The main problem of wordpress is that where and order by clauses can not be satisfied by indices because the where's are on one table and order by on another.

Can you be more specific?

[gmouse]

The answer to your question is not really interesting. Both queries are fast and you will not notice the difference.

I have looked at wordpress a long time ago, but immediately gave up because it would be faster to start from scratch for what I needed. What I posted is from memory. Maybe you could provide some facts on queries. On http://core.trac.wordpress.org/ticket/7415 you can find instructions on finding slow queries (start reading at SAVEQUERIES). Please do that for one large blog and post some results here so we can take a look at your queries.