Re: MySQL InnoDB Performance vs Postgres

[Ram Gudavalli]

I added a primary key index on id which increased the performance from about 50 queries/sec to 155 queries/sec. That is a reasonable boost but still doesn't come close to the 250 queries/sec that postgres gets.

It is random which one of the two queries will return more rows. On average over a large set of queries, they should return about the same number of rows.

The table represents a relationship between users. Userid and friendid are both foreign keys of the id column in the users table.

This particular table will get new rows inserted into it and existing rows removed. Thus, we are using InnoDB to for crash recovery ability and general data integrity.

So I will try covering by adding a userid/status index and a friendid/status index and get the performance results from that. Are there other ways to improve this query? In general, will the performance of the query degrade as the table gets bigger?


Ram