[annomatik]

Ok, having another look today... The performance for the _view tables seems to be quite bad for me (factor 15). Is this just happening for me or is this a general problem?

Code:

MariaDB [myvideos107]> select count(*) from movie;
+----------+
| count(*) |
+----------+
| 3124     |
+----------+
1 row in set (0.33 sec)

MariaDB [myvideos107]> select count(*) from movie_view;
+----------+
| count(*) |
+----------+
| 3101     |
+----------+
1 row in set (5.11 sec)

Also had a look regarding indexes. Yes, I have some, but the slow-downs I had where not covered by them. The one with movie_ is the one I created:

Code:

MariaDB [myvideos107]> show index from movie;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| movie | 0 | PRIMARY | 1 | idMovie | A | 2646 | NULL | NULL | | BTREE | | |
| movie | 0 | ix_movie_file_1 | 1 | idFile | A | 2646 | NULL | NULL | YES | BTREE | | |
| movie | 0 | ix_movie_file_1 | 2 | idMovie | A | 2646 | NULL | NULL | | BTREE | | |
| movie | 0 | ix_movie_file_2 | 1 | idMovie | A | 2646 | NULL | NULL | | BTREE | | |
| movie | 0 | ix_movie_file_2 | 2 | idFile | A | 2646 | NULL | NULL | YES | BTREE | | |
| movie | 1 | ixMovieBasePath | 1 | c23 | A | 240 | 12 | NULL | YES | BTREE | | |
| movie | 1 | movie_idSet | 1 | idSet | A | 882 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

MariaDB [myvideos107]>

Joining manually seems to be much quicker, about factor 7 (I didn't join everything here though):

Code:

MariaDB [myvideos107]> select c00, dateAdded, uniqueid_value from movie_view order by dateAdded desc limit 5;
+----------------------------------------------+---------------------+----------------+
| c00                                          | dateAdded           | uniqueid_value |
+----------------------------------------------+---------------------+----------------+
| Crank 2: High Voltage                        | 2018-11-12 19:59:09 | tt1121931      |
| Crank 1                                      | 2018-11-12 19:54:53 | tt0479884      |
| The Incredibles 1.8: Mr. Incredible and Pals | 2018-11-11 10:47:09 | tt0486141      |
| The Incredibles 1.3: Jack-Jack Attack        | 2018-11-10 23:23:51 | tt0455565      |
| Class of Nuke 'Em High 1                     | 2018-11-10 13:45:41 | tt0090849      |
+----------------------------------------------+---------------------+----------------+
5 rows in set (1.25 sec)

MariaDB [myvideos107]> select m.c00, f.dateAdded, r.rating_id, r.rating_type from movie m, files f, rating r where m.idFile=f.idFile and m.c05=r.rating_id order by dateAdded desc  limit 5;
+----------------------------------------------+---------------------+-----------+-------------+
| c00                                          | dateAdded           | rating_id | rating_type |
+----------------------------------------------+---------------------+-----------+-------------+
| Crank 2: High Voltage                        | 2018-11-12 19:59:09 |     71140 | themoviedb  |
| Crank 1                                      | 2018-11-12 19:54:53 |     71141 | themoviedb  |
| The Incredibles 1.8: Mr. Incredible and Pals | 2018-11-11 10:47:09 |     71143 | themoviedb  |
| The Incredibles 1.3: Jack-Jack Attack        | 2018-11-10 23:23:51 |     71142 | themoviedb  |
| Class of Nuke 'Em High 1                     | 2018-11-10 13:45:41 |     71144 | themoviedb  |
+----------------------------------------------+---------------------+-----------+-------------+
5 rows in set (0.18 sec)

MariaDB [myvideos107]>