The problems in PostGres DB are solved in MySQL to an extent. The design merits of MySQL are the following:
- The primary index has a mapping of key to an offset in the disk. But all secondary index tables have a mapping of key to the primary index’s key.
Primary index
--------------------
| key | Disk offset|
--------------------
Secondary index
---------------------
| key | primary key |
---------------------
- The drawback is that a lookup of the secondary table needs two hops.
- On the other hand, a change in a row only needs modification of the primary index. It avoids changing the secondary index for every change in row’s data
- MySQL replication uses logical information instead of data with physical information used in PostGres.
- Logs are compact compared to PG.
- MySQL manages its own cache, unlike PG that uses buffer cache.
- PG buffer cache use is inefficient since a read operation would have to land in the buffer cache and hence a context switch.
- PG uses a process per connection. It is expensive. MySQL uses threads for a connection and scales without too much resource consumption.
