title: Create an index only on certain rows in mysql
body: <p>So, I have this funny requirement of creating an index on a table only on a certain set of rows.</p>
  
  <p>This is what my table looks like:</p>
  
  <blockquote>
    <p>USER: userid, friendid, created, blah0, blah1, ..., blahN</p>
  </blockquote>
  
  <p>Now, I'd like to create an index on:</p>
  
  <blockquote>
    <p>(userid, friendid, created)</p>
  </blockquote>
  
  <p>but only on those rows where userid = friendid. The reason being that this index is only going to be used to satisfy queries where the WHERE clause contains "userid = friendid". There will be many rows where this is NOT the case, and I really don't want to waste all that extra space on the index.</p>
  
  <p>Another option would be to create a table (query table) which is populated on insert/update of this table and create a trigger to do so, but again I am guessing an index on that table would mean that the data would be stored twice.</p>
  
  <p>How does mysql store Primary Keys? I mean is the table ordered on the Primary Key or is it ordered by insert order and the PK is like a normal unique index?</p>
  
  <p>I checked up on clustered indexes (http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html), but it seems only InnoDB supports them. I am using MyISAM (I mention this because then I could have created a clustered index on these 3 fields in the query table).</p>
  
  <p>I am basically looking for something like this:</p>
  
  <blockquote>
    <p>ALTER TABLE USERS ADD INDEX (userid, friendid, created) WHERE userid=friendid</p>
  </blockquote>