How to manage mysql database table index

Question

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: id, post_id, imdbRating, genres, year, cast, and posterURLs.

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?

Answer 1

Assuming that id uniquely specifies the row, then you should have

PRIMARY KEY(id)

For the numeric (and date, etc) columns, simply have

INDEX(post_id),
INDEX(imdbRating),
INDEX(year)

and use things like

WHERE year = 1970
  AND imbdRating >= 4

For the TEXT fields, use FULLTEXT:

FULLTEXT(genres),
FULLTEXT(cast),
FULLTEXT(posterURLs)

and

WHERE MATCH(cast) AGAINST('Brando')

There are possibly better ways to do genre. How many different values are there? Do you need to test for one only? Or many, such as

WHERE MATCH(genres) AGAINST('drama romance')

On a side note, what does age mean? If it refers to how many years ago the movie came out, that is a bad way to store the info because all entries change every year. Instead, test on year.

Some queries are likely to involve multiple tests. If you have a million movies, some such queries will likely be slow. Come back with some specific, slow, queries for further refinement -- as in "composite" indexes and/or combining columns in FULLTEXT.

More (after query, etc, were added to the Question)

ORDER BY mo.year DESC, pd.date DESC -- No index can be used for this since it involves two tables.

WHERE ... tr.term_id IN (...) turns the LEFT JOIN into INNER JOIN. (This confuses the reader.) If the "genres check" is optional, remove both the JOIN and the AND .. IN. That is, dynamically build the query based on the arguments that the user provided.

GROUP BY pd.id implies that you can somehow get multiple rows from the joins. This is the "explode-implode syndrome" that can be avoided by (and sped up by) something like

SELECT ...
    FROM ( SELECT ...  -- as little as possible
               FROM ... JOIN ... 
               WHERE ...
               ORDER BY ...
               LIMIT ... ) AS x
    JOIN ... -- to whatever was not picked up in `x`.

(Sorry, but I got lost in your query, so I hesitate to spell it out completely for you.)

The EXISTS could be done using INNER JOIN. It may not matter which way. (Or it may work better.)

This may help:

pd:  INDEX(type, status)

If pd.type always tracks mo.type, then an extra check may be avoidable.

More 2

A composite index to have on tr: INDEX(post_id, term_id). Furthermore, if that table is simply a many-to-many mapping then toss it's id and have:

PRIMARY KEY(post_id, term_id),
INDEX(term_id, post_id)

More discussion: Index Cookbook It discusses how to design indexes based on queries.

After tossing EXISTS

      pd.id = mo.post_id 
      mo.type = "movie" /* checks movies / shows */ /* platforms check */
      mo.streamingInfo REGEXP "jio|zee5|voot" /* genres check */

needs

mo:  INDEX(post_id, type, streamingInfo)  -- in this order

And

    FROM  movies
    WHERE  cast REGEXP "(Cast Title)"
      AND  post_id <> 123456
      AND  post_id <> 0
    ORDER BY  year DESC

may benefit from

INDEX(cast)

or it may need

MATCH(cast) AGAINST("cast title")
FULLTEXT(cast)

Github

Query 1: If none of the filters are present and the two types are redundant, then `INDEX(type, status) could be useful.. (Either order for the columns).

term_relation:  INDEX(obj_id, tax_id)  -- in this order

Query 2: INDEX(post_id)

Query 3: If FULLTEXT and MATCH would work that is the way to go.

Query 4: I don't see the JOIN to table sd

Things that prevent further optimizations:

• ORDER BY mentioning two tables
• No obvious way to find the LIMIT rows before reaching into all the tables.
• REGEXP
• Filtering on inside TEXT or JSON.

Answer 2

Whatever GUI you are using to manage your database should have some way to add an index.

Using sql

To create a unique index

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

To allow duplicates

CREATE INDEX index_name
ON table_name (column1, column2, ...);

How to decide which columns to index

• Start by adding indexes to columns that are central in your queries and that will return large matching rows. Possibly any column that has id in its name.
• Then fine tune to improve the speed of critical queries.

Answer 3

Although the post by Rick James gives a quick answer.

But, directly jumping to implementing INDEXes (especially by a novice) can be dangerous and can degrade insert-performances significantly.

Indexes are double-ended sword, should be used with caution.

You can learn more at https://use-the-index-luke.com/