Question 346553 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I optimize this simple join query? It takes a long time

I am not sure on how to begin optimizing this High Latency SQL query that my platform runs. Other than reducing the datasets, what are some things that I can do?

SELECT ctm.* 
FROM compound_transaction_map ctm 
JOIN bustransaction_map btm ON ctm.transId = btm.transId 
WHERE btm.docId = ?

docId gets inserted by my DBWConnector in the Java code

The main issue the size of the tables, compound_transaction_map is 776,387 entries and bustransaction_map is 3,252,772. I am working on reducing the table sizes, but just wondering if this query is also an issue.

Just looking to get some insight from someone more experienced than me. I appreciate your help.

Edit: There are indexes on transId and bustransaction_map.docID

mysql> show index from compound_transaction_map;
+--------------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                    | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| compound_transaction_map |          0 | PRIMARY                |            1 | id          | A         |      765804 |     NULL | NULL   |      | BTREE      |         |               |
| compound_transaction_map |          0 | UNQ_compoundId_transId |            1 | transId     | A         |      772560 |     NULL | NULL   |      | BTREE      |         |               |
| compound_transaction_map |          0 | UNQ_compoundId_transId |            2 | compoundId  | A         |      772560 |     NULL | NULL   |      | BTREE      |         |               |
| compound_transaction_map |          1 | transId                |            1 | transId     | A         |      772560 |     NULL | NULL   |      | BTREE      |         |               |
| compound_transaction_map |          1 | compoundId             |            1 | compoundId  | A         |      645871 |     NULL | NULL   |      | BTREE      |         |               |
| compound_transaction_map |          1 | companyId              |            1 | companyId   | A         |         625 |     NULL | NULL   |      | BTREE      |         |               |
+--------------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show index from bustransaction_map;
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bustransaction_map |          0 | PRIMARY  |            1 | id          | A         |     2869993 |     NULL | NULL   |      | BTREE      |         |               |
| bustransaction_map |          0 | docId    |            1 | docId       | A         |     2883521 |     NULL | NULL   | YES  | BTREE      |         |               |
| bustransaction_map |          1 | transId  |            1 | transId     | A         |     1019639 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT ctm.*
IN bustransaction_ma    -> FROM compound_transaction_map ctm
    -> JOIN bustransaction_map btm ON ctm.transId = btm.transId
    -> WHERE btm.docId = '62EFCD90-BC62-9984-0B20-4465528D6359';
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | btm   | NULL       | const | docId,transId | docId | 153     | const |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | ctm   | NULL       | ALL   | NULL          | NULL  | NULL    | NULL  | 772560 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+--------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)


mysql> SHOW CREATE TABLE compound_transaction_map;
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                    | Create Table

                                                            |
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| compound_transaction_map | CREATE TABLE `compound_transaction_map` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `compoundId` varchar(100) NOT NULL,
  `transId` varchar(100) NOT NULL,
  `companyId` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNQ_compoundId_transId` (`transId`,`compoundId`),
  KEY `transId` (`transId`),
  KEY `compoundId` (`compoundId`),
  KEY `companyId` (`companyId`)
) ENGINE=InnoDB AUTO_INCREMENT=865326 DEFAULT CHARSET=latin1 |
+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE bustransaction_map;
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table
                                                                                                                |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| bustransaction_map | CREATE TABLE `bustransaction_map` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `transId` varchar(50) DEFAULT NULL,
  `docId` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `docId` (`docId`),
  KEY `transId` (`transId`)
) ENGINE=InnoDB AUTO_INCREMENT=13033253 DEFAULT CHARSET=utf8 |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN select ctm.* from bustransaction_map btm join compound_transaction_map ctm force index (transId) on ctm.transId = btm.transId where btm.docId = '62EFCD90-BC62-9984-0B20-4465528D6359';
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | btm   | NULL       | const | docId,transId | docId | 153     | const |      1 |   100.00 | NULL        |
|  1 | SIMPLE      | ctm   | NULL       | ALL   | NULL          | NULL  | NULL    | NULL  | 772560 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
Tags:

2

Answers


  1. 0

    MySQL can only use one index per table. The explain shows that it chose to use the index on bustransaction_map.docId to help with the where docId = ?; bustransaction_map is the larger table so it wants to cut down its rows. It uses no index on compound_transaction_map.

    To solve this, create a compound index on bustransaction_map of (docId, transId) (the order is important). MySQL can use this index for any queries which feature just bustransaction_map.docId and queries which use both bustransaction_map.docId and bustransaction_map.transId like yours.

    You can then drop the index on docId, it’s redundant. You can also drop the single column index on compound_transaction_map.transId, it is redundant with the compound index on (transId, compoundId).

    In general, if you have two columns you can cover all queries containing any combination of them with two indexes…

    • A
    • (B, A)

    More notes:

    • Only restrict varchar sizes if the data has an inherent size limit, otherwise use varchar(255). "varchar" means "variable [size] characters"; MySQL will only use as much space as it needs. Arbitrary restrictions just cause future headaches.
    • Your transId is of different sizes in each table: one is varchar(50) and one is varchar(100). If they’re referring to the same thing they should be the same.
    Login or Signup to reply.
  2. 0

    You can try

    SELECT ctm.* 
FROM compound_transaction_map ctm 
WHERE ctm.transid in
    (select btm.transid 
     from bustransaction_map btm 
     WHERE btm.docId = ?
    )

    I think, for subquery will be used index (docId) – index seek, or (docId,transId) – only index scan.
    For join will be used range join with index on ctm.transId.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search