Crushes server on reciprocal connections on big database table?

I've happily used Posts2Posts for several years for a connection between my WordPress install's main post type and a smaller custom post type — works great! But recently, I created a new connection where both sides are the main post type. (It's to connect two different posts that are translated versions of one another — say, the English and Portuguese versions of the same story.)

function my_translation_connection_types() { if ( !function_exists( 'p2p_register_connection_type' ) ) return; p2p_register_connection_type( array( 'name' => 'language_to_language', 'from' => 'post', 'to' => 'post', 'reciprocal' => true, 'title' => 'Translated versions', 'admin_box' => array( 'show' => 'any', 'context' => 'side' ) ) ); } add_action( 'init', 'my_translation_connection_types', 100 ); But when I try to check that connection from a WP template page, it works fine — but over a period of time, the entire site gets extremely slow, MySQL runs super hot, and eventually everything crashes — and then repeat. Server logs and P3 Profiler agree that it's this Posts2Posts call that is doing it; when I comment out the section below in the template file, the crashing stops.

`$connected = new WP_Query( array( 'connected_type' => 'language_to_language', 'connected_items' => get_queried_object(), 'nopaging' => true, ) );

if ( $connected->have_posts() ) : echo '

Translation: '; $langcounter = 0; while ( $connected->have_posts() ) : $connected->the_post(); $transintro = 'Read in English:'; $langs = get_the_terms( $post->ID, 'intl_languages' ); if ( $langs && !is_wp_error( $langs ) ) : $langs_links = array(); foreach ( $langs as $lang ) {
if ($lang->slug == 'spanish') { $transintro = 'Leer en español'; } if ($lang->slug == 'german') { $transintro = 'Lesen Sie auf Deutsch'; } if ($lang->slug == 'chinese') { $transintro = '点击查看本文中文版'; } if ($lang->slug == 'portuguese') { $transintro = 'Leia em português'; } if ($lang->slug == 'polish') { $transintro = 'Przeczytaj ten artykuł po polsku'; } // add more languages here as we publish translations } $langcounter++; endif; if ( !$langs ) : $transintro = 'Read in English '; $langcounter++; endif;
?> <?php if ($langcounter > 1) { echo ' / '; } ?><?php echo $transintro; ?><?php // the_title(); ?> <?php endwhile; echo '

'; wp_reset_postdata(); endif; `

Admittedly, my wp_posts table is large — 1 GB in size, ~30K rows. But here's my question: Is this something that is just doomed, running both sides of a Posts2Posts connection on a large database table? Or is there some way I could change this code to make it not melt my server? Thanks!

scribu / wp-posts-to-posts

Crushes server on reciprocal connections on big database table? #549