Mysql self inner join query?

Question

When i run the below query in mysql it took 78 sec to display record. Is there other way to write this query. Here is my mysql query -> "

select distinct nuqta1.post_id from wp_postmeta as nuqta1 
inner join wp_postmeta as nuqta2 on (nuqta1.post_id = nuqta2.post_id) 
inner join wp_postmeta as nuqta4 on (nuqta1.post_id = nuqta4.post_id) 
inner join wp_postmeta as nuqta5 on (nuqta1.post_id = nuqta5.post_id) 
inner join wp_postmeta as nuqta6 on (nuqta1.post_id = nuqta6.post_id) 
inner join wp_postmeta as nuqta7 on (nuqta1.post_id = nuqta7.post_id) 
inner join wp_postmeta as nuqta8 on (nuqta1.post_id = nuqta8.post_id) 
inner join wp_postmeta as nuqta9 on (nuqta1.post_id = nuqta9.post_id) 
inner join wp_postmeta as nuqta10 on (nuqta1.post_id = nuqta10.post_id) 
inner join wp_postmeta as nuqta11 on (nuqta1.post_id = nuqta11.post_id) 
inner join wp_postmeta as nuqta12 on (nuqta1.post_id = nuqta12.post_id) 
where (nuqta2.meta_key = 'checkin' and nuqta2.meta_value LIKE '%10/31/2012%') 
and (nuqta4.meta_key = 'guests' and nuqta4.meta_value ='1') 
and (nuqta5.meta_key = 'roomtype' and nuqta5.meta_value LIKE '%Entire home/apt%') 
and (nuqta6.meta_key = 'price' and cast(nuqta6.meta_value as signed) BETWEEN '10' and '99999') 
and (nuqta7.meta_key = 'amenities' and nuqta7.meta_value LIKE '%Wireless Internet%') 
and (nuqta8.meta_key = 'amenities' and nuqta8.meta_value LIKE '%TV%') 
and (nuqta9.meta_key = 'amenities' and nuqta9.meta_value LIKE '%Kitchen%') 
and (nuqta10.meta_key = 'amenities' and nuqta10.meta_value LIKE '%Wireless Internet%') 
and (nuqta11.meta_key = 'amenities' and nuqta11.meta_value LIKE '%TV%') 
and (nuqta12.meta_key = 'amenities' and nuqta12.meta_value LIKE '%Kitchen%') 
and 1=1 order by nuqta1.post_id asc

". And and i am using wordpress table wp_postmeta to run this query

Answer 1

You have a lot of LIKE '%whatever%' clauses in this query. Each of these clauses necessarily causes a full table scan of wp_postmeta. It's actually pretty good that you got them done in less than ten seconds each.

If you know more about your meta_value column's values, so you can use LIKE 'whatever%' (getting rid of the leading % wildcard term) you'll speed things up a lot.

Also it's not clear why you have nuqta10, nuqta11, nuqta12. Those seem to search for the same stuff as 7,8,9. Considering the cost of the searches, you might consider eliminating those.