How to decrease this mysql query execution time?

Question

Parent Table,

mysql> desc sattool_testing;

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| circuit_id       | varchar(30)  | NO   |     | NULL    |                |
| coff_id          | varchar(100) | NO   |     | NULL    |                |
| result           | int(11)      | YES  |     | NULL    |                |
| result_details   | text         | NO   |     | NULL    |                |
| details          | longtext     | NO   |     | NULL    |                |
| reverse_response | varchar(100) | NO   |     | NULL    |                |
| start_date       | datetime     | NO   |     | NULL    |                |
| end_date         | datetime     | YES  |     | NULL    |                |
| Modules          | varchar(100) | YES  |     | NULL    |                |
| rehit            | datetime     | YES  |     | NULL    |                |
| isAuto           | tinyint(1)   | NO   |     | 0       |                |
+------------------+--------------+------+-----+---------+----------------+

12 rows in set (0.00 sec)

1st Child Table,

mysql> desc sattool_desc;

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| sattool_desc_id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| ceinterface        | varchar(50)  | YES  |     | NULL    |                |
| cehostname         | varchar(50)  | YES  |     | NULL    |                |
| vprnno             | varchar(50)  | YES  |     | NULL    |                |
| policyname         | varchar(50)  | YES  |     | NULL    |                |
| cosno              | varchar(10)  | YES  |     | NULL    |                |
| peipaddress        | varchar(17)  | YES  |     | NULL    |                |
| router_type        | varchar(5)   | YES  |     | NULL    |                |
| sattool_testing_id | bigint(20)   | YES  |     | NULL    |                |
| epipeid            | varchar(20)  | YES  |     | NULL    |                |
| cerouter           | varchar(10)  | YES  |     | NULL    |                |
| service_type       | varchar(10)  | YES  |     | NULL    |                |
| scope_of_manage    | varchar(50)  | YES  |     | NULL    |                |
| service_name       | varchar(10)  | YES  |     | NULL    |                |
| ce_vrf_name        | varchar(150) | YES  |     | NULL    |                |
+--------------------+--------------+------+-----+---------+----------------+

15 rows in set (0.01 sec)

2nd Child Table (Contain multiple records against one parent table record), mysql> desc sattool_error_log;

+----------------+--------------+------+-----+-------------------+-----------------------------+
| Field          | Type         | Null | Key | Default           | Extra                       |
+----------------+--------------+------+-----+-------------------+-----------------------------+
| id             | bigint(20)   | NO   | PRI | NULL              | auto_increment              |
| service_id     | varchar(30)  | NO   |     | NULL              |                             |
| coff_id        | varchar(50)  | NO   |     | NULL              |                             |
| error_name     | varchar(100) | NO   |     | NULL              |                             |
| error_desc     | text         | NO   |     | NULL              |                             |
| error_occurred | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| error_type     | int(11)      | YES  |     | NULL              |                             |
| error_code     | varchar(10)  | YES  |     | NULL              |                             |
| sattool_id     | bigint(20)   | YES  |     | 0                 |                             |
| module_no      | int(11)      | YES  |     | 0                 |                             |
+----------------+--------------+------+-----+-------------------+-----------------------------+

10 rows in set (0.00 sec)

SQL Query :-

SELECT GROUP_CONCAT(sattool_error_log.error_desc) AS err_desc, GROUP_CONCAT(sattool_error_log.error_type) AS err_type, sattool_testing.*, sattool_desc.service_type, sattool_desc.service_name, sattool_desc.scope_of_manage 
FROM sattool_testing 
    LEFT JOIN sattool_desc ON sattool_testing.id = sattool_desc.sattool_testing_id 
    LEFT JOIN sattool_error_log ON sattool_testing.id = sattool_error_log.sattool_id 
WHERE sattool_testing.isAuto = 1 GROUP BY sattool_testing.id ORDER BY sattool_testing.id DESC limit 100

Current Execution Time is (22.76 sec)

Explain Query :-

+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | sattool_testing   | ALL  | NULL          | NULL | NULL    | NULL | 3578 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | sattool_desc      | ALL  | NULL          | NULL | NULL    | NULL | 4009 |                                              |
|  1 | SIMPLE      | sattool_error_log | ALL  | NULL          | NULL | NULL    | NULL | 8904 |                                              |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------------------------------------+

3 rows in set (0.00 sec)

I want to increase speed to above query execution.

Please give me suggestion on that.

add indices to the columns you use in the join/where clauses. — Dormilich

Thribhuwan Thribhuwan · Accepted Answer · 2018-03-12T06:50:50

Thanks!!!

I have added index to the columns used in the join/where clauses.

Like :-

ALTER TABLE sattool_desc ADD INDEX sattool_testing_id (sattool_testing_id);
ALTER TABLE sattool_error_log ADD INDEX sattool_id (sattool_id);
ALTER TABLE sattool_testing ADD INDEX isAuto (isAuto);

Now the execution time is 100 rows in set (0.00 sec).

Thanks again.

How to decrease this mysql query execution time?

1 Answers