Inefficient Query Madness!

Preface: This is a bit of a rant. I don’t hate WordPress and I appreciate the effort all of the contributors have put in to it. I just need to blow off some steam.

I am tasked with migrating a large static site to WordPress which involves a large number of pages. Thousands of them. I know know there are some other performance issues related to how permalinks are handled, but this one is completely senseless.

It takes a ridiculously long time to bring up the “Add Page” screen in the admin. Wondering what could be so difficult about rendering an edit screen, I profiled it. Found this query used to populate the “Parent” menu:

SELECT *
FROM wp_posts
WHERE (post_type = 'page'
AND post_status = 'publish')
ORDER BY menu_order,wp_posts.post_title ASC

Really?!? WordPress is selecting *, every column (including post_content), from every single page, and on top of that ordering the result by two unindexed columns, one of which can’t be indexed because it is a text column, just to render the options for parent field?

The select * doesn’t really make the query itself any slower, but it slows everything else down to load the content of every page in to memory to display a select menu that only needs select id, post_title, post_parent.

The real query performance issue is the order by. Ordering a large result set by two unindexed columns, increases the query execution time for me by 5,300%.

The “All Pages” screen has similar over selection and and unindexed ordering issues as well. It was just slightly less surprising there.