title: Using Index Cause MySQL Query Stuck For Infinite Time
body: <p>I have few mysql tables with huge data. To speed up some frequent queries, I am creating indexes to make queries faster. I already did created few composite index which are working fine.</p>
  
  <p>However, I am currently stuck on creating a two column index, which I targeted to make two queries faster. One of the queries running fine(and faster, of course), but other query is getting stuck in the following state:</p>
  
  <p><img src="https://i.stack.imgur.com/bT2rl.png" alt="enter image description here"></p>
  
  <p>The index created on (one foreign key(u2_.'service_id', u2_.'status') + one boolean) column. The query is as below:</p>
  
  <pre><code>SELECT u0_.value AS value0, u1_.property_uri AS property_uri1, count(u0_.id) AS sclr2,
    u2_.service_id AS sclr3 
  FROM usc_connection_triple u0_ 
  INNER JOIN usc_property u1_ 
    ON u0_.property_id = u1_.id AND (u1_.status = 1) 
  INNER JOIN usc_account_connection u3_ 
    ON u0_.account_connection_id = u3_.id AND (u3_.status = 1) 
  INNER JOIN usc_service_subscriber u2_ 
    ON ((u2_.id = u3_.account_1_id OR u2_.id = u3_.account_2_id)) 
  WHERE (u1_.create_analytics = '1') AND (u0_.status = 1) AND (u2_.status = 1) 
  GROUP BY u2_.service_id, u0_.property_id, u0_.value
  </code></pre>
  
  <p>I can't have a clue what I can do to resolve this? Can you please give some suggestion/idea how can I go ahead with this? As this suppose to improve two important queries, its kind of critical I need use this.</p>
  
  <p>Update:
  I realized, I wasn't properly descriptive about my idea of deadlock. Here is why I thought so:</p>
  
  <p>before creating the index, this query was taking around 70 seconds to complete. after the index creation, I waited up to several hours, didn't help at all, which is very unusual. Later, I killed the php process and tried to drop the index, which also got stuck in the following state:</p>
  
  <p><img src="https://i.stack.imgur.com/bda78.png" alt="enter image description here"></p>
  
  <p>So, for this 'metadata lock' state, I though, the issue was may be related in getting lock for tables. Also, as its known that, over-indexing might create deadlock, I was thinking, probably that was my situation.</p>
  
  <p>Do you think this might be related to some other aspect <strong>like buffer pool config</strong> etc? Or <strong>any problem using a column in multiple secondary index</strong>?</p>