Lately, I've been coming up with some mysql performance issues, here is the query:
select
assoc.id as id,
parentNode.id as parentNodeId,
parentNode.version as parentNodeVersion,
parentStore.protocol as parentNodeProtocol,
parentStore.identifier as parentNodeIdentifier,
parentNode.uuid as parentNodeUuid,
childNode.id as childNodeId,
childNode.version as childNodeVersion,
childStore.protocol as childNodeProtocol,
childStore.identifier as childNodeIdentifier,
childNode.uuid as childNodeUuid,
assoc.type_qname_id as type_qname_id,
assoc.child_node_name_crc as child_node_name_crc,
assoc.child_node_name as child_node_name,
assoc.qname_ns_id as qname_ns_id,
assoc.qname_localname as qname_localname,
assoc.is_primary as is_primary,
assoc.assoc_index as assoc_index
from
alf_child_assoc assoc
join alf_node parentNode on (parentNode.id = assoc.parent_node_id)
join alf_store parentStore on (parentStore.id = parentNode.store_id)
join alf_node childNode on (childNode.id = assoc.child_node_id)
join alf_store childStore on (childStore.id = childNode.store_id)
where
parentNode.id = 837
order by
assoc.assoc_index ASC,
assoc.id ASC;
The problem is that the query took too long to sort and send the result set. Any help is appreciated, thanks in advance
Information about some accounts:
mysql> select count(id) from alf_child_assoc;
+-----------+
| count(id) |
+-----------+
| 7208882 |
+-----------+
1 row in set (12.51 sec)
mysql> select count(id) from alf_node;
+-----------+
| count(id) |
+-----------+
| 3986059 |
+-----------+
1 row in set (1.68 sec)
mysql> select count(id) from alf_store;
+-----------+
| count(id) |
+-----------+
| 6 |
+-----------+
1 row in set (0.00 sec)
This is the conclusion explain:
+----+-------------+-------------+--------+---------------------------------------------------------------------+----------------+---------+--------------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------------------------------------------------------------+----------------+---------+--------------------------+--------+----------------+
| 1 | SIMPLE | parentNode | const | PRIMARY,store_id,fk_alf_node_store,will_store_node_idx | PRIMARY | 8 | const | 1 | Using filesort |
| 1 | SIMPLE | parentStore | const | PRIMARY | PRIMARY | 8 | const | 1 | |
| 1 | SIMPLE | assoc | ref | parent_node_id,fk_alf_cass_pnode,fk_alf_cass_cnode,idx_alf_cass_pri | parent_node_id | 8 | const | 275218 | Using where |
| 1 | SIMPLE | childNode | eq_ref | PRIMARY,store_id,fk_alf_node_store,will_store_node_idx | PRIMARY | 8 | repo.assoc.child_node_id | 1 | |
| 1 | SIMPLE | childStore | eq_ref | PRIMARY | PRIMARY | 8 | repo.childNode.store_id | 1 | |
+----+-------------+-------------+--------+---------------------------------------------------------------------+----------------+---------+--------------------------+--------+----------------+
5 rows in set (0.00 sec)
The following is information related to the table:
mysql> desc alf_child_assoc;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| parent_node_id | bigint(20) | NO | MUL | NULL | |
| type_qname_id | bigint(20) | NO | MUL | NULL | |
| child_node_name_crc | bigint(20) | NO | | NULL | |
| child_node_name | varchar(50) | NO | | NULL | |
| child_node_id | bigint(20) | NO | MUL | NULL | |
| qname_ns_id | bigint(20) | NO | MUL | NULL | |
| qname_localname | varchar(255) | NO | | NULL | |
| qname_crc | bigint(20) | NO | MUL | NULL | |
| is_primary | bit(1) | YES | | NULL | |
| assoc_index | int(11) | YES | MUL | NULL | |
+---------------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)
mysql> desc alf_node;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| store_id | bigint(20) | NO | MUL | NULL | |
| uuid | varchar(36) | NO | | NULL | |
| transaction_id | bigint(20) | NO | MUL | NULL | |
| node_deleted | bit(1) | NO | MUL | NULL | |
| type_qname_id | bigint(20) | NO | MUL | NULL | |
| locale_id | bigint(20) | NO | MUL | NULL | |
| acl_id | bigint(20) | YES | MUL | NULL | |
| audit_creator | varchar(255) | YES | | NULL | |
| audit_created | varchar(30) | YES | | NULL | |
| audit_modifier | varchar(255) | YES | | NULL | |
| audit_modified | varchar(30) | YES | | NULL | |
| audit_accessed | varchar(30) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)
mysql> desc alf_store;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| version | bigint(20) | NO | | NULL | |
| protocol | varchar(50) | NO | MUL | NULL | |
| identifier | varchar(100) | NO | | NULL | |
| root_node_id | bigint(20) | YES | MUL | NULL | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
mysql> show index from alf_node;
+----------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| alf_node | 0 | PRIMARY | 1 | id | A | 3890448 | NULL | NULL | | BTREE | | |
| alf_node | 0 | store_id | 1 | store_id | A | 18 | NULL | NULL | | BTREE | | |
| alf_node | 0 | store_id | 2 | uuid | A | 3890448 | NULL | NULL | | BTREE | | |
| alf_node | 1 | idx_alf_node_del | 1 | node_deleted | A | 18 | NULL | NULL | | BTREE | | |
| alf_node | 1 | idx_alf_node_txn_del | 1 | transaction_id | A | 3890448 | NULL | NULL | | BTREE | | |
| alf_node | 1 | idx_alf_node_txn_del | 2 | node_deleted | A | 3890448 | NULL | NULL | | BTREE | | |
| alf_node | 1 | fk_alf_node_acl | 1 | acl_id | A | 3890448 | NULL | NULL | YES | BTREE | | |
| alf_node | 1 | fk_alf_node_txn | 1 | transaction_id | A | 3890448 | NULL | NULL | | BTREE | | |
| alf_node | 1 | fk_alf_node_store | 1 | store_id | A | 18 | NULL | NULL | | BTREE | | |
| alf_node | 1 | fk_alf_node_tqn | 1 | type_qname_id | A | 18 | NULL | NULL | | BTREE | | |
| alf_node | 1 | fk_alf_node_loc | 1 | locale_id | A | 18 | NULL | NULL | | BTREE | | |
| alf_node | 1 | will_store_node_idx | 1 | id | A | 3890448 | NULL | NULL | | BTREE | | |
| alf_node | 1 | will_store_node_idx | 2 | store_id | A | 3890448 | NULL | NULL | | BTREE | | |
+----------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.01 sec)
mysql> show index from alf_store;
+-----------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| alf_store | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | |
| alf_store | 0 | protocol | 1 | protocol | A | 6 | NULL | NULL | | BTREE | | |
| alf_store | 0 | protocol | 2 | identifier | A | 6 | NULL | NULL | | BTREE | | |
| alf_store | 1 | fk_alf_store_root | 1 | root_node_id | A | 6 | NULL | NULL | YES | BTREE | | |
+-----------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> show index from alf_child_assoc;
+-----------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| alf_child_assoc | 0 | PRIMARY | 1 | id | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 0 | parent_node_id | 1 | parent_node_id | A | 632375 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 0 | parent_node_id | 2 | type_qname_id | A | 632375 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 0 | parent_node_id | 3 | child_node_name_crc | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 0 | parent_node_id | 4 | child_node_name | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | fk_alf_cass_pnode | 1 | parent_node_id | A | 695612 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | fk_alf_cass_cnode | 1 | child_node_id | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | fk_alf_cass_tqn | 1 | type_qname_id | A | 16 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | fk_alf_cass_qnns | 1 | qname_ns_id | A | 16 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | idx_alf_cass_qncrc | 1 | qname_crc | A | 3478063 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | idx_alf_cass_qncrc | 2 | type_qname_id | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | idx_alf_cass_qncrc | 3 | parent_node_id | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | idx_alf_cass_pri | 1 | parent_node_id | A | 1159354 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | idx_alf_cass_pri | 2 | is_primary | A | 1159354 | NULL | NULL | YES | BTREE | | |
| alf_child_assoc | 1 | idx_alf_cass_pri | 3 | child_node_id | A | 6956126 | NULL | NULL | | BTREE | | |
| alf_child_assoc | 1 | will_order_idx | 1 | assoc_index | A | 16 | NULL | NULL | YES | BTREE | | |
| alf_child_assoc | 1 | will_order_idx | 2 | id | A | 6956126 | NULL | NULL | | BTREE | | |
+-----------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
17 rows in set (0.16 sec)
after reading the @Solarflare answer, I tried it, now the request will look like this:
SELECT STRAIGHT_JOIN
assoc.id AS id,
parentNode.id AS parentNodeId,
parentNode.version AS parentNodeVersion,
parentStore.protocol AS parentNodeProtocol,
parentStore.identifier AS parentNodeIdentifier,
parentNode.uuid AS parentNodeUuid,
childNode.id AS childNodeId,
childNode.version AS childNodeVersion,
childStore.protocol AS childNodeProtocol,
childStore.identifier AS childNodeIdentifier,
childNode.uuid AS childNodeUuid,
assoc.type_qname_id AS type_qname_id,
assoc.child_node_name_crc AS child_node_name_crc,
assoc.child_node_name AS child_node_name,
assoc.qname_ns_id AS qname_ns_id,
assoc.qname_localname AS qname_localname,
assoc.is_primary AS is_primary,
assoc.assoc_index AS assoc_index
FROM
alf_child_assoc assoc FORCE INDEX (will_subq_idx)
JOIN
alf_node parentNode ON (parentNode.id = assoc.parent_node_id)
JOIN
alf_node childNode ON (childNode.id = assoc.child_node_id)
JOIN
alf_store parentStore ON (parentStore.id = parentNode.store_id)
JOIN
alf_store childStore ON (childStore.id = childNode.store_id)
WHERE
parentNode.id = 550
ORDER BY assoc.assoc_index ASC , assoc.id ASC;
and the explanation shows:
+----+-------------+-------------+------------+--------+-----------------------------------------------------------------------+---------------+---------+--------------------------+--------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+-----------------------------------------------------------------------+---------------+---------+--------------------------+--------+----------+---------------------------------------+
| 1 | SIMPLE | assoc | NULL | ref | will_subq_idx | will_subq_idx | 8 | const | 303104 | 100.00 | Using index condition; Using filesort |
| 1 | SIMPLE | parentNode | NULL | const | PRIMARY,store_id,fk_alf_node_store,will_store_node_idx,will_store_idx | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | childNode | NULL | eq_ref | PRIMARY,store_id,fk_alf_node_store,will_store_node_idx,will_store_idx | PRIMARY | 8 | repo.assoc.child_node_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | parentStore | NULL | eq_ref | PRIMARY,will_store_idx | PRIMARY | 8 | repo.parentNode.store_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | childStore | NULL | eq_ref | PRIMARY,will_store_idx | PRIMARY | 8 | repo.childNode.store_id | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+--------+-----------------------------------------------------------------------+---------------+---------+--------------------------+--------+----------+---------------------------------------+
5 rows in set, 1 warning (0.03 sec)
This is the query plan displayed by mysql workbench:
Thanks guys, you are cool!