Re: MySQL InnoDB Performance vs Postgres

[Peter Zaitsev]

Jay has added good comment, if you can have reasonably short PK on your table it is good to have it.

In your case with Innodb you really want to have data as clustered as possible, to avoid a lot of random IO.

Innodb has 16K pages. Each pages so may fit few hundreds of rows in your case. If you get things right page would contain most of rows which match your query if not you may get only 1 row from this page and would need to do much more IO to retrieve others.

In your case which query part does return more rows the one selecting by user_id or by friend_id ? If query by user_id returns more rows make user_id first column in the primary key.

Also your query reads only few columns, so you may benefit a lot by adding "covering" which would help second part of union:

(friend_id,status,id)


At least if you do not want to change your structure make both of the keys involved covered (add status and id)

This would avoid reading the data file which is expensive.

Let us know what results are after these changes.