EXPLAIN PLAN menjelaskan Query langsung terhadap table dan view adalah sama saja di MySQL

explain-plan.sql

mysql> -------------------------------------
mysql> ini execution plan 
mysql> untuk query langsung ke base table
mysql> -------------------------------------
mysql> explain
    -> select a.*, b.account_id, b.item_id, b.debet, b.credit
    -> from journals a
    -> join journal_details b on a.id=b.journal_id
    -> where a.id=1 ;
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys               | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY                     | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | b     | ALL   | FK_journal_details_journals | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> -------------------------------------
mysql> coba kita buat view-nya - tanpa where
mysql> -------------------------------------
mysql> create view view_data as
    -> select a.*, b.account_id, b.item_id, b.debet, b.credit
    -> from journals a
    -> join journal_details b on a.id=b.journal_id ;
Query OK, 0 rows affected (0.05 sec)

mysql> -------------------------------------
mysql> ini execution plan 
mysql> untuk query langsung ke view
mysql> -------------------------------------
mysql> explain
    -> select * from view_data
    -> where id=1 ;
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys               | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY                     | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | b     | ALL   | FK_journal_details_journals | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+-----------------------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> -------------------------------------
mysql> YAY....!!!
mysql> rupanya sama saja.....
mysql> -------------------------------------