Create an index only on certain rows in mysql

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 use开发者_如何学Pythonrid = 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

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.

hard to tell what you're actually trying to do here (why would a user need to be his own friend?) but it seems to me a simple rethinking of your database schema would resolve this problem.

table 1: USER: userid, created, blah0, blah1, ...,

table 2: userIsFriend (user1,user2,...)

and just do your indexing on table 2 (whose elements presumably have foreign key constraint on table 1)

btw you should probably be using InnoDB if you want to do anything semi-serious with mySQL anyway, IMHO.