0

I have the following table:

CREATE TABLE `CustomerRatings` (
  `movie_id` int NOT NULL,
  `category_id` int NOT NULL,
  `customer_score` double NOT NULL,                                                                                                                                                 
  `num_of_theaters` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`movie_id`,`category_id`)
)

I'd like to be able to do efficient queries having num_of_theaters as part of the WHERE condition (along with movie_id and category_id).
I think that if I add a new index as follows:

`KEY (`movie_id`,`category_id`,`num_of_theaters`)

that should help but I am not sure if the cardinality of the columns matter in this case.
The category_id has much lower cardinality (very small range of values) than num_of_theaters. Would that affect the index and should the order of the cols in the index definition be different? Or is there another more efficient approach?

Note: In case it matters the most common condition would be num_of_theaters > 0 or num_of_theaters <> 0

Jim
  • 3,845
  • 3
  • 22
  • 47
  • *I'd like to be able to do efficient queries having num_of_theaters as part of the WHERE condition* , then the index will not be used https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html#:~:text=MySQL%20can%20use%20multiple%2Dcolumn,three%20columns%2C%20and%20so%20on. – Ergest Basha Oct 25 '22 at 19:45
  • @ErgestBasha: Why it won't be used? The way I read the highlighted statement I understand it is used. What am I messing up on this? Is it because it is similar to PK index? – Jim Oct 25 '22 at 19:49
  • at the page a linked you have a detailed explanation. The order of columns in the index matter – Ergest Basha Oct 25 '22 at 19:55
  • Why `DOUBLE`? It sounds like an integral value. Furthermore, it sounds like a non-negative value. – Rick James Oct 26 '22 at 17:51
  • We _must_ see the rest of the query -- it matters whether you are saying `movie_id=123` or `movie_id IN (...)` or `movie_id > 0` The answer may be different in each case! – Rick James Oct 26 '22 at 17:54
  • See also https://stackoverflow.com/questions/50239658/higher-cardinality-column-first-in-an-index-when-involving-a-range – Rick James Oct 26 '22 at 17:56

0 Answers0