I'm developing a map-based website and need to get posts associated with a place.

Each place has a post in wp_posts and the other associated posts (images of each place etc) is associated with the place using the custom lookup-table wp_lookup_places by post_id.

How can I query to get the results in ms? I thought it would be a simple task for a mysql to filter based on indexed integers?

Rows in lookup: 774 202 Total posts in db: 1 073 777

Current execution time: approx 33 sec

My lookup table:

CREATE TABLE wp_lookup_places (
    post_id bigint(20),
    place_id bigint(20),
    PRIMARY KEY (post_id, place_id)

Query from extending WP_Query with a posts_where filter for place with post_id 638891.

SELECT   wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_lookup_places ON  (wp_lookup_places.post_id = wp_posts.ID AND wp_lookup_places.place_id = 638891)  
WHERE 1=1  
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
ORDER BY wp_posts.post_title ASC LIMIT 0, 250;

Above query with EXPLAIN:

******************** 1. row *********************
           id: 1
  select_type: SIMPLE
        table: wp_posts
         type: ref
possible_keys: PRIMARY,type_status_date
          key: type_status_date
      key_len: 62
          ref: const
         rows: 532062
        Extra: Using index condition; Using where; Using filesort
******************** 2. row *********************
           id: 1
  select_type: SIMPLE
        table: wp_bb_places
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 16
          ref: server01.wp_posts.ID,const
         rows: 1
        Extra: Using where; Using index

1 Answer 1


It's the order of your primary key declaration. If you're always searching on place_id, declare that first, then post_id:

PRIMARY KEY (place_id, post_id)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.