set profiling = 1;
show profiles;
select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid and (i.key_ like 'log%' or i.key_ like 'logrt%')
inner join history_log hl on i.itemid = hl.itemid
order by ia.applicationid, ia.itemid, i.key_
select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid
inner join history_log hl on i.itemid = hl.itemid
where i.key_ like 'log%' or i.key_ like 'logrt%'
order by ia.applicationid, ia.itemid, i.key_
show profiles;
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query
|
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.034328 | select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid and (i.key_ like 'log%' or i.key_ like 'logrt%')
inner join histo |
| 2 | 0.019305 | select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid
inner join history_log hl on i.itemid = hl.itemid
where i.key_ l |
| 3 | 0.023647 | select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid and (i.key_ like 'log%' or i.key_ like 'logrt%')
inner join histo |
| 4 | 0.335506 | select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid
inner join history_log hl on i.itemid = hl.itemid
where i.key_ l |
+----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> explain select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid and (i.key_ like 'log%' or i.key_ like 'logrt%')
inner join history_log hl on i.itemid = hl.itemid
order by ia.applicationid, ia.itemid, i.key_;
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+------------------+------+--------------------------+
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | ia | ref | items_applications_1,items_applications_2 | items_applications_1 | 8 | const | 6 | Using where; Using index |
| 1 | SIMPLE | i | eq_ref | PRIMARY | PRIMARY | 8 | smp2rl.ia.itemid | 1 | Using where |
| 1 | SIMPLE | hl | ref | history_log_2,history_log_1 | history_log_2 | 8 | smp2rl.ia.itemid | 889 | |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+------------------+------+--------------------------+
4 rows in set
mysql>
mysql> explain select ia.applicationid, ia.itemid, i.name, i.hostid, i.key_, hl.value from items_applications ia
inner join applications a on ia.applicationid = a.applicationid and ia.applicationid = 242
inner join items i on ia.itemid = i.itemid
inner join history_log hl on i.itemid = hl.itemid
where i.key_ like 'log%' or i.key_ like 'logrt%'
order by ia.applicationid, ia.itemid, i.key_;
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+------------------+------+--------------------------+
| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | ia | ref | items_applications_1,items_applications_2 | items_applications_1 | 8 | const | 6 | Using where; Using index |
| 1 | SIMPLE | i | eq_ref | PRIMARY | PRIMARY | 8 | smp2rl.ia.itemid | 1 | Using where |
| 1 | SIMPLE | hl | ref | history_log_2,history_log_1 | history_log_2 | 8 | smp2rl.ia.itemid | 889 | |
+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+------------------+------+--------------------------+
4 rows in set