Optimize an SQL statement

Hey, I'm running WordPress, the database diagram could be found here: http://codex.wordpress.org/Database_Description

After doing tonnes of filters and applying some hooks to the core, I'm left with the following query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts 

JOIN wp_开发者_StackOverflowpostmeta ppmeta_beds ON (ppmeta_beds.post_id = wp_posts.ID AND
  ppmeta_beds.meta_key = 'pp-general-beds' AND ppmeta_beds.meta_value >= 2)

JOIN wp_postmeta ppmeta_baths ON (ppmeta_baths.post_id = wp_posts.ID AND
  ppmeta_baths.meta_key = 'pp-general-baths' AND ppmeta_baths.meta_value >= 3)

JOIN wp_postmeta ppmeta_furnished 
  ON (ppmeta_furnished.post_id = wp_posts.ID AND
  ppmeta_furnished.meta_key = 'pp-general-furnished' 
  AND ppmeta_furnished.meta_value = 'yes')

JOIN wp_postmeta ppmeta_pool 
  ON (ppmeta_pool.post_id = wp_posts.ID AND
  ppmeta_pool.meta_key = 'pp-facilities-pool' 
  AND ppmeta_pool.meta_value = 'yes')

JOIN wp_postmeta ppmeta_pool_type 
  ON (ppmeta_pool_type.post_id = wp_posts.ID AND
  ppmeta_pool_type.meta_key = 'pp-facilities-pool-type' 
  AND ppmeta_pool_type.meta_value 
  IN ('tennis', 'voleyball', 'basketball', 'fitness'))

JOIN wp_postmeta ppmeta_sport ON (ppmeta_sport.post_id = wp_posts.ID AND
  ppmeta_sport.meta_key = 'pp-facilities-sport' 
  AND ppmeta_sport.meta_value = 'yes') 

JOIN wp_postmeta ppmeta_sport_type ON (ppmeta_sport_type.post_id = wp_posts.ID 
  AND ppmeta_sport_type.meta_key = 'pp-facilities-sport-type' 
  AND ppmeta_sport_type.meta_value 
  IN ('tennis', 'voleyball', 'basketball', 'fitness')) 

JOIN wp_postmeta ppmeta_parking ON (ppmeta_parking.post_id = wp_posts.ID 
  AND ppmeta_parking.meta_key = 'pp-facilities-parking' 
  AND ppmeta_parking.meta_value = 'yes') 

JOIN wp_postmeta ppmeta_parking_type 
  ON (ppmeta_parking_type.post_id = wp_posts.ID 
  AND ppmeta_parking_type.meta_key = 'pp-facilities-parking-type' 
  AND ppmeta_parking_type.meta_value IN ('street', 'off-street', 'garage')) 

JOIN wp_postmeta ppmeta_garden ON (ppmeta_garden.post_id = wp_posts.ID 
  AND ppmeta_garden.meta_key = 'pp-facilities-garden' 
  AND ppmeta_garden.meta_value = 'yes') 

JOIN wp_postmeta ppmeta_garden_type 
  ON (ppmeta_garden_type.post_id = wp_posts.ID 
  AND ppmeta_garden_type.meta_key = 'pp-facilities-garden-type' 
  AND ppmeta_garden_type.meta_value IN ('private', 'communal')) 

JOIN wp_postmeta ppmeta_type ON (ppmeta_type.post_id = wp_posts.ID 
  AND ppmeta_type.meta_key = 'pp-general-type' 
  AND ppmeta_type.meta_value IN ('villa', 'apartment', 'penthouse')) 

JOIN wp_postmeta ppmeta_status ON (ppmeta_status.post_id = wp_posts.ID 
  AND ppmeta_status.meta_key = 'pp-general-status' 
  AND ppmeta_status.meta_value IN ('off-plan', 'resale')) 

JOIN wp_postmeta ppmeta_location_type 
  ON (ppmeta_location_type.post_id = wp_posts.ID 
  AND ppmeta_location_type.meta_key = 'pp-location-type' 
  AND ppmeta_location_type.meta_value 
  IN ('beachfront', 'countryside', 'town-center', 'near-the-sea', 
    'hillside', 'private-resort')) 

JOIN wp_postmeta ppmeta_price_range 
  ON (ppmeta_price_range.post_id = wp_posts.ID 
  AND ppmeta_price_range.meta_key = 'pp-general-price' 
  AND ppmeta_price_range.meta_value BETWEEN 10000 AND 50000) 

JOIN wp_postmeta ppmeta_area_range 
  ON (ppmeta_area_range.post_id = wp_posts.ID 
  AND ppmeta_area_range.meta_key = 'pp-general-area' 
  AND ppmeta_area_range.meta_value BETWEEN 50 AND 150) 

WHERE 1=1 AND (((wp_posts.post_title LIKE '%fdsfsad%') 
OR (wp_posts.post_content LIKE '%fdsfsad%'))) 
AND wp_posts.post_type = 'property' 
AND (wp_posts.post_status = 'publish' 
  OR wp_posts.post_status = 'private') 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

It's way too big. Could anybody please show me a way of optimizing all those joins into fewer statements? As you can see they all use the same tables but under different names. I'm not an SQL guru but I think there should be a way, because this is insane ;)

Thanks!

Update Here's what explain returns: http://twitpic.com/1cd36p

You can't optimise that. You need all the joins because they are separate filters probably due to AND logic i.e. you want beach-front AND off street parking.

Your best bet is to ensure your table is not fragmented. Have an index on meta-value, meta-key and post-id.

PHP connects to mysql through its own protocols. The size of the SQL allowed goes up MBs, also since php/mysql communication is on localhost (or on LAN) it will not be a bottleneck. So, the size of SQL does not matter in terms of communication between php amd mysql.

In terms of preparing the SQL (parsing and planning) it might need extra time and you might consider creating a VIEW that does the above.

Also, what matters are the indexes, but the plan looks ok (except for the filesort - you might want to have an index which can be used for sorting and selecting on wp_post).

Furthermore you are selecting columns only from the wp_post table, but are joining on many more tables - consider rewriting it using WHERE EXISTS conditions.

As for optimizing - do get some sample data in your database at the order of magnitude that would simulate the hardware and size of the database that you intend to have in the end. Optimizing queries is something that you should not forget during development, but also spending too much time too early might not be justified.

This is a particularly inefficient way of implementing attribute-based searching. Unfortunately I have no idea if WordPress can do it any other way, ie : each attribute should have its own column and index. This is less flexible but much faster.

As Simon Sabin has already said you can not get rid of joins because they work as filters.

For reading purposes it would be great to have an index on wp_postmeta as (post_id, meta_key, metavalue(255)). The order is significant because in that order the conditions in joins are evaluated, but the index will double the size of the database and slow down inserts and updates.

The order in which the conditions for the product set are applied, is also not optimal: equations should go first and then LIKE and range conditions, otherwise no indices are used for filtering.

The last but not least killer is sorting by post_date: since each row contains TEXT fields in results, the MySQL will use filesystem temporary tables for sorting. Sorting by wp_posts.ID might be a better idea if ids and dates are increasing together.