Базы одинаковые скопированы через mysqldump.
Есть запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT lpu_group1.id, lpu_group1.name_group, SUM(IF(sel1.locality=13,1,0)) AS col32,
SUM(IF(sel1.locality=14,1,0)) AS col33, SUM(IF(sel1.sex=1,1,0)) AS col34,
SUM(IF(sel1.sex=2,1,0)) AS col35, SUM(IF(sel1.sex=2 OR sel1.sex=1 OR sel1.sex=3,1,0)) AS col31
FROM lpu_group1
LEFT JOIN (
SELECT live.lpuid, geo.codOut, live.locality, live.sex, IF(t.name_group IS NULL,0,t.name_group),
IF(t.id IS NULL, 35,t.id) AS grid
FROM live, geo
LEFT JOIN lpu_group1 t ON (geo.codOut LIKE CONCAT(t.codOut,"%"))
WHERE geo.uniCod = live.residence AND live.date_birth >= '2015-01-01'
AND live.date_birth <= '2015-05-08 23:59:59' AND live.del=0
) AS sel1 ON (sel1.grid = lpu_group1.id)
GROUP BY lpu_group1.id
ORDER BY lpu_group1.num, lpu_group1.name_group
Вот выводы команды explain от этого запроса.
10.0.17-MariaDB
1.
2.
3.
4.
5.
6.
7.
8.
+------+-------------+------------+--------+----------------------+---------+---------+---------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+--------+----------------------+---------+---------+---------------------+-------+---------------------------------+
| 1 | SIMPLE | lpu_group1 | index | NULL | PRIMARY | 4 | NULL | 29 | Using temporary; Using filesort |
| 1 | SIMPLE | live | ALL | residence,date_birth | NULL | NULL | NULL | 51878 | Using where |
| 1 | SIMPLE | geo | eq_ref | PRIMARY | PRIMARY | 4 | baby.live.residence | 1 | |
| 1 | SIMPLE | t | index | NULL | codOut | 54 | NULL | 29 | Using where; Using index |
+------+-------------+------------+--------+----------------------+---------+---------+---------------------+-------+---------------------------------+
Мysql 5.6.24
1.
2.
3.
4.
5.
6.
7.
8.
9.
+----+-------------+------------+--------+----------------------+-------------+---------+---------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------+-------------+---------+---------------------+------+----------------------------------------------------+
| 1 | PRIMARY | lpu_group1 | index | PRIMARY,codOut | PRIMARY | 4 | NULL | 29 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 9 | baby.lpu_group1.id | 2158 | Using where |
| 2 | DERIVED | live | range | residence,date_birth | date_birth | 6 | NULL | 7442 | Using index condition; Using where |
| 2 | DERIVED | geo | eq_ref | PRIMARY | PRIMARY | 4 | baby.live.residence | 1 | NULL |
| 2 | DERIVED | t | ALL | NULL | NULL | NULL | NULL | 29 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+--------+----------------------+-------------+---------+---------------------+------+----------------------------------------------------+
И естественно на mysql этот запрос выполняется за 0,08 сек, а на mariadb 4.66 сек
Почему такая ерунда и что можно сделать? Перешли на mariadb и такие проблемы.
Базы и ключи полностью одинаковые. Комп под mariadb намного мощнее, чем под mysql.
