我不知道如何管理 mysql 数据库表索引,并且我尝试实现它,但我不确定如何管理这个广泛的 MySQL 数据库表的索引(将在我最后完成,但只需要一些与此表有关的信息)。
我使用不同的查询并根据搜索需要使用此字段:id
、post_id
、imdbRating
、genres
、year
、cast
和posterURLs
。
较慢的查询可以清楚地了解我为什么要使用索引。
/* 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
我已经在表上有一个复合索引posts
,它使用这些索引idx_type_status_date: (type, status, date, id)
及其各自的顺序。但是,我想知道是否也可以在电影表上创建单独的索引?