I'm migrating and database from mysql to mariadb. the ruby on rails app is doing the following query using cancan so I cannot change the query:
SELECT DISTINCT COUNT(DISTINCT `buildings`.`id`)
FROM `buildings`
INNER JOIN `structure_subtypes`
ON `structure_subtypes`.`id` = `buildings`.`structure_subtype_id`
WHERE `buildings`.`id` IN (33553, 33554, 33555, 33556, 33557,
33558, 33559, 33560, 33561, 33562, 33563, 33564, 33565,
33566, 33567, 33568, 33569, 33570, 33571, 33572, 33573,
33574, 33575, 33576,+ ,..., 439515, 439521, 439600, 439602,
439604, 440016, 440017, 440019, 440275, 440315, 440379,
440403, 440405, 412987, 439889, 439969, 439980, 440216,
440218, 440341, 440485, 440486, 440488, 440491, 440494,
440501, 441458, 441498, 441519, 441536, 443387, 443389,
443464, 433752, 440109, 440110, 440464, 443938, 440513,
440514, 443391, 443394, 443353, 443364, 443401, 443486,
175036, 175037
)
AND `buildings`.`client_id` IN (175, 47 , 162, 152, 170,
104, 90, 127, 101, 51, 163, 81, 164, 165, 166, 172, 137,
174, 106, 108, 161, 158, 169, 97, 123, 136, 102, 157,
167, 135, 105, 171, 180, 120, 119, 118, 121, 110, 59,
57, 178, 140, 138, 176, 141, 168, 126, 96, 117, 103, 133,
173, 131, 179, 80, 100, 95, 116, 142, 147, 159, 160, 154,
115, 153, 156, 91, 125, 144, 150, 93, 155, 149, 151, 146
)
AND `structure_subtypes`.`structure_type_id` IN (11, 12,
13, 14, 15
)
AND (buildings.created_at >= '2016-12-31 23:00:00')
AND (buildings.created_at <= '2017-12-31 22:59:59');
The problem seems to be so many items in the IN section
MySQL "IN" operator performance on (large?) number of values
but the problem is de difference in performance between MYSQL and mariadb
for MYSQL it takes less than 10 seconds
$ mysql -h 127.0.0.1 -u root -pPASSWORD database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [database]> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)
MySQL [database]> Bye
$ mysql -h 127.0.0.1 -u root -pPASSWORD database < select_query.sql
RESULTS
ouput explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE buildings range PRIMARY,fk__buildings_client_id,index_buildings_on_workflow_state,fk__buildings_structure_subtype_id,index_buildings_on_workflow_state_and_created_at,index_buildings_on_client_id_and_created_at
index_buildings_on_workflow_state_and_created_at 258 NULL 1001 Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE structure_subtypes eq_ref PRIMARY,index_structure_subtypes_on_structure_type_code,fk__structure_subtypes_structure_type_id PRIMARY 4 antifraud.buildings.structure_subtype_id 1 Using where; Distinct
output describe table
MySQL [antifraud]> describe buildings;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| client_id | int(11) | YES | MUL | NULL | |
| observations | varchar(2000) | YES | | NULL | |
| created_at | datetime | NO | MUL | NULL | |
| updated_at | datetime | NO | | NULL | |
| workflow_state | varchar(255) | YES | MUL | NULL | |
| structure_subtype_id | int(11) | YES | MUL | NULL | |
| svs | tinyint(1) | YES | | NULL | |
| parent_id | int(11) | YES | MUL | NULL | |
| reference | varchar(255) | YES | | NULL | |
| soc_notify | tinyint(1) | YES | | NULL | |
| origin | int(11) | YES | | NULL | |
| category | int(11) | YES | | NULL | |
| ip_filtering | int(11) | YES | | NULL | |
| priority | int(11) | YES | | NULL | |
| creator_id | int(11) | YES | MUL | NULL | |
| external_id | varchar(255) | YES | | NULL | |
| duration_time | float | YES | | NULL | |
| reopening_at | datetime | YES | MUL | NULL | |
| closed_at | datetime | YES | | NULL | |
| cbs_detection_id_legacy | varchar(255) | YES | | NULL | |
| cbs_callback_legacy | varchar(255) | YES | | NULL | |
| load_percentage | float | YES | | NULL | |
| items_in_special_domains | tinyint(1) | NO | | 0 | |
+--------------------------+---------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)
..
MySQL [antifraud]> show index from buildings;
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buildings | 0 | PRIMARY | 1 | id | A | 72567 | NULL | NULL | | BTREE | | |
| buildings | 1 | fk__buildings_client_id | 1 | client_id | A | 82 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_structure_subtype_id | 1 | structure_subtype_id | A | 78 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_parent_id | 1 | parent_id | A | 4535 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_creator_id | 1 | creator_id | A | 168 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 2 | created_at | A | 72567 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_created_at | 1 | created_at | A | 72567 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_reopening_at | 1 | reopening_at | A | 806 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 1 | client_id | A | 80 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 2 | created_at | A | 72567 | NULL | NULL | | BTREE | | |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)
MySQL [antifraud]> Bye
for MariaDB it takes 1 minute 10 seconds
$ mysql -h 127.0.0.1 -u root -pPASSWORD database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.26-MariaDB-1~jessie mariadb.org binary distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [database]> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)
MariaDB [database]> Bye
$ mysql -h 127.0.0.1 -u root -pPASSWORD database < select_query.sql
ouput explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE buildings range PRIMARY,fk__buildings_client_id,index_buildings_on_workflow_state,fk__buildings_structure_subtype_id,index_buildings_on_workflow_state_and_created_at,index_buildings_on_client_id_and_created_at index_buildings_on_workflow_state_and_created_at 258 NULL 1001 Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE structure_subtypes eq_ref PRIMARY,index_structure_subtypes_on_structure_type_code,fk__structure_subtypes_structure_type_id PRIMARY 4 antifraud.buildings.structure_subtype_id 1 Using where; Distinct
..
output describe table
MariaDB [antifraud]> describe buildings;
+--------------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| client_id | int(11) | YES | MUL | NULL | |
| observations | varchar(2000) | YES | | NULL | |
| created_at | datetime | NO | MUL | NULL | |
| updated_at | datetime | NO | | NULL | |
| workflow_state | varchar(255) | YES | MUL | NULL | |
| structure_subtype_id | int(11) | YES | MUL | NULL | |
| svs | tinyint(1) | YES | | NULL | |
| parent_id | int(11) | YES | MUL | NULL | |
| reference | varchar(255) | YES | | NULL | |
| soc_notify | tinyint(1) | YES | | NULL | |
| origin | int(11) | YES | | NULL | |
| category | int(11) | YES | | NULL | |
| ip_filtering | int(11) | YES | | NULL | |
| priority | int(11) | YES | | NULL | |
| creator_id | int(11) | YES | MUL | NULL | |
| external_id | varchar(255) | YES | | NULL | |
| duration_time | float | YES | | NULL | |
| reopening_at | datetime | YES | MUL | NULL | |
| closed_at | datetime | YES | | NULL | |
| cbs_detection_id_legacy | varchar(255) | YES | | NULL | |
| cbs_callback_legacy | varchar(255) | YES | | NULL | |
| load_percentage | float | YES | | NULL | |
| items_in_special_domains | tinyint(1) | NO | | 0 | |
+--------------------------+---------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)
MariaDB [antifraud]> show index from buildings;
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buildings | 0 | PRIMARY | 1 | id | A | 71923 | NULL | NULL | | BTREE | | |
| buildings | 1 | fk__buildings_client_id | 1 | client_id | A | 82 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_structure_subtype_id | 1 | structure_subtype_id | A | 80 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_parent_id | 1 | parent_id | A | 4495 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | fk__buildings_creator_id | 1 | creator_id | A | 170 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 1 | workflow_state | A | 8 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_workflow_state_and_created_at | 2 | created_at | A | 71923 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_created_at | 1 | created_at | A | 71923 | NULL | NULL | | BTREE | | |
| buildings | 1 | index_buildings_on_reopening_at | 1 | reopening_at | A | 826 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 1 | client_id | A | 80 | NULL | NULL | YES | BTREE | | |
| buildings | 1 | index_buildings_on_client_id_and_created_at | 2 | created_at | A | 71923 | NULL | NULL | | BTREE | | |
+-----------+------------+--------------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
12 rows in set (0.00 sec)
I'm playing with the variables in show variables but I do not know how to proceed
