Slower MySQL queries involving both wp and custom table

Question

Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the inclusion of post_id in a dedicated movies table to accommodate additional data. Is there anyone assist in optimizing the performance of the MySQL queries?

/* QUERY 1: (If Remove WHERE filters, approx. time 0.3000ms) */
SELECT pd.id, pd.title, pd.slug, mo.posterURLs, mo.imdbRating
FROM posts pd
INNER JOIN movies mo ON (pd.id = mo.post_id) 
INNER JOIN term_relation tr ON (pd.id = tr.obj_id) /* Used with filter (Can be removed) */
WHERE pd.type = 'movies'
  AND pd.status = 'publish'
  AND mo.type = "movie"  /* Used with filters (Can be removed) */
  AND mo.streamingInfo REGEXP "jio|zee5|voot" /* Used with filter (Can be removed) */
  AND tr.tax_id IN (123456,78910) /* Used with filter (Can be removed) */
GROUP BY pd.id
ORDER BY mo.year DESC, pd.date DESC /* Can be mo.year DESC or mo.imdbRating ASC / DESC  */
LIMIT 0, 24;

/* QUERY 4 for getting movies by genres (approx. time 0.01400ms) */
SELECT pd.ID, pd.post_title, pd.guid, mo.posterURLs, mo.imdbRating
FROM posts pd
INNER JOIN movies mo
ON pd.id = mo.post_id
INNER JOIN term_relation tr
ON pd.id = tr.obj_id
WHERE pd.type = 'movie'
AND pd.status = 1
AND tr.tax_id IN (123456)
GROUP BY pd.id
ORDER BY mo.imdbRating DESC, pd.date DESC
LIMIT 0, 30
-- OR --
SELECT pd.ID, pd.post_title, pd.guid, mo.posterURLs, mo.imdbRating
FROM posts pd
INNER JOIN movies mo
ON pd.id = mo.post_id
INNER JOIN term_relation tr
ON pd.id = tr.obj_id
INNER JOIN term_taxonomy tt 
ON (tr.tax_id = tt.tax_id)
INNER JOIN terms t 
ON (tt.term_id = t.term_id)
WHERE 1=1 
AND pd.type = 'movies'
AND pd.status = 'publish'
AND t.term_id IN (123456)
GROUP BY pd.id
ORDER BY mo.imdbRating DESC, pd.date DESC
LIMIT 0, 30

For a more detailed explanation of the result from the "EXPLAIN" statement, the creation of table structures, and sample row data, please follow Gist here.

Answer 1

pd:  INDEX(type, status, id)
mo:  INDEX(type, post_id)
tr:  INDEX(tax_id, obj_id)  -- see below
tt:  INDEX(tax_id,  term_id)
tt:  INDEX(term_id, tax_id)   -- (not obvious which direction is best)
t:  INDEX(term_id)   -- unless already the `PRIMARY KEY`

Mixing tables in ORDER BY, such as

ORDER BY  mo.imdbRating DESC, pd.date DESC

inhibits use of indexing for that part of the query.

IN is optimized the same as = when there is only one element. When there are multiple IN values, performance is likely to suffer.

If term_relation is a many-to-many relationship, then the optimal indexing does not need an id for itself; instead:

PRIMARY KEY(tax_id, obj_id),
INDEX(obj_id, tac_id)

What is in terms? I ask because that may be an example of "over-normalization".

There is little use in indexing the object of a REGEXP.

For further discussion, pleas provide SHOW CREATE TABLE for each table.