使用LIKE语句时MySql查询挂起 [英] MySql Query Hangs when using LIKE statement

问题描述

我正在尝试在Wordpress中设置警报，以便用户可以创建警报，以便在创建的新帖子(属性)与他们的设置标准(警报)匹配时收到通知.我的计划是使用两种帖子类型来执行此操作，一种用于属性，另一种用于警报.然后，在保存属性帖子后，它将执行以下示例查询:

I am trying to set up alerts within Wordpress so that users can create alerts so that they will be notified when a new post (property) that is created matches their set criteria (alerts). My plan was to do this using two post types one for properties and one for the alerts. Then when a property post is saved it will perform the following example query:

SELECT 
    post_author
    FROM 
        wp_posts
    INNER JOIN wp_postmeta m1
        ON wp_posts.ID = m1.post_id
        AND m1.meta_key = 'a_property_type'
        AND m1.meta_value = 'flat'
    INNER JOIN wp_postmeta m2
        on wp_posts.ID = m2.post_id
        AND m2.meta_key = 'a_bedrooms_min' 
        AND m2.meta_value <= '2'
    INNER JOIN wp_postmeta m3
        on wp_posts.ID = m3.post_id
        AND m3.meta_key = 'a_bedrooms_max' 
        AND m3.meta_value >= '2'
    INNER JOIN wp_postmeta m4
        on wp_posts.ID = m4.post_id
        AND m4.meta_key = 'a_bathrooms_min' 
        AND m4.meta_value <= '2'
    INNER JOIN wp_postmeta m5
        on wp_posts.ID = m5.post_id
        AND m5.meta_key = 'a_bathrooms_max' 
        AND m5.meta_value >= '2'      
    INNER JOIN wp_postmeta m6
        on wp_posts.ID = m6.post_id
        AND m6.meta_key = 'a_garden' 
        AND m6.meta_value >= '1'        
    INNER JOIN wp_postmeta m7
        on wp_posts.ID = m7.post_id
        AND m7.meta_key = 'a_garage' 
        AND m7.meta_value >= '1'    
    INNER JOIN wp_postmeta m8
        on wp_posts.ID = m8.post_id
        AND m8.meta_key = 'a_parking' 
        AND m8.meta_value >= '1'        
    INNER JOIN wp_postmeta m9
        on wp_posts.ID = m9.post_id
        AND m9.meta_key = 'a_double_glazing' 
        AND m9.meta_value >= '1'    
    INNER JOIN wp_postmeta m10
        on wp_posts.ID = m10.post_id
        AND m10.meta_key = 'a_country' 
        AND m10.meta_value LIKE 'Scotland'        
    WHERE
        wp_posts.post_type = 'alerts'
        AND wp_posts.post_status = 'publish'
    GROUP BY 
       wp_posts.ID
    ORDER BY 
        wp_posts.post_date DESC

这有效并且返回与条件集匹配的属性，但是当我引入LIKE时，它确实使查询挂起.我正在使用默认的Wordpress表'wp_posts'和'wp_postmeta'.我曾尝试在"meta_value"列上设置索引，但因为它是"longtext"字段，所以不允许我这样做.

This works and does return the properties matching the criteria set however when I introduce a LIKE it really makes the query hang. I am working with the default Wordpress tables 'wp_posts' and 'wp_postmeta'. I have tried setting an index on the column 'meta_value' but because its a 'longtext' field it wont allow me to.

是否有更好的解决方案，或者可以更高效地编写我的查询?

Is there a better solution to do this or can my Query be written more efficiently?

谢谢

推荐答案

除了eggyal提供的功能外，我也将添加一个索引(post_id，meta_key，meta_value).如果那对您的查询没有帮助，请尝试另一件事.将除LIKE之外的所有元素预先包装到预查询中，以便首先处理内部查询并返回一小组非常小的ID，然后加入LIKE条件.

Aside from what eggyal offered, I too would add an index on (post_id, meta_key, meta_value). If that does not help your query, I would TRY one other thing. Pre-wrap all elements EXCEPT the LIKE into a prequery such that the inner query gets processed first and returns a very small set of IDs, THEN join to the LIKE condition.

让我们说您有1000处房产，但只有35处符合您的所有其他条件，并且在不到一秒的时间内得到解决.现在，您只有35条记录会被尝试满足其他附加条件.像

Lets say you have 1000 properties, but only 35 qualify with all your other criteria and is resolved in sub-second time. Now you have only 35 records that will be ATTEMPTED to your additional LIKE condition. Something like

select
      wp.Post_Author
   from
      ( SELECT 
              post_id,
              post_author
           FROM 
              wp_posts
                 INNER JOIN wp_postmeta m1
                    ON wp_posts.ID = m1.post_id
                   AND m1.meta_key = 'a_property_type'
                 INNER JOIN the m2 through m9 
           WHERE
               type and status criteria ) as PreQuery
      INNER JOIN your m10 version
         on  PreQuery.Post_ID = m10.post_id
        AND ... rest of join condition

这篇关于使用LIKE语句时MySql查询挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！