Create an index only on certain rows in mysql

Question

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

Answer 1

Regarding the conditional index:

You can't do this. MySQL has no such thing.

Regarding the primary key:

It depends on the storage engine. MySQL does not define how data is stored or retrieved, that's left up to the storage engine.

MyISAM does not enforce any order on how rows are stored; they're appended to the end of the table but gaps from deleting can be reused and UPDATE queries can leave things out of order even without any DELETEs.

InnoDB stores rows in order of their primary keys.