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_postmeta 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
