Very slow search

  • Hi !

    I currently have around 33000 posts (with specific post type) with all having less than 10 words in excerpt or content.
    When I try a search, it takes around 1mn30 to load 3 poor results… I don’t understand why the query is so slow.
    I looked at mysql processlist and found it’s copying in tmp table. So I created a tmp dir as tmpfs, but it’s still very slow.
    The query contains some join, but it’s probably normal…
    Here it is :

    SELECT DISTINCT   wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)  INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) LEFT JOIN wp_options ON wp_options.option_name LIKE CONCAT('_ait_directory2-child_elements_opts_page_', wp_posts.ID) WHERE 1=1  AND (
  wp_term_relationships.term_taxonomy_id IN (305)
  AND
  tt1.term_taxonomy_id IN (305)
) AND (((((wp_posts.post_title LIKE '%carcassonne%') OR ( wp_posts.post_content LIKE '%carcassonne%'))) OR ((option_value LIKE '%carcassonne%')) ))  AND wp_posts.post_type = 'ait-item' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE '%carcassonne%' DESC, wp_posts.post_date DESC

    Anyone have an idea for me to speed up my search result page display ?

    thanks for help !

Viewing 3 replies - 1 through 3 (of 3 total)
  • jack randall

    (@theotherlebowski)

    have you applied any kind of taxonomies to this amount of content? 33,000 posts IS A LOT! if each post has 10 words that 330,000 words to scan over in the database then it has to import what it finds into the html to display it so if you’ve got slow or limited hosting, a bloated theme, lots of plugins and overheads then you’re basically putting a huge stress on your db.

    using some taxonomies would help, using tags and categories effectively would help as then your search query would be saying “find me ‘a’ in place ‘b’ or group ‘c’ rather than saying “find me x in all of the 330,000 words in the posts table”…

    Thread Starter antonyvk

    (@antonyvk)

    My need is just to find a word in post titles (post titles corresponds to city names, so between 1 and 3 words in each title). So I’d like to disable the params that build the query looking in content and option_value. I also don’t understand why it joins to wp_term_relationships twice (seems to be useless !).

    Thread Starter antonyvk

    (@antonyvk)

    Good idea to ask about the theme ! This part is due to it : wp_options.option_name LIKE CONCAT('_ait_directory2-child_elements_opts_page_', wp_posts.ID)
    And when I try the query without it it’s really faster (0.12 sec in MySQL).
    Now I just have to find how to disable it ! ??

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Very slow search’ is closed to new replies.