Trying to understand how to use mysql query profiling to win

sql_views

  SELECT count(*)
  FROM drug_order
  INNER JOIN drug ON drug_order.drug_inventory_id = drug.drug_id
  INNER JOIN concept_set as arv_drug_concepts ON 
    arv_drug_concepts.concept_set = 460 AND 
    arv_drug_concepts.concept_id = drug.concept_id  
  WHERE drug.concept_id IS NOT NULL;
=> 
+----------+
| count(*) |
+----------+
|   144617 | 
+----------+
1 row in set (0.08 sec)

+--------------------+-----------+
| Status             | Duration  |
+--------------------+-----------+
| (initialization)   | 0.000006  | 
| Opening tables     | 0.000023  | 
| System lock        | 0.000009  | 
| Table lock         | 0.000013  | 
| init               | 0.000037  | 
| optimizing         | 0.000029  | 
| statistics         | 0.000107  | 
| preparing          | 0.000034  | 
| executing          | 0.00001   | 
| Sending data       | 0.0714169 | 
| end                | 0.000013  | 
| query end          | 0.000005  | 
| freeing items      | 0.000022  | 
| closing tables     | 0.000007  | 
| logging slow query | 0.000003  | 
+--------------------+-----------+


Adding in joins to get at one field (all one to one relations) kills the performance

  SELECT count(*)
  FROM drug_order
  INNER JOIN drug ON drug_order.drug_inventory_id = drug.drug_id
  INNER JOIN concept_set as arv_drug_concepts ON 
    arv_drug_concepts.concept_set = 460 AND 
    arv_drug_concepts.concept_id = drug.concept_id  
  INNER JOIN orders ON orders.order_id = drug_order.order_id
  INNER JOIN encounter ON orders.encounter_id = encounter.encounter_id
  WHERE drug.concept_id IS NOT NULL;
=>
+----------+
| count(*) |
+----------+
|   144617 | 
+----------+
1 row in set (3.75 sec)

+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| (initialization)   | 0.000109 | 
| Opening tables     | 0.00003  | 
| System lock        | 0.000011 | 
| Table lock         | 0.000014 | 
| init               | 0.000044 | 
| optimizing         | 0.000034 | 
| statistics         | 0.000139 | 
| preparing          | 0.000041 | 
| executing          | 0.000011 | 
| Sending data       | 3.718889 | 
| end                | 0.000015 | 
| query end          | 0.000006 | 
| freeing items      | 0.000029 | 
| closing tables     | 0.000007 | 
| logging slow query | 0.000004 | 
+--------------------+----------+


## Index Information

mysql> SHOW INDEX FROM concept_set;
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| concept_set |          0 | PRIMARY          |            1 | concept_id  | A         |         130 |     NULL | NULL   |      | BTREE      |         | 
| concept_set |          0 | PRIMARY          |            2 | concept_set | A         |         130 |     NULL | NULL   |      | BTREE      |         | 
| concept_set |          1 | has_a            |            1 | concept_set | A         |          43 |     NULL | NULL   |      | BTREE      |         | 
| concept_set |          1 | user_who_created |            1 | creator     | A         |           4 |     NULL | NULL   |      | BTREE      |         | 
+-------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

mysql> mysql> SHOW INDEX FROM orders;
+--------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name                    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| orders |          0 | PRIMARY                     |            1 | order_id        | A         |      123779 |     NULL | NULL   |      | BTREE      |         | 
| orders |          1 | order_creator               |            1 | creator         | A         |           3 |     NULL | NULL   |      | BTREE      |         | 
| orders |          1 | orderer_not_drug            |            1 | orderer         | A         |         273 |     NULL | NULL   | YES  | BTREE      |         | 
| orders |          1 | orders_in_encounter         |            1 | encounter_id    | A         |      123779 |     NULL | NULL   | YES  | BTREE      |         | 
| orders |          1 | type_of_order               |            1 | order_type_id   | A         |           3 |     NULL | NULL   |      | BTREE      |         | 
| orders |          1 | user_who_discontinued_order |            1 | discontinued_by | A         |           3 |     NULL | NULL   | YES  | BTREE      |         | 
| orders |          1 | user_who_voided_order       |            1 | voided_by       | A         |           3 |     NULL | NULL   | YES  | BTREE      |         | 
+--------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.20 sec)

mysql> SHOW INDEX FROM encounter;
+-----------+------------+--------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| encounter |          0 | PRIMARY            |            1 | encounter_id   | A         |      520719 |     NULL | NULL   |      | BTREE      |         | 
| encounter |          1 | encounter_location |            1 | location_id    | A         |          11 |     NULL | NULL   |      | BTREE      |         | 
| encounter |          1 | encounter_patient  |            1 | patient_id     | A         |       40055 |     NULL | NULL   |      | BTREE      |         | 
| encounter |          1 | encounter_provider |            1 | provider_id    | A         |         537 |     NULL | NULL   |      | BTREE      |         | 
| encounter |          1 | encounter_type_id  |            1 | encounter_type | A         |          11 |     NULL | NULL   | YES  | BTREE      |         | 
| encounter |          1 | encounter_creator  |            1 | creator        | A         |         129 |     NULL | NULL   |      | BTREE      |         | 
| encounter |          1 | encounter_form     |            1 | form_id        | A         |           9 |     NULL | NULL   | YES  | BTREE      |         | 
| encounter |          1 | ordered_encounters |            1 | encounter_id   | A         |      520719 |     NULL | NULL   |      | BTREE      |         | 
+-----------+------------+--------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.59 sec)

mysql> SHOW INDEX FROM drug_order;
+------------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name       | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| drug_order |          0 | PRIMARY        |            1 | drug_order_id     | A         |      211533 |     NULL | NULL   |      | BTREE      |         | 
| drug_order |          1 | inventory_item |            1 | drug_inventory_id | A         |          58 |     NULL | NULL   | YES  | BTREE      |         | 
| drug_order |          1 | extends_order  |            1 | order_id          | A         |      211533 |     NULL | NULL   |      | BTREE      |         | 
+------------+------------+----------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)


# EXPLAIN 

-------------------------+
| id | select_type | table             | type   | possible_keys                | key                  | key_len | ref                                     | rows | Extra                    |
+----+-------------+-------------------+--------+------------------------------+----------------------+---------+-----------------------------------------+------+--------------------------+
|  1 | SIMPLE      | arv_drug_concepts | ref    | PRIMARY,has_a                | has_a                | 4       | const                                   |   21 | Using where; Using index | 
|  1 | SIMPLE      | drug              | ref    | PRIMARY,primary_drug_concept | primary_drug_concept | 4       | openmrs_lh.arv_drug_concepts.concept_id |    1 | Using index              | 
|  1 | SIMPLE      | drug_order        | ref    | inventory_item,extends_order | inventory_item       | 5       | openmrs_lh.drug.drug_id                 | 3647 | Using where              | 
|  1 | SIMPLE      | orders            | eq_ref | PRIMARY,orders_in_encounter  | PRIMARY              | 4       | openmrs_lh.drug_order.order_id          |    1 |                          | 
|  1 | SIMPLE      | encounter         | eq_ref | PRIMARY,ordered_encounters   | PRIMARY              | 4       | openmrs_lh.orders.encounter_id          |    1 | Using index              | 
+----+-------------+-------------------+--------+------------------------------+----------------------+---------+-----------------------------------------+------+--------------------------+