MySQL: Sometimes query failed to use index

Question

I have a complex query including join of 4-5 tables. 1 months back, I have optimized this query and it was using the index. Also, two months back, index of all tables had been rebuilt by just export and import the database. Just few days back, I found that this query is appearing in slow query logs again. When I analysed it, I found that one table in this query is not using index which prompts to higher join size as well as higher execution time.

As a solution, I executed optimized table tablename command on the table which was skipping the index. As a result Query again started to use that index and not appearing in slow query log.

Now, I have below question regarding the above analysis:

• Why query skipped to use the index?
• Should I rebuild the index after interval of each two months either by executing Optimize command or by export / import?
• As we know that after each insert, index rebuild then why this issue occur? Is it the result of fragmentation?

Below is the explain result of query after optimization of the jos_content tbale:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: jtrf
         type: ref
possible_keys: PRIMARY,idx_name_jos_tag_rules_fields
          key: idx_name_jos_tag_rules_fields
      key_len: 767
          ref: const
         rows: 1
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: jtrv
         type: ref
possible_keys: PRIMARY,idx_field_id_jos_tag_rules_values
          key: idx_field_id_jos_tag_rules_values
      key_len: 4
          ref: jprod.jtrf.id
         rows: 16
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: jct
         type: ref
possible_keys: PRIMARY,idx_content_id_jos_content_tags,idx_tag_value_id_jos_
ent_tags
          key: idx_tag_value_id_jos_content_tags
      key_len: 4
          ref: jprod.jtrv.id
         rows: 411
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY,idx_access,idx_id_jos_content
          key: PRIMARY
      key_len: 4
          ref: jprod.jct.content_id
         rows: 1
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: jprod.a.created_by
         rows: 1
        Extra:
*************************** 6. row ***************************
           id: 1
  select_type: PRIMARY
        table: g
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 1
          ref: jprod.a.access
         rows: 1
        Extra:
*************************** 7. row ***************************
           id: 7
  select_type: DEPENDENT SUBQUERY
        table: jos_tag_rules_content_exclude
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: jprod.a.id,const
         rows: 1
        Extra: Using where; Using index
*************************** 8. row ***************************
           id: 6
  select_type: DEPENDENT SUBQUERY
        table: inc
         type: index_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index; Using where
*************************** 9. row ***************************
           id: 5
  select_type: DEPENDENT SUBQUERY
        table: jos_tag_rules_content_include
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: jprod.a.id
         rows: 1
        Extra: Using where; Using index
*************************** 10. row ***************************
           id: 4
  select_type: DEPENDENT SUBQUERY
        table: inc
         type: index_subquery
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index; Using where
*************************** 11. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: exc
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using where; Using index
*************************** 12. row ***************************
           id: 3
  select_type: DEPENDENT UNION
        table: inc
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: func
         rows: 1
        Extra: Using where; Using index
*************************** 13. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
13 rows in set (0.00 sec)