3

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

Tahir Shahid
  • 31
  • 2
  • Remove unnecessary brackets from join clause and where clause. and also distinct if there is not required. – Bajrang Oct 23 '12 at 12:08
  • 1
    Thanks for displaying the query. It might help if you tell us what you're trying to do. – O. Jones Oct 23 '12 at 12:10
  • erk. Not surprised it took so long. But you've provided no details of the table structure nor the execution plan. However to fix this properly you need to radically overhaul your DBMS schema - the msot sensible way would be to normalise it properly. Having said that, it go a lot faster using the HandlerSocket API – symcbean Oct 23 '12 at 14:30
  • @lucia nie I try that. I remove distinct and brackets from join clause but it didn't worked. – Tahir Shahid Oct 26 '12 at 11:08
  • I am trying to get the post_id from the wp_postmeta table where my meta_key = "guests" and meta_value = "1" and also where my meta_key = 'price' and meta_value = '300' . I am using wordpress table postmeta for my searching. – Tahir Shahid Oct 26 '12 at 11:12
  • Try the solution I posted to a similar question here: http://stackoverflow.com/a/15398104/212076 – KalenGi Mar 13 '13 at 22:58

2 Answers2

2

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks for your opinion. But i also try using "=" operator instead of like but it didn't worked. Is there a short way to solve my problem and i will get rid of nuqta1, nuqta2, nuqta3. Thanks for your advice. – Tahir Shahid Oct 26 '12 at 10:49
  • It seems likely that you don't quite understand what is in the meta_value column of wp_postmeta. You're obviously using a Wordpress plugin to handle this hotel-type stuff. Which plugin? What do you get when you use an SQL client to issue the following command? `SELECT * FROM wp_postmeta WHERE meta_key IN ('amenities', 'price', 'guests', 'roomtype')` Knowing how these wp_values are actually stored is a prerequisite to fixing up your query. – O. Jones Oct 26 '12 at 12:27
  • No i am not using any wordpress plugin. And also "SELECT * FROM wp_postmeta WHERE meta_key IN ('amenities', 'price', 'guests', 'roomtype')" this query will display all the results where meta_key equal to amenities or price or guests or roomtype but i want this query with "and logical operrator" not with "or logical operator". Thanks for your quick response. – Tahir Shahid Oct 26 '12 at 14:18
  • Tahir, what I'm trying to figure out is exactly what the values look like in your meta_value column. You've said you can't search for them with `=`. The question is, "why not?" In my wordpress instance the meta_value column has fairly clean values in it. What is different about yours? – O. Jones Oct 26 '12 at 17:24
  • Yes you are right "=" operator working fine but query taking 70 sec to show me the result. And here is few of my (meta_key , meta_value)
    ('guests', '20'), ('roomtype', 'private'), ('price', '$120'), ('bedrooms', '2'), ('bathrooms', '1') and so on...     – Tahir Shahid Oct 26 '12 at 21:50
  • Jones this query is also working fine "SELECT post_id FROM wp_postmeta WHERE (meta_key = 'guests' and meta_value = '20') or (meta_key = 'roomtype' and meta_value='private') or (meta_key = 'price' and meta_value='$120') order by post_id" but i want this query with "and operator" not with "or operator" – Tahir Shahid Oct 26 '12 at 21:59
0

Try using Fulltext Search

What you'll do is something like

SELECT
    *
FROM
    tableName
WHERE
    MATCH ( columnName ) AGAINST ( 'Keyword1', 'Keyword2', 'Keyword3' )
Kao
  • 2,242
  • 3
  • 22
  • 31