title: Very slow mysql query: left join multiple table and where clause for each table
body: <p>I have one base table(id table) and multiple tables(data table) which have the data related to the id's and each table has different type data.
  I want to filter id's by multiple condition from these different table.</p>
  
  <p>I created query as follows, but this is too slow like 3 minutes. I checked explain information but still can not find better way. Could anyone find the way to get it faster?</p>
  
  <p>query</p>
  
  <pre><code>    select jj.id, jj.imgtitle, jj.alias
      from jjtable jj
      inner join jjtable_catg jjc on jj.catid = jjc.cid
      left join jjtable_map jjtm_name on jj.id = jjtm_name.picid
      left join jjtable_tags jjts_name on jjtm_name.tid = jjts_name.tid
      left join jjtable_map_ge jjtm_ge on jj.id = jjtm_ge.picid
      left join jjtable_tags jjts_ge on jjtm_ge.tid = jjts_ge.tid
      left join jjtable_map_ban jjtm_ban on jj.id = jjtm_ban.picid
      left join jjtable_tags jjts_ban on jjtm_ban.tid = jjts_ban.tid
      left join jjtable_map_per jjtm_per on jj.id = jjtm_per.picid
      left join jjtable_tags jjts_per on jjtm_per.tid = jjts_per.tid
      left join jjtable_map_fea jjtm_fea on jj.id = jjtm_fea.picid
      left join jjtable_tags jjts_fea on jjtm_fea.tid = jjts_fea.tid
      left join jjtable_map_ev jjtm_ev on jj.id = jjtm_ev.picid
      left join jjtable_tags jjts_ev on jjtm_ev.tid = jjts_ev.tid 
      left join jjtable_map_ag jjtm_ag on jj.id = jjtm_ag.picid
      left join jjtable_tags jjts_ag on jjtm_ag.tid = jjts_ag.tid
      left join jjtable_map_fa jjtm_fa on jj.id = jjtm_fa.picid
      left join jjtable_tags jjts_fa on jjtm_fa.tid = jjts_fa.tid
      left join jjtable_map_im jjtm_im on jj.id = jjtm_im.picid
      left join jjtable_tags jjts_im on jjtm_im.tid = jjts_im.tid where jj.published = 1
      and jj.approved  = 1 
      and jjts_fea.tid in(87,90)
      and jjts_fea.delete_flag = 0
      and jjtm_fea.delete_flag = 0
      and jjc.cid in(4,10)
      and jjts_name.tid in(77)
      and jjts_name.delete_flag = 0
      and jjtm_name.delete_flag = 0
      and jjts_per.tid in(28,36)
      and jjts_per.delete_flag = 0
      and jjtm_per.delete_flag = 0
      and jjts_ag.tid in(98,99)
      and jjts_ag.delete_flag = 0
      and jjtm_ag.delete_flag = 0
      and jjts_fa.tid in(104,107)
      and jjts_fa.delete_flag = 0
      and jjtm_fa.delete_flag = 0 
      group by jj.id 
      order by case when date(jj.date) &gt; date_add(date(now()), interval -14 day) then jj.view end DESC, jj.id DESC
  </code></pre>
  
  <p>EDIT: Thank you for your comment. firstly I add information as your request.</p>
  
  <p>WHY NEED THIS QUERY;</p>
  
  <p>This query are intended to received the post data from "form input" tag which user select options from different categories to filter ID. That is why this query includes tables and data which are not used in this example.</p>
  
  <p>The reason why there are so many left join is that each 'map' table has category mapping information to filter ID's. etc, user want to find ID's which are mapped with 'fea', 87, 90, and 'ag', 98, 99.</p>
  
  <p>explain select;</p>
  
  <pre><code>        +----+-------------+--------------+--------+-------------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
          | id | select_type | table        | type   | possible_keys     | key             | key_len | ref                                            | rows | Extra                           |
          +----+-------------+--------------+--------+-------------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
          |  1 | SIMPLE      | jjts_name    | const  | PRIMARY           | PRIMARY         | 4       | const                                          |    1 | Using temporary; Using filesort |
          |  1 | SIMPLE      | jjc          | range  | PRIMARY           | PRIMARY         | 4       | NULL                                           |    2 | Using where                     |
          |  1 | SIMPLE      | jjts_pe      | range  | PRIMARY           | PRIMARY         | 4       | NULL                                           |    2 | Using where; Using join buffer  |
          |  1 | SIMPLE      | jjts_fea     | range  | PRIMARY           | PRIMARY         | 4       | NULL                                           |    2 | Using where; Using join buffer  |
          |  1 | SIMPLE      | jjts_ag      | range  | PRIMARY           | PRIMARY         | 4       | NULL                                           |    2 | Using where; Using join buffer  |
          |  1 | SIMPLE      | jjts_fa      | range  | PRIMARY           | PRIMARY         | 4       | NULL                                           |    2 | Using where; Using join buffer  |
          |  1 | SIMPLE      | jj           | ref    | PRIMARY,idx_catid | idx_catid       | 4       | jjc.cid                                        |   95 | Using where                     |
          |  1 | SIMPLE      | jjtm_ag      | eq_ref | udx_picid_tid     | udx_picid_tid   | 8       | jj.id,jjts_ag.tid                              |    1 | Using where                     |
          |  1 | SIMPLE      | jjtm_fa      | eq_ref | udx_picid_tid     | udx_picid_tid   | 8       | jj.id,jjts_fa.tid                              |    1 | Using where                     |
          |  1 | SIMPLE      | jjtm_fea     | eq_ref | udx_picid_tid     | udx_picid_tid   | 8       | jj.id,jjts_fea.tid                             |    1 | Using where                     |
          |  1 | SIMPLE      | jjtm_pe      | eq_ref | udx_picid_tid     | udx_picid_tid   | 8       | jjtm_fea.picid,jjts_per.tid                    |    1 | Using where                     |
          |  1 | SIMPLE      | jjtm_ev      | ref    | udx_picid_tid     | udx_picid_tid   | 4       | jjtm_fa.picid                                  |    3 | Using index                     |
          |  1 | SIMPLE      | jjts_ev      | eq_ref | PRIMARY           | PRIMARY         | 4       | jjtm_ev.tid                                    |    1 | Using index                     |
          |  1 | SIMPLE      | jjtm_im      | ref    | udx_picid_tid     | udx_picid_tid   | 4       | jjtm_pe.picid                                  |    6 | Using index                     |
          |  1 | SIMPLE      | jjts_im      | eq_ref | PRIMARY           | PRIMARY         | 4       | jjtm_im.tid                                    |    1 | Using index                     |
          |  1 | SIMPLE      | jjtm_ge      | ref    | udx_picid_tid     | udx_picid_tid   | 4       | jj.id                                          |   23 | Using index                     |
          |  1 | SIMPLE      | jjts_ge      | eq_ref | PRIMARY           | PRIMARY         | 4       | jjtm_ge.tid                                    |    1 | Using index                     |
          |  1 | SIMPLE      | jjtm_ban     | ref    | udx_picid_tid     | udx_picid_tid   | 4       | jjtm_pe.picid                                  |    9 | Using index                     |
          |  1 | SIMPLE      | jjts_ban     | eq_ref | PRIMARY           | PRIMARY         | 4       | jjtm_ban.tid                                   |    1 | Using index                     |
          |  1 | SIMPLE      | jjtm_name    | eq_ref | udx_picid_tid     | udx_picid_tid   | 8       | jjtm_fea.picid,const                           |    1 | Using where                     |
          +----+-------------+--------------+--------+-------------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
          20 rows in set (2 min 15.87 sec)
  </code></pre>
  
  <p>show columns;</p>
  
  <pre><code>        mysql&gt; show columns from jjtables;
          +--------------+------------------+------+-----+---------+----------------+
          | Field        | Type             | Null | Key | Default | Extra          |
          +--------------+------------------+------+-----+---------+----------------+
          | id           | int(11)          | NO   | PRI | NULL    | auto_increment |
          | catid        | int(11)          | NO   | MUL | 0       |                |
          | imgtitle     | text             | NO   |     | NULL    |                |
          | alias        | varchar(255)     | NO   |     |         |                |
          | date         | datetime         | NO   |     | NULL    |                |
          | view         | int(11)          | NO   |     | 0       |                |
          | published    | tinyint(1)       | NO   |     | 0       |                |
          | approved     | tinyint(1)       | NO   |     | 0       |                |
          +--------------+------------------+------+-----+---------+----------------+
  
  
          mysql&gt; show columns from jjtable_catg;
          +--------------+---------------------+------+-----+---------+----------------+
          | Field        | Type                | Null | Key | Default | Extra          |
          +--------------+---------------------+------+-----+---------+----------------+
          | cid          | int(11)             | NO   | PRI | NULL    | auto_increment |
          | name         | varchar(255)        | NO   |     |         |                |
          | alias        | varchar(255)        | NO   |     |         |                |
          | parent       | int(11)             | NO   | MUL | 0       |                |
          | desc         | text                | YES  |     | NULL    |                |
          | order        | int(11)             | NO   |     | 0       |                |
          +--------------+---------------------+------+-----+---------+----------------+
  
  
          mysql&gt; show columns from jjtable_tags;
          +--------------+-------------+------+-----+---------------------+----------------+
          | Field        | Type        | Null | Key | Default             | Extra          |
          +--------------+-------------+------+-----+---------------------+----------------+
          | tid          | int(11)     | NO   | PRI | NULL                | auto_increment |
          | name         | varchar(60) | NO   | UNI | NULL                |                |
          | talias       | varchar(60) | NO   |     |                     |                |
          | ttypeid      | int(11)     | NO   |     | 1                   |                |
          | pa_tid       | int(11)     | NO   |     | 0                   |                |
          | delete_flag  | tinyint(1)  | NO   |     | 0                   |                |
          | created      | datetime    | NO   |     | 0000-00-00 00:00:00 |                |
          | created_by   | int(11)     | NO   |     | 0                   |                |
          | modified     | datetime    | NO   |     | 0000-00-00 00:00:00 |                |
          | modified_by  | int(11)     | NO   |     | 0                   |                |
          | t_due        | datetime    | NO   |     | 0000-00-00 00:00:00 |                |
          +--------------+-------------+------+-----+---------------------+----------------+
  
  
          mysql&gt; show columns from jjtable_map;
          +-------------+------------+------+-----+---------------------+----------------+
          | Field       | Type       | Null | Key | Default             | Extra          |
          +-------------+------------+------+-----+---------------------+----------------+
          | id          | int(11)    | NO   | PRI | NULL                | auto_increment |
          | picid       | int(11)    | NO   | MUL | NULL                |                |
          | tid         | int(11)    | NO   |     | NULL                |                |
          | delete_flag | tinyint(1) | NO   |     | 0                   |                |
          | created     | datetime   | NO   |     | 0000-00-00 00:00:00 |                |
          | created_by  | int(11)    | NO   |     | 0                   |                |
          | modified    | datetime   | NO   |     | 0000-00-00 00:00:00 |                |
          | modified_by | int(11)    | NO   |     | 0                   |                |
          +-------------+------------+------+-----+---------------------+----------------+
  </code></pre>
  
  <p>Sorry for my poor explanation. I hope this edit help your better understanding.
  EDIT END
  Regards</p>