CODEWITHSUNDEEP
mysqlsqlindexingblobsql-like
Jov
Jov

Reputation: 19

indexing for speeding up reading | mysql

i've been asked the following question:

For each of the following queries, design one or more indexes that would speed it up assuming the table(s) had enough rows. For each query submit SQL statements that add the index(es) to the relevant table(s) in alikas.

a. SELECT film_id, description FROM film WHERE description LIKE 'A B ';

b. SELECT film_id, description FROM film_text WHERE description LIKE 'A B ';

c. SELECT f.film_id, f.title, a.last_update FROM film AS f INNER JOIN film_actor AS fa ON f.film_id = fa.film_id INNER JOIN actor AS a ON fa.actor_id = a.actor_id WHERE f.rating = 'G' AND a.last_update `>= '2006-02-15 04:34:33';

I tried to create an index on description column but got an error code 1770 : BLOB/TEXT column 'description' used in key specification without a key length

is it the right column to index for that purpose ? if so, how do I solve the problem? if not, what is the right column to index?

table is set by

CREATE TABLE `film` (
 `film_id` smallint unsigned NOT NULL AUTO_INCREMENT,

 `title` varchar(255) NOT NULL,

 `description` text,

 `release_year` year DEFAULT NULL,

 `language_id` tinyint unsigned NOT NULL,

 `original_language_id` tinyint unsigned DEFAULT NULL,

 `rental_duration` tinyint unsigned NOT NULL DEFAULT ''3'',

 `rental_rate` decimal(4,2) NOT NULL DEFAULT ''4.99'',

 `length` smallint unsigned DEFAULT NULL,

 `replacement_cost` decimal(5,2) NOT NULL DEFAULT ''19.99'',

 `rating` enum(''G'',''PG'',''PG-13'',''R'',''NC-17'') DEFAULT ''G'',

 `special_features` set(''Trailers'',''Commentaries'',''Deleted Scenes'',''Behind the Scenes'') DEFAULT NULL,

 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

 PRIMARY KEY (`film_id`),

 KEY `idx_title` (`title`),

 KEY `idx_fk_language_id` (`language_id`),

 KEY `idx_fk_original_language_id` (`original_language_id`),

 CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

 CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb3

thank you!

Upvotes: 0

Views: 64

Answers (1)

Rick James
Rick James

Reputation: 142218

Query 3 is a bit tricky.

The Optimizer will probably ponder whether to start with f.rating = 'G' or with a.last_update >= '2006-02-15 04:34:33'. It will decide based on limited knowledge about the distribution of data in the tables fanda`.

If it starts with f, such as when the requested rating occurs in very few rows:

f:  INDEX(rating)
fa:  INDEX(film_id,  actor_id)
a:  INDEX(last_update)

If it starts with a, such as when the date range is narrow:

a:  INDEX(last_update, actor_id)
fa:  INDEX(actor_id, film_id)
f:  INDEX(rating, film_id)

Combining them, I recommend:

f:  INDEX(rating, film_id)       -- (handles both cases)
fa:  INDEX(film_id, actor_id)
fa:  INDEX(actor_id, film_id)    -- (yes both orderings)
a:  INDEX(last_update, actor_id)
a:  INDEX(actor_id, last_update) -- (yes both orderings)

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Upvotes: 1

Related Questions