"Using Temporary" with a Custom Wordpress Search

I currently use a custom wordpress MySQL query to get related products for some of my shopping sites. I've just profiled my pages and i've noticed that this query takes about 3 seconds, yet i'm unsure how to optimise it. The query is below:

explain select 
    p . *,
    unix_timestamp(p.post_modified) as post_modified_ut,
    unix_timestamp(p.post_date) as post_date_ut,
    (((2.3 * (MATCH (p.post_title) AGAINST ('Motorola+MBP+36+Digital+Video+Monitor' IN BOOLEAN MODE)))) + (0.6 * (MATCH (p.post_content) AGAINST ('Motorola+MBP+36+Digital+Video+Monitor' IN BOOLEAN MODE)))) AS relevance
from
    wp_posts as p,
    wp_terms as t,
    wp_term_taxonomy as tt,
    wp_term_relationships as tr
where
    (MATCH (p.post_title , p.post_content) AGAINST ('Motorola+MBP+36+Digital+Video+Monitor' IN BOOLEAN MODE))
        and tr.object_id = p.ID
        and tr.term_taxonomy_id = tt.term_taxonomy_id
        and tt.term_id = t.term_id
        and p.post_type = 'post'
        and p.post_status in ('inherit' , 'publish')        
group by p.ID , p.post_title
order by relevance desc
limit 5;

The result from the explain is:

+----+-------------+-------+--------+-------------------------------------------------+------------------+---------+------------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                                   | key              | key_len | ref                                | rows | Extra                           |
+----+-------------+-------+--------+-------------------------------------------------+------------------+---------+------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | tt    | ALL    | PRIMARY,term_id_taxonomy                        | NULL             | NULL    | NULL                               | 2822 | Using temporary; Using filesort |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                                         | PRIMARY          | 8       | reviewexplorer.tt.term_id          |    1 | Using index                     |
|  1 | SIMPLE      | tr    | ref    | PRIMARY,term_taxonomy_id                        | term_taxonomy_id | 8       | reviewexplorer.tt.term_taxonomy_id |    5 |                                 |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date,searches,searches_more | PRIMARY          | 8       | reviewexplorer.tr.object_id        |    1 | Using where                     |
+----+-------------+-------+--------+-------------------------------------------------+------------------+---------+------------------------------------+------+---------------------------------+

As you can see i'm using temporary tables and i dont want to, I'd like to create an index to speed up this query but i dont fully understand what the explain is telling me.

Solutions

Apparently, MySQL was not able to use any index on the wp_term_taxonomy table and have to examine+sort in temporary table all the 2822 rows. That being said, 2822 rows is not so large that it could explain such long query time. Is the load on DB or disk high? anyway...


By looking more closely to your query, it is very confusing. Apparently you have a wp_term_taxonomy.term_taxonomy_id and wp_term_relationships.term_taxonomy_id but a possible index is on wp_term_taxonomy.term_id_taxonomy.

See the difference? term_taxonomy_idterm_id_taxonomy.
Is this a typo? An error? A "feature"?

Similar questions

Search (Custom Form, Custom Search Result)
If I have a form: How would I add the custom page-search, The From Tutorial does not work. (Outputs nothing, it doesn't really seem to do anything). I tried too loop as well.. How do I process the form?
Custom search for custom post meta with pre_get_posts interferes with WP search
I'm making a custom post meta search with the pre_get_posts function in my Divi child theme functions.php file. The problem is that the final query gathers not only the results for my custom post field (named 'autor') but other fields like 'post_title' and 'post_post' which I have not included. That's the resulting query with the troublesome line h...
Custom Search on Custom Page while preserving the main search function
Wordpress - Custom Search on Custom Page while preserving the standard search function. Hi all, I would like to create a custom search on a custom page on top of main search function. The additional one is supposed to be focused only on products. search-product.php The form: When I define the post type within the php file, it displays the query as ...
Category Search / Custom Post Type search on my website. Custom Post Types that are "page-like"?
I am creating an educational website and some of my pages are "subjects" like math, physics etc and I want them to be searchable. How do I do this? I thought that it would be easy if I just made a custom post type for the subjects, and then a search form that only searches for that particular custom post type (the subjects). The problem is that I'm...
Cannot make custom search permalink to work in a fully custom theme. Search string $_GET['s'] is always empty
I am learning to build a fully custom WordPress theme. I have a search form which renders the following HTML: In my search.php template, I currently have the following code to check whether everything works as desired: When I run http://local.devsite.com/?s=sedan, I get the value of $_GET['s'] printed on the search page perfectly However, I want th...
Search from all custom-fields very slow. I need filter search for custom post type
I'm doing a custom search in my WP and in the search field, any typed word will be sought in custom-fields, in the titles and posting content. It turns out that my database has more than 1 million custom-fields lines, and wanted to limit the search only for a certain type of post. I already do this in the Loop to display the results, but I wish thi...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.