Queries in mysql 5.7 2 or more times slower than i

We migrated a full database from MySQL 5.1.63 to a different (a bit better) server into MySQL 5.7.22. Now most of the queries are 2 times slower on average. We haven't done too much optimization for MySQL 5.1.

Here is the configuration which we have changed:

table_open_cache            = 4096
tmp_table_size=256M
max_heap_table_size=256M
query_cache_limit       = 1000000
query_cache_size        = 32000000
innodb_buffer_pool_size = 3200M
innodb_log_buffer_size  = 1024M

Here is one concrete example:

I want to get all the persons who are marked as a teacher in a course:

SELECT id, TRIM(CONCAT_WS(" ", name, lastname)) AS name
FROM person
WHERE id IN (SELECT DISTINCT teacher_id FROM course)
ORDER BY name;

The run time:

mysql 5.1: 0.03s. subsequent queries: 0.00s
mysql 5.7: 1.27s. subsequent queries: 0.80s

The difference is 40+ times.

EXPLAIN in 5.1:

+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+-------+-----------------------------+
| id | select_type        | table    | type           | possible_keys      | key                | key_len | ref  | rows  | Extra                       |
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+-------+-----------------------------+
|  1 | PRIMARY            | person   | ALL            | NULL               | NULL               | NULL    | NULL | 16293 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | course   | index_subquery | teacher_id         | teacher_id         | 5       | func |  2677 | Using index; Using where    |
+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+-------+-----------------------------+

EXPLAIN in 5.7:

+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys      | key                | key_len | ref                  | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+
|  1 | SIMPLE       | person      | NULL       | ALL    | PRIMARY            | NULL               | NULL    | NULL                 |  16491 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>         | <auto_key>         | 5       | db.person.id         |      1 |   100.00 | Using where |
|  2 | MATERIALIZED | course      | NULL       | index  | teacher_id         | teacher_id         | 5       | NULL                 | 109741 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+--------------------+--------------------+---------+----------------------+--------+----------+-------------+

Now if I write the query like that:

select distinct person.id, trim(concat_ws(" ", name, lastname)) as name 
from person, course
where person.id = course.teacher_id
order by name;

Times will become:

mysql 5.1: 0.01s
mysql 5.7: 0.03s

So, it's a bit better, but still slower.

course.teacher_id has an index.

One of the differences between the installations is that in the server with 5.7, data folder is on another drive (SSD, the performance is a bit better than for the drive in 5.1 server).

Any suggestions what should I configure to get 5.7 to the same speed as 5.1? Probably some of the queries have to be rewritten too, but I think configuration seems necessary.

You could use the better query otimization using an inner join

  SELECT id, TRIM(CONCAT_WS(" ", name, lastname)) AS name
  FROM person
  INNER JOIN (
    SELECT DISTINCT teacher_id FROM course
  ) t on  t.teacher_id = person.id 

  ORDER BY name;

and be sure that you test are do in the same condition .. same data .. and first execution for both query