How to design a database for better performance?

Question on the part of the performance and use of indexes.
There is a table FILMS (id, title, rating)
In movies there are 2 types of filters: genre and country; Sootvetsvenno movie can have multiple genres and a few countries. When you filter should look for films where there is at least one match pair of genre and country.
From 2 ways to structure implementation:
1) Create 2 tables with the GENRE COUNTRY and 2 staging tables. If you ask to search knowing the ID of countries and genres, search only for the staging table:
SELECT * FORM films f 
Genre_film JOIN g ON(g.film_id = f.id ) WHERE g.genre_id IN(1,2,3)
Country_film JOIN c ON(c.film_id = f.id ) WHERE c.country_id IN(1,2,3)


2) Make one table PARAMS with a TYPE column which will indicate what kind of a setting (country or genre) and 1st intermediate table. There may still be appropriate composite index on film_id and fields type.
Here, too, the search only about the staging table.
SELECT * FORM films f 
JOIN param_film p ON(p.film_id = f.id ) 
WHERE (p.param_id IN(1,2,3) AND p.type = `country`) AND (p.param_id IN(4,5,6) AND p.type = `genre`)

the column TYPE is not in the staging table, pointed it in the query to simplify the example, because the filter probably will involve the main table with parameters.
March 19th 20 at 09:15
3 answers
March 19th 20 at 09:17
The first will be faster almost certainly.
But ideally, it would be necessary to conduct tests on the data about the volume, what you will do in the end.
March 19th 20 at 09:19
1 will be faster if the where is the comparison with the attributes of the table films
March 19th 20 at 09:21
The options are one and the same, and that the performance will be about the same.
Only in the second embodiment, it is necessary to use, of course, not text comparison - use the list of countries and genres, search by ID and countries and genres.
I would choose the second option, because the tables less. But in the first embodiment, the queries will be more clear.
Query performance varies 1000 times from the introduction of just a single composite index, because the number of aisles is reduced, and you're comparing two completely different approaches. - America_Collier5 commented on March 19th 20 at 09:24
@America_Collier5, You think that the approaches are different. 1 just in case you have 2 tables with parameters, and 2 1.
On account of the indexes I'm aware of.
I saw in the work and the other option. - lawrence commented on March 19th 20 at 09:27

Find more questions by tags Database designMySQL