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?
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.