So, I have this funny requirement of creating an index on a table only on a certain set of rows.

This is what my table looks like:

USER: userid, friendid, created, blah0, blah1, ..., blahN

Now, I'd like to create an index on:

(userid, friendid, created)

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.

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.

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?

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).

I am basically looking for something like this:

ALTER TABLE USERS ADD INDEX (userid, friendid, created) WHERE userid=friendid