I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end but just need some information to do with this table).
I am using different queries and use this fields as needed for search:
Slower queries to get clear ideas that why i want to use indexing.
/* QUERY 1 using for search filters, filter with tr.tax_id, pd.type or mo.streamingInfo (slower) */ 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 (skippable) */ WHERE pd.type = 'movies' /* Used with filters (skippable) */ AND pd.status = 1 AND mo.type = "movie" AND mo.streamingInfo REGEXP "jio|zee5|voot" /* Used with filters (skippable) */ AND tr.tax_id IN (123456,78910,111213) /* Used with filter (skippable) */ 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 2 only from movie table (slower) */ SELECT * FROM movies WHERE post_id='123456'; /* Resolved with INDEX(post_id) */ SELECT post_id FROM movies WHERE cast REGEXP "(Cast Title)" AND post_id <> 123456 AND post_id <> 0 ORDER BY year DESC LIMIT 12
I already have a composite index on the
posts table, which using these index
idx_type_status_date: (type, status, date, id) with their respective orders. However, I'm wondering if it's also possible to create a separate index on the movies table?