how to improve query performance

Question

## 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?

Answer 1

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) ;