very slow query with ORDER BY

I have read through many tutorials online and here on stackoverflow but I still can't figure out how to solve the problem I'm facing right now.

I would like to tell you guys that I'm a mysql newbie so please forgive my noobness.

Alright, the query is this and it grabs the information that I need from wordpress database

SELECT 
  product.ID productId, 
  product.guid productLink,
  product.post_title productTitle,
  post.ID postId,
  post.post_title postTitle,
  post.post_content postContent,
  post.post_date postDate,
  tm.slug typeSlug, tm.name typeName,
  tm2.slug langSlug, tm2.name langName,
  tm3.slug pubSlug, tm3.name pubName,
IFNULL(wl.id,0) wishlist
FROM wp_posts product
    JOIN wp_postmeta meta ON meta.meta_key = 'p2m' AND meta.meta_value=product.ID
    JOIN wp_posts post ON post.ID = meta.post_id

JOIN wp_term_relationships tr ON tr.object_id = product.ID
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id  = tr.term_taxonomy_id AND tt.taxonomy = 'mtype'
JOIN wp_terms tm ON tm.term_id = tt.term_id

JOIN wp_term_relationships tr2 ON tr2.object_id = product.ID
JOIN wp_term_taxonomy tt2 ON tt2.term_taxonomy_id  = tr2.term_taxonomy_id AND tt2.taxonomy = 'language'
JOIN wp_terms tm2 ON tm2.term_id = tt2.term_id

JOIN wp_term_relationships tr3 ON tr3.object_id = product.ID
JOIN wp_term_taxonomy tt3 ON tt3.term_taxonomy_id  = tr3.term_taxonomy_id AND tt3.taxonomy = 'publisher'
JOIN wp_terms tm3 ON tm3.term_id = tt3.term_id

LEFT JOIN wp_yith_wcwl wl ON wl.user_id = 1 AND wl.prod_id = product.ID AND wl.post_id = post.ID


WHERE product.post_type = 'product'

ORDER BY post.post_date DESC LIMIT 0,35

When I remove "ORDER BY post.post_date DESC" the speed of the query gets down to .03 seconds which is freaking amazing.. But with the addition of the "ORDER BY post.post_date DESC" the speed of the query goes to amazing 10+ seconds which is way too long..

I've used EXPLAIN and it seems that there is usage of filesort when the ORDER BY by date gets into the query.

I need to have my query reply back the results according to the post_date so I can't figure out what I could do at this point...

Additionally, I would like to point it out that in Database Description of wordpress there is an INDEX referred as "type_status_date" which could be used in my case. However, I'm totally clueless where to use it and how to do it. If there is anyone who can point out the flaw in the logic of my query or help me out with the optimization of the query (or index) please do so. Thanks for you kind attention!

P.S: I don't know how to create an index too :)

Initial Result of EXPLAIN with ORDER BY

Solution

    JOIN wp_postmeta meta
       ON meta.meta_key = 'p2m'       -- filters
      AND meta.meta_value=product.ID  -- shows relation

is confusing. JOIN...ON is used to say how two tables are related. Filters belong in WHERE:

    WHERE ...
      AND meta.meta_key = 'p2m'
      ...

wp_postmeta is not well indexed. More discussion here .

Adding INDEX(post_date) may or may not help performance -- It depends on how quickly 35 good rows are found.

From the EXPLAIN, we see that the worst part is getting into meta -- something like 30K rows to look through. This _estimates that there are 30 rows with meta_key = 'p2m'. How many rows are there?

Unfortunately wp_postmeta is not designed to efficiently start with the meta_key+meta_value. This is a general problem with key-value stores (such as Posts in WP), especially when the 'value' is LONGTEXT.