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

MySQL optimising slow query

I am trying to run a transaction report but it is taking more than 40sec then timeout by the server. Hope I can get some help to get it optimised.

select 
      test_txn.abcid, 
      txndate,
      txndate2 
   from 
      test_txn 
         inner join test_abc 
            on test_txn.abcid = test_abc.abcid 
         inner join typetest 
            on test_txn.txntypeid = typetest.id 
   WHERE 
      typetest.name = 'DAILY';

SHOW CREATE TABLE test_txn;

-- transaction table contains over 1million records 

CREATE TABLE `test_txn` (
   `id` int(11) unsigned NOT NULL DEFAULT 0,
   `txndate` datetime NOT NULL DEFAULT current_timestamp(),
   `txndate2` date DEFAULT NULL,
   `abcid` char(15) DEFAULT NULL,
   `txntypeid` int(11) unsigned DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `txntypeid` (`txntypeid`),
   KEY `abcid` (`abcid`,`txndate`),
   CONSTRAINT `test_txn_ibfk_1` FOREIGN KEY (`txntypeid`) REFERENCES `typetest` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE test_abc;
-- contains few hundreds records

CREATE TABLE `test_abc` (
   `id` int(11) unsigned NOT NULL DEFAULT 0,
   `abcid` char(15) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `abcid` (`abcid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE TABLE typetest;
-- a small lookup table only 20 records;
CREATE TABLE `typetest` (
   `id` int(11) unsigned NOT NULL DEFAULT 0,
   `name` char(20) NOT NULL DEFAULT '',
   `payment` tinyint(1) NOT NULL DEFAULT 0,
   `description` varchar(100) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `txnName` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

This is the slow report I am trying to run

Analyze select count(*) from test_txn inner join test_abc on test_txn.abcid=test_abc.abcid inner join typetest on test_txn.txntypeid=typetest.id WHERE typetest.name='DAILY';
+------+-------------+----------+------+-----------------+-----------+---------+------------------------+--------+-------------+----------+------------+--------------------------+
| id   | select_type | table    | type | possible_keys   | key       | key_len | ref                    | rows   | r_rows      | filtered | r_filtered | Extra                    |
+------+-------------+----------+------+-----------------+-----------+---------+------------------------+--------+-------------+----------+------------+--------------------------+
|    1 | SIMPLE      | typetest | ref  | PRIMARY,txnName | txnName   | 20      | const                  | 1      | 1.00        |   100.00 |     100.00 | Using where; Using index |
|    1 | SIMPLE      | test_txn | ref  | txntypeid,abcid | txntypeid | 5       | residev.typetest.id    | 386642 | 10969301.00 |   100.00 |     100.00 | Using where              |
|    1 | SIMPLE      | test_abc | ref  | abcid           | abcid     | 16      | residev.test_txn.abcid | 1      | 1.00        |   100.00 |     100.00 | Using index              |
+------+-------------+----------+------+-----------------+-----------+---------+------------------------+--------+-------------+----------+------------+--------------------------+
3 rows in set (49.26 sec)

When I took out the join with the small lookup table it ran fine

 Analyze select count(*) from test_txn inner join test_abc on test_txn.abcid=test_abc.abcid;
+------+-------------+----------+-------+---------------+-------+---------+------------------------+--------+-----------+----------+------------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key   | key_len | ref                    | rows   | r_rows    | filtered | r_filtered | Extra                    |
+------+-------------+----------+-------+---------------+-------+---------+------------------------+--------+-----------+----------+------------+--------------------------+
|    1 | SIMPLE      | test_abc | index | abcid         | abcid | 16      | NULL                   | 131202 | 131304.00 |   100.00 |     100.00 | Using where; Using index |
|    1 | SIMPLE      | test_txn | ref   | abcid         | abcid | 16      | residev.test_abc.abcid | 66     | 88.23     |   100.00 |     100.00 | Using index              |
+------+-------------+----------+-------+---------------+-------+---------+------------------------+--------+-----------+----------+------------+--------------------------+
2 rows in set (5.60 sec)

transaction table join with the lookup table typetest is fine

 Analyze select count(*) from test_txn inner join typetest on test_txn.txntypeid=typetest.id WHERE typetest.name='DAILY';
+------+-------------+----------+------+-----------------+-----------+---------+---------------------+--------+-------------+----------+------------+--------------------------+
| id   | select_type | table    | type | possible_keys   | key       | key_len | ref                 | rows   | r_rows      | filtered | r_filtered | Extra                    |
+------+-------------+----------+------+-----------------+-----------+---------+---------------------+--------+-------------+----------+------------+--------------------------+
|    1 | SIMPLE      | typetest | ref  | PRIMARY,txnName | txnName   | 20      | const               | 1      | 1.00        |   100.00 |     100.00 | Using where; Using index |
|    1 | SIMPLE      | test_txn | ref  | txntypeid       | txntypeid | 5       | residev.typetest.id | 386642 | 10969301.00 |   100.00 |     100.00 | Using index              |
+------+-------------+----------+------+-----------------+-----------+---------+---------------------+--------+-------------+----------+------------+--------------------------+
2 rows in set (4.87 sec)


SELECT COUNT(*) from test_txn;
+----------+
| COUNT(*) |
+----------+
| 12430021 |
+----------+
1 row in set (3.70 sec)
 SELECT COUNT(*) from test_txn where abcid IS NULL;
+----------+
| COUNT(*) |
+----------+
|   844795 |
+----------+
1 row in set (0.65 sec)
Tags:

2

Answers


  1. 0

    maybe you can add one new index on the table test_txn , like this:

    ALTER TABLE `test_txn`  ADD INDEX `idx_com1`(`abcid`, `txntypeid`) USING BTREE;

    that it can using indexes when you join with three tables.

    Login or Signup to reply.
  2. 0

    I would rewrite based first on the table you want the data from with the filtering WHERE criteria. Have an appropriate index to match that where clause AND the column needed to get to the next table in the joins

    table       index
typetest    (name, id)
test_txn    (txntypeid, abcid )
test_abc    (abcid)

    By having a single index on a table that has both (or more) columns associated with the search will allow the engine to QUALIFY the records directly from the index without having to go to the raw data pages for any other parts.

    So here, you can see I start with the typeTest based on the name first since that is your WHERE condition, but then ALSO the ID as that is the lead into the Test_TXN table. From the Test_TXN table you need that first part txnTypeId to qualify the test, but then need the ABCID for its next link down to the test_abc table. Finally, since you indicated this is an abbreviated query due to private information, I dont see a need for anything else but the abcid column in your test_abc table.

    Once the records are qualified all from the index, it can then go to the raw data pages to pull the records it needs for additional pull of columns such as your txnDate and txnDate2.

    select 
      test_abc.abcid, 
      test_txn.txndate,
      test_txn.txndate2 
   from 
      typetest 
         inner join test_txn 
            on typetest.id = test_txn.txntypeid
            inner join test_abc 
               on test_txn.abcid = test_abc.abcid 
   WHERE 
      typetest.name = 'DAILY';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search