Mysql – how to improve query performance

MySQLperformancequery-performance

## Only 1 query which returns 10 rows. 
## It took about 66 secs

mysql> explain 
    SELECT al.log_id , al.user_id , al.page_name , 
           al.section_name , al.submit_time , al.url  , 
           alfm1.value as 'source_env'  , 
           alfm2.value as 'website_id'  , 
           alfm3.value as 'index_id'  
    FROM  admin_logs al 
      LEFT OUTER JOIN 
        (select * from admin_log_field_map where field_id = 3351 
        )  alfm1 ON al.log_id = alfm1.log_id  
      LEFT OUTER JOIN 
        (select * from admin_log_field_map where field_id = 911 
        )  alfm2 ON al.log_id = alfm2.log_id  
      , admin_log_field_map alfm3  
    WHERE 1=1  
      AND al.page_name='Index Management'  
      AND al.log_id=alfm3.log_id 
      AND alfm3.field_id=891  
      AND alfm3.value='jewelry'  
    ORDER BY al.log_id DESC;

+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys      | key        | key_len | ref                 | rows  | Extra                                        |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>          | system | NULL               | NULL       | NULL    | NULL                |     0 | const row not found                          |
|  1 | PRIMARY     | al                  | ref    | idx_622,idx_pgname | idx_pgname | 78      | const               | 27720 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | alfm3               | ref    | idx_1533,idx_0311  | idx_1533   | 10      | cms.al.log_id,const |     1 | Using where                                  |
|  1 | PRIMARY     | <derived3>          | ALL    | NULL               | NULL       | NULL    | NULL                | 35358 |                                              |
|  3 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     | 33098 | Using where                                  |
|  2 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     |     1 | Using where                                  |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
6 rows in set (0.20 sec)

mysql>
mysql>
mysql> show create table admin_logs\G
*************************** 1. row ***************************
       Table: admin_logs
Create Table: CREATE TABLE `admin_logs` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `user_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `page_name` varchar(75) COLLATE latin1_bin DEFAULT NULL,
  `section_name` varchar(125) COLLATE latin1_bin DEFAULT NULL,
  `submit_time` datetime DEFAULT NULL,
  `url` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `source_environment` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `log` longtext COLLATE latin1_bin,
  UNIQUE KEY `idx_622` (`log_id`),
  KEY `idx_pgname` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> \! hostname
fol-piwikdb01.dca.ftd.untd.com
mysql> show create table admin_log_field_map\G
*************************** 1. row ***************************
       Table: admin_log_field_map
Create Table: CREATE TABLE `admin_log_field_map` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `field_id` int(11) DEFAULT NULL,
  `value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_1533` (`log_id`,`field_id`),
  KEY `idx_0311` (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)


how to get better performance using above query?

Best Answer

First, lets rewrite the query by dropping the ANSI-89 implicit join syntax and removing the derived tables:

SELECT 
       al.log_id , al.user_id , al.page_name , 
       al.section_name , al.submit_time , al.url  , 
       alfm1.value as 'source_env'  , 
       alfm2.value as 'website_id'  , 
       alfm3.value as 'index_id'  
FROM  
    admin_logs AS al 
  LEFT OUTER JOIN 
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id 
  LEFT OUTER JOIN 
    admin_log_field_map AS alfm2
      ON  alfm2.field_id = 911
      AND alfm2.log_id = al.log_id  
  INNER JOIN 
    admin_log_field_map AS alfm3  
      ON  alfm3.field_id = 891  
      AND alfm3.value = 'jewelry' 
      AND alfm3.log_id = al.log_id
WHERE 
    al.page_name = 'Index Management'  
ORDER BY 
    al.log_id DESC ;

I've also move some of the conditions from the WHERE clause to the responding ON clause, so it is more apparent which indexes you need.

From the two outer joins it's clear that from table admin_log_field_map, you only need the rows with a fixed field_id (either 3351 or 911) and then only the columns log_id (to do the join) and the value (to be returned in the SELECT list):

SELECT 
--
       alfm1.value as 'source_env'  , 
--
  LEFT OUTER JOIN 
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id

So, a good index for these 2 left joins would be (field_id, log_id, value)

The third (inner) join to admin_log_field_map, is different. You only need the rows with a fixed field_id (891) and fixed value ('jewelry') and then log_id (to do the join). After that, nothing is really needed for the SELECT list (the value is already known and fixed):

SELECT 
--
       alfm3.value as 'index_id'  
--
  INNER JOIN 
    admin_log_field_map AS alfm3  
      ON  alfm3.field_id = 891  
      AND alfm3.value = 'jewelry' 
      AND alfm3.log_id = al.log_id

So, a good index for these 2 left joins would be (field_id, value, log_id)

For the admin_logs table, you already have an index on (page_name) so it will probably be used for the WHERE condition.

So, what you can do is add these two indexes:

ALTER TABLE admin_log_field_map 
    ADD INDEX idx__field_id__log_id__value
        (field_id, log_id, value)
  , ADD INDEX idx__field_id__value__log_id
        (field_id, value, log_id) ;
Related Question