Earlier quoted context omitted.
Many of the complaints I hear people make about the entire concept of an "RDBMS" (often then to motivate why the NoSQL solution they decided to start using is better) are actually MySQL-specific issues that do not affect PostgreSQL (or Oracle, or usually SQL Server; I only mention PostgreSQL, as you wanted a concrete experience); one key example is "if you want to change your schema, it requires locking the entire sy…
Or "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others. Take, for example, this gem: > Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results. This describes just about every standard page-with-comments schema ever devi…
LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it